Category: SQL

DDL, SQL

UNIQUE constraints in SQL Server

UNIQUE constraints are enforcing no duplicates can exists for a column or combination of columns for a table. We can define constraints in 3 ways:

  1. while creating a table
  2. using ALTER TABLE statement
  3. using SQL Server Management Studio

Let us quickly see with a simple example as below.

Drop table if exists TestTable
Create Table TestTable ( ID int UNIQUE, FirstName varchar(50) )

sp_help 'TestTable'

sp_help system procedure can be used to understand the table properties. If we observe the result of sp_help, we can see two important information.

  1. constraint name – unique constraint created.
  2. index name – though we have not created any index separately, there is one created as the same name of constraint.

So, when we create a UNIQUE constraint, SQL Server automatically creates a nonclustered index on the table. And this index is responsible for enforcing the uniqueness on the column(s).

Can we drop the index created?

No, we will never be able to drop those indexes until we drop the constraints.

Can we insert NULL values to a UNIQUE column?

Yes, we can have ONLY one NULL value to a UNIQUE column. If we try to insert again, it will fail for the same reason of duplicate violation.

Can we define UNIQUE for more than one column?

Yes, we can define on combination of columns. Interestingly, by defining on composite, we can have NULL values for each individual columns once and one for combination as well.

Drop table if exists TestTable
Create Table TestTable ( ID int , FirstName varchar(50) , UNIQUE(ID,FirstName))

--Data inserts
Print 'First insert'
Insert into TestTable Values(1,NULL)
Print 'Second insert'
Insert into TestTable Values(NULL,NULL)
Print 'Third insert'
Insert into TestTable Values(NULL,'SQL')
Print 'Fourth insert'
Insert into TestTable Values(100,'some name')

If you enjoyed this blog post, feel free to share it with your friends!

DDL, SQL

NOT NULL Constraint in SQL Server

NOT NULL constraints are important constraints in SQL Server to ensure the column defined on never accepts NULL values. By default, column accepts NULL value in SQL Server. If we need to ensure the column should not accept NULL values, then we can define a NOT NULL constraints on the column. We can create NOT NULL constraint in 3 ways:

  • while creating a table
  • using alter table statement
  • using SQL Server management Studio

Let us see an example:

Create Table TestTable ( ID int NOT NULL , FirstName varchar(50) )

Insert into TestTable Values(1,'SQL'),(NULL, 'Zealot')

Msg 515, Level 16, State 2, Line 3
Cannot insert the value NULL into column ‘ID’, table ‘SQLZealot.dbo.TestTable’; column does not allow nulls. INSERT fails.
The statement has been terminated.

Now, let us insert some data with NULL values for FirstName column and define a NOT NULL constraint on First Name column as below.

Insert into TestTable Values(2,NULL)

ALTER TABLE TestTable ALTER COLUMN FirstName VARCHAR(50) NOT NULL

Msg 515, Level 16, State 2, Line 5
Cannot insert the value NULL into column ‘FirstName’, table ‘SQLZealot.dbo.TestTable’; column does not allow nulls. UPDATE fails.
The statement has been terminated.

This error has raised because we have already inserted a record that has NULL value for First Name column in the table. That means, when we create a constraint, it checks the existing data before it creates the constraints.

If you enjoyed this blog post, feel free to share it with your friends!

DataTypes, SQL

CAST and CONVERT in SQL Server

CAST and CONVERT are two functions that explicitly converts an expression from one data type to another data type.

Syntax:


-- CAST Syntax:
CAST ( expression AS data_type [ ( length ) ] )

-- CONVERT Syntax:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Let us see a quick example of CAST and CONVERT as below.

Select cast(getdate() as varchar(max))
Select convert(varchar(max),getdate(),101)
Select convert(varchar(max),getdate(),102)

If we observe the difference, CAST has only a one form of way, however, CONVERT is feasible to convert to different results as we define the style of the format. There are many different style options, and you can refer those in “standard date CONVERT formats” section of ready reckoner date post.

In fact, CAST is changing as CONVERT internally while executing the query. We can simply look at the execution plan and understand this behavior as below screen shot.

SQL Server Data Type Conversion Chart:

The below chart is a beautiful way of representing the Data type conversion in SQL Server.

Credit: https://www.microsoft.com/en-us/download/confirmation.aspx?id=35834

Points to ponder:

  • CAST is an ANSI SQL Standard, however, CONVERT is a SQL Server specific.
  • CAST is single form result function, other hand, CONVERT function results as style format defined.
  • There are NO major notable difference observed in performance.

If you enjoyed this blog post, feel free to share it with your friends!

SQL

@@SERVERNAME, @@SERVICENAME & @@REMSERVER in SQL Server

@@SERVERNAME

This variable returns a nvarchar value that represents the name of the local server. 

Select @@SERVERNAME

If the current instance is a default instance, it will return “servername”. If the instance is a named instance, then it will return “servername\instancename”.

I would prefer SERVERPROPERTY(‘servername’) over this global variable. The SERVERNAME property automatically reports changes in the network name of the computer. But, @@SERVERNAME does not report such changes automatically unless the changes are made to the local server name using the sp_addserver or sp_dropserver stored procedures.

Select  @@SERVername,SERVERPROPERTY('servername')

@@SERVICENAME

This variable returns a nvarchar value that represents the service name of SQL Server. If the current instance is a named instance, it will return the instance name else if its a default, then it reutrns “MSSQLSERVER”

Select  @@SERVICENAME

@@REMSERVER

This variable returns the name of the remote SQL Server database server as it appears in the login record.

Since am currently using SQL Server 2016, this feature returns NULL value, however , in earlier version it was returning the server name from where the procedure is being called. There is an example of how it was working in MS official documentation: https://docs.microsoft.com/en-us/sql/t-sql/functions/remserver-transact-sql?view=sql-server-ver15

This feature has been deprecated, hence its not really advisable to use in future development and also recommend to find a better way to replace in existing codes.

Honestly, I was not really able to find a good alternative for this feature and also asked this question in MS Q&A looking for an answer. Unfortunately, there are not much help on this.

If you enjoyed this blog post, feel free to share it with your friends!

SQL

Msdb database in SQL Server

Msdb – A biological clock and Hippocampus

Msdb is a very important database as far as considering SQL Server, so called a biological clock & hippocampus.

Msdb database stores lots of information like agent jobs/ database backup-restore information and many more. We can quickly see a script developed to get job details and history information, mainly used msdb objects. To add to that, there is a script which pulls all information of back up and restore as well. In addition, some of Service Broker information, log shipping monitor history, SSIS packages, Database Engine Tuning Advisor data, Central Management Server (CMS)  are also stored in msdb database.

Since it play vital role by saving all these information, it is highly recommended to take back up of msdb on a daily basis. Note that the msdb comes with simple recovery model by default, which can be changed to FULL as required depending on your requirement.

An important thing is maintenance of msdb database, since it stores all lots of information about your sql agent jobs and backup histories, a clear maintenance needs to be established for msdb like purging data more than 60 or 90 days etc. There are system procedures like  sp_delete_backuphistory , sp_cleanup_log_shipping_history & sp_purge_jobhistory to cleanup the data from msdb.

Forbidden Actions

  1. We cannot drop msdb database
  2. We cannot set msdb database offline
  3. We cannot enable Change Data Capture (CDC) or Change Tracking (CT) for msdb
  4. Database mirroring cannot be configured on msdb

See Also

A good read on job related posts

How to move msdb database

If you enjoyed this blog post, feel free to share it with your friends!