Please note, the above script is not actually disabling the cdc on tables, but generates the statements which can be used to disable the CDC on tables. This is just to make sure we review before we apply the disable commands.
I would also suggest to execute the below to disable the Change Data Capture on the database as well.
use dbname
EXEC sys.sp_cdc_disable_db
If you enjoyed this blog post, feel free to share it with your friends!
CHECK constraint is to specify a predicate to a column or multiple columns that allows the data values that are acceptable. If the value is not satisfying the condition, the record violates the constraint and the operation will be ignored.
Let us quickly look at few of its usage with some examples as below.
create table testcheck
(
ID int NOT NULL,
Name varchar(50),
Gender varchar(50) check (Gender in ('Male','Female'))
)
Insert into testcheck values(1,'Latheesh','Male')--Success row
Insert into testcheck values(1,'SQL Server','') --Failed row
Is it possible to drop a constraint?
Yes, we can very well drop a check constraint using alter table statement as below.
--Drop the constraint
ALTER TABLE testcheck DROP CONSTRAINT CK__testcheck__Gende__29572725;
--retry with no value for gender
Insert into testcheck values(1,'SQL Server','')
Does the constraint check the existing data?
Yes, when we create a constraint, it check the existing value by default.
--Add the constraint back
ALTER TABLE testcheck ADD CONSTRAINT CK__testcheck__Gende__29572725 CHECK (Gender in ('Male','Female') );
Is it possible to enable and disable a constraint?
Yes, very well.
Select * From testcheck
--Disable the constraint
ALTER TABLE testcheck NOCHECK CONSTRAINT CK__testcheck__Gende__29572725;
--Insert a value that does not satisfy
Insert into testcheck values(1,'SQL Server','')
Select * From testcheck
Now, let us enable the constraint back and see the interesting behavior.
--Enable the constraint back
ALTER TABLE testcheck CHECK CONSTRAINT CK__testcheck__Gende__29572725; --This would not check the existing data
Select * From testcheck
Insert into testcheck values(1,'dummy','')
Select * From testcheck
We can see that the CHECK CONSTRAINT operation is enabling the constraint and it violates the next insert statement as expected. However, if you notice the existing data, there is a violated data, which is not evaluated. Quite interesting, right? So, we need a different way to enable the constraint that also verifies the existing data. Here we go with the usage of WITH CHECK.
-- Enable the constraint properly
ALTER TABLE testcheck WITH CHECK CHECK CONSTRAINT CK__testcheck__Gende__29572725 --This would also check the existing data
The above shows that WITH CHECK actually evaluates the existing data as well. Now, let us clean up the data and re-enable successfully.
--Existing data clean up
Delete from testcheck where gender not in ('male','Female')
-- Enable the constraint properly
ALTER TABLE testcheck WITH CHECK CHECK CONSTRAINT CK__testcheck__Gende__29572725 --This time, this would succeed
When you disable a constraint using WITH NOCHECK, SQL Server is making a change to constraint to no longer trusts that constraint. It flags it as “not trusted” in metadata. Eventually, the only way to set it to trusted is by specifying the constraint WITH CHECK while enabling the constraint.
If you enjoyed this blog post, feel free to share it with your friends!
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:
while creating a table
using ALTER TABLE statement
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.
constraint name – unique constraint created.
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!
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!
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.
