You are reading this post just because of two reason
1) You are curious whether it is possible or not and why we require ?
2) You need to disable constraints
So , let me share here that you can disable constraints at anytime of a table.
Sometime it is possible when you are doing bulk insert or you need to insert values in column and for this you need to disable constraints.
You can disable all the constraints of a table using following command
ALTER TABLE TABLENAME NOCHECK CONSTRAINT ALL ;
If you want to disable a specific constraint of a table then you can use following syntax
ALTER TABLE TABLENAME NOCHECK CONSTRAINT_NAME
For example suppose you want to disable all the constraints of student table then you can write following syntax
ALTER TABLE dbo.Students NOCHECK ALL;
Below is very live example in Indian scenario
Suppose you added a check marks in last class more than 45% then only add student now due to some out side pressure you want to give admission to a student who has 40% percent then you need to disable percentage check.
ALTER TABLE dbo.Student NOCHECK chk_Student_Percentage_40
I hope this might help you somewhere.
Sometimes it may require that you don’t know what will be output of a stored procedure ? what kind of result set it return ?
In such case SQL SERVER provided a new DMV statement which is sys.dm_exec_describe_first_result_set_for_object.
In other words if we want to know result set’s meta data then we can use it.
Lets understand this by an example.
Suppose we have an advertisementSelect stored procedure as shown below
As you see there are various column returning by the stored procedure.
Now let suppose we don’t have enough permission to view its definition or any other reason we are not able to view actual what is return in the stored procedure and now we want to know what is the result set then in such case we will use DMV command which is “SYS.DM_EXEC_DESCRIBE_FIRST_RESULT_SET_FOR_OBJECT”
We can use it as follows
When we run it we will get result which we require as shown in below figure
If you see above screen you will find all the columns with their data type which will come as a result set of a stored procedure “AdvertisementSelect”
I hope this may help you somewhere.
This is very interesting feature and I recently come to know this awesome feature.
Lets understand this by an example below
Suppose we have a student table with following structure as shown in below image
We have a detail column with VARCHAR(MAX) .
Now as it is VARCHAR(MAX) column it may content a large amount of data currently it has following data as shown in below figure
Suppose we want to replace a specific string from this large column value like we want to replace “interested in” with “Always” of studentId = 1 then
we can use write function easily.
With the help of Write we can update a specific text/string of a large column.
syntax of Write is as shown below
SET COLUMNNAME.WRITE (ReplacedWithString, startPosition, length)
Now lets understand this by above student example where we want to replace “Interested in” which is doubled by mistake with “always”
So when you run above query you will get following result when you run select command.
great we have replaced the string which we want.
The main benefit of this the entire column value is not logged. suppose you have 2 GB data in your column then instead of logging 2 GB data at the time update only few KB will be logged.
I hope this might helpful to you somewhere.
Suppose, sometimes you just need entry in the table with all the default values.
For example you have a Student table which looks like as below
CREATE TABLE #tmpStudent (StudentId INT IDENTITY(1,1),
FirstName VARCHAR(100) DEFAULT ‘RAJAT’,
Course VARCHAR(100) DEFAULT ‘MATHS’)
Now , suppose for in some situation we need just default entry for example in my case I am just trying to insert 100 rows with default value so I need to write below statement
INSERT INTO #tmpStudent (FirstName ,Course ) VALUES (Default, default)
Now when I run the above statement. It insert default value of FirstName,course
I hope this might be helpful to you somewhere.
Suppose, We are working on a stored procedure in which some complex operation is going. We are fetching some value from a table and running some custom operations and after ending of this complex operation, we want a pause of suppose 10 seconds to run another job then in such situation we will use
“WAITFOR DELAY HH:MM: SS”
For example if we want to run select command (second statement) after 10 second delay of first command execution then we will use this as shown in below
INSERT INTO tblStudent (StudentId, StudentName)
— in the background we are running some cursor which would take aprox 10 seconds so we are waiting for 10 seconds here
WAITFOR DELAY 00:00:10
SELECT * FROM tblStudentHistory
In other situation suppose you want to run specific command after particular time then in such cases we will use “WAITFOR TIME HH: MM: SS”
“WAITFOR TIME 22:30:45 “ it means SQL statement will pause till 10:30:45 PM
I hope this tips may help you somewhere in your project.
Recently , one of my friends shared that some interviewer asked him a question “How to copy table structure only from a SQL Query?”
Below is simplest query to copy structure only of a table into another table.
WHERE 1= 0
in the above query we want to copy structure of tblStudentSource.
see below snap which help you to understand it more
I hope this might help you if some asked you this question.
Thanks & Enjoy!!!
Problem:- One of the most important question comes in our mind what is the cause of slow SQL SERVER.
We always struggle with following questions
Which is highly CPU consuming query ?
Which is highly RAM consuming query ?
Who is blocking the transaction ?
and many more other performance dragging questions.
Believe me if you know who is culprit of making your system slow, you will win half battle.
Solution:- SQL Server provided an easy way to clear your all doubts related to above questions. This easy way is “STANDARD REPORTS”.
You can access Standard Reports option by right clicking the SQL SERVER instance. These Standard reports contains not only performance related reports but other useful reports also.
If you see below image you will find there are many other reports option available.
Now, suppose I am interested to know which query consuming high CPU , to achieve this I clicked on Performance Query – TOP Queries by Average CPU time or Performance query – Top queries by total CPU time.
When I clicked on any of this option ,I got a report.
This report contained a bar graph & query detail in tabular format which contains query & CPU consuming time as shown below.
in similar way we can get answer of our other performance related query also.
I hope this may help you somewhere.
Thanks & Best Regards,