Database Management Systems
You can run the following query to return all the collations that are supported in your instance of SQL Server:
SELECT name, description FROM sys.fn_helpcollations();
Note that this returns quite a long list. Running that statement on SQL Server 2017 returns 3955 collations.
Below is a table of all collations supported in SQL Server 2017 (warning: It’s a long list!).
This list was returned by running the following code:
SELECT name, description FROM sys.fn_helpcollations();
That returns the following list of 3955 collations.
Continue reading
This is a quick article to demonstrate how to use Transact-SQL to change the collation of a database in SQL Server.
Here’s the basic code:
USE master; GO ALTER DATABASE Solutions COLLATE French_CI_AS ; GO
This first changes to the master database, then we use the ALTER DATABASE statement to change the collation of the Solutions database to French_CI_AS collation.
Continue reading
You can use the T-SQL BACKUP DATABASE statement to back up any SQL Server database.
This article shows you how to backup a database to disk. This creates a .bak file which can be used later to restore the database if required.
Continue reading
While you can certainly rename a SQL Server database in the SSMS GUI by right-clicking on the database name and selecting Rename, sometimes you might prefer (or need) to do it using Transact-SQL.
The most basic way to rename a database using T-SQL is like this:
-- Change to the master database USE master; -- Change the database name ALTER DATABASE Films Modify Name = Movies; GO
The only problem with this basic script is that it doesn’t change the name of the data files and log files. In most cases you’ll probably want to change the names of these files to match the new name. In that case you can take the following script and replace the database name with your own (as well as its file names and paths):
Continue reading
The simplest way to create a database in SQL Server is to use CREATE DATABASE my_database without specifying anything else. When you do this, data files and log files are created in the default location (see how to find the default location).
However, sometimes you might want the data files and log files to reside in a different location. If that’s the case, use the following code example to explicitly state your own location for the database’s data files and log files.
USE master;
GO
CREATE DATABASE Solutions
ON
( NAME = Solutions_dat,
FILENAME = 'D:\mssql\data\Solutionsdat.mdf',
SIZE = 10MB,
MAXSIZE = 50MB,
FILEGROWTH = 5MB )
LOG ON
( NAME = Solutions_log,
FILENAME = 'D:\mssql\data\Solutionslog.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB );
GO
That example uses Windows path conventions (starts with a drive letter and uses a backslash).
Any time you create a database in SQL Server, two files are created. One is the data file, and the other is the transaction log file.
The location of these files will depend on whether or not you explicitly specify a location for these files when you create the database. If not, they will be created in the default location.
You can find the default location with the following code:
SELECT
SERVERPROPERTY('InstanceDefaultDataPath') AS 'Data Files',
SERVERPROPERTY('InstanceDefaultLogPath') AS 'Log Files'
If you ever need to know where your database files are located, run the following T-SQL code:
USE master; SELECT name 'Logical Name', physical_name 'File Location' FROM sys.master_files;
This will return a list of all data files and log files for the SQL Server instance.
If you’ve ever tried to insert values into an identity column in SQL Server, you might’ve seen an error like this:
Cannot insert explicit value for identity column in table ‘Artists’ when IDENTITY_INSERT is set to OFF.
This is normal. An identity column is there for a reason. It automatically populates the column with an incrementing value for each row that’s inserted. Therefore there’s no need for you to insert a value into that column.
However, sometimes you do need to insert a value into an identity column. For example, you could be populating the database with data that needs to retain its own identity values. If this is the case, you’ll need to override the IDENTITY property. Here’s how.
Here’s a quick overview of the difference between SQL and T-SQL (Transact-SQL). Not so much the differences as such, but more an explanation of T-SQL and where it sits in relation to SQL.
If you’ve done any database development or administration, you’re probably familiar with SQL. SQL, which stands for Structured Query Language, is a standard query language for working with databases. Most of the major relational database management systems such as MySQL, Oracle, SQL Server, PostgreSQL, etc support SQL in one way or another.
However, while the SQL standard provides clear specifications, it also allows for database vendors to add their own extensions. This allows vendors to provide extra features and functionality for their customers that might not be offered by their competitors.
This is where T-SQL comes in.