SQL SERVER – How to Disable and Enable All Constraint for Table and Database

One of the most popular questions I find still coming to via email is how to enable or disable all the constraint for single table or database.

Well, in this blog post we will not discuss the reasons why do you need them or what are the advantages or disadvantages of the same. Instead, we will go over scripts to do the same. As lots of users are seeking this script there should be good reason for the same.

-- Disable all table constraints
-- Enable all table constraints
-- ----------
-- Disable single constraint
-- Enable single constraint
-- ----------
-- Disable all constraints for database
-- Enable all constraints for database

Let me know if there is any better way to do the same.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQL SERVER – How to Learn SQL Server 2014 – Video Tutorial

As December begins the realization is that Year 2014 is about to come to an end. The month of November was a travel month for me. I have travelled to 6 different cities, 2 countries and 3 different conferences presenting and discussing SQL Server. While presenting at this conference I realized that even though SQL Server 2014 has been released for quite a while, the adoption of the product is very slow. There are two main reasons, I figured out regarding why there is not much of the adoption of SQL Server.

Reason 1: Price of the SQL Server 2014

It seems like there is heavy concern about the upgrade price for SQL Server 2014. Many are concerned that SQL Server 2014 is the expense they would not be able to justify to their boss if they are already using SQL Server 2008 R2 and SQL Server 2012. There were quite a many developers who were even concerned that there are not enough features introduced in SQL Server which can be justified as a solid reason to upgrade to SQL Server.

Well, here is where the second reason kicks in.

Reason 2: Lack of Learning Resources

Lots of people complained that there are not enough resources which can teach them SQL Server 2014. There are MSDN and few other website, but there is no real course out there which can walk users with them to upgrade them to the latest version of SQL Server. The lack of good resources out is also one of the reasons for why many organizations are not aware of what SQL Server 2014 has to offer and how it can be justified to upgrade.

SQL Server 2014 Video Course

Earlier this year I have created a Pluralsight course on SQL Server 2014 Administration New Features. The course is very popular as it covers quite a few of the most talked features of SQL Server 2014. I earlier recommended this course to quite a few who wants to upgrade themselves to SQL Server 2014. I have so far received very good feedback on this course. This course also talks about Azure, Security and Performance tuning aspects of SQL Server 2014. If you are new to SQL Server or even if you are familiar with earlier version of SQL Server but wants to upgrade yourself to the new version of SQL Server. This is the course which you want to explore and learn.

Here is the abstract of the course: Every new release of SQL Server brings a whole load of new features that an administrator can add to their arsenal of efficiency. This course takes a tour of the basic new features that were introduced, including backup, security, column store enhancements, Resource Governor, General Administration, Azure integration, extensions with SQL Server 2014 and more.

Here is the image of the actual course page which describes the outline of the course.

If you are interested in learning this course. Leave a comment and I have few passes for FREE learning of this course which I can share with you. Make sure you have provided your correct email address.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Inside Temp table Object Creation – Part II

After my previous article on “Inside Temp table object creation”, I had a number of people ask me more about this topic. There seems to be a need for understanding these basic fundamentals that we have known for ages in a refreshingly new way. Though there were tons of mails that had come to me, there was one mail that caught my attention and I was pleasantly surprised to see the same. I thought it was worth the time to explain the same here over the blogs again. The Email reads like:

“Hi Pinal,

I really liked your article on Inside TempDB Table creation article. And the steps were very useful for me to understand how tempdb works. As a fresher and into my first year of career, I somehow found courage to send you this email. I am not sure if you will reply, still thought you will have some pointers to help me. When I did the step on my local laptop, I could see the exact steps shown by you. I was so excited and I thought let me know what our dev / test environments look like and I ran the DMV for user tables there.

I do get to see a number of objects with names like #B0D42C6C, #D5F46A7C and so on. They are not like the table names that you mention inside your previous article. And even after I run the same DMV after 5-10 mins, I see a lot of them still on my system. What are these tables? They also have negative ID value as you mentioned before. Are they generated by system? Are they used by my application? How do I get to know about them?”

I think my friend you have brought a different dimension to this article. I am glad you found time to write to me and I appreciate each and every one who takes time to write to me. There is no simple questions in this world. I think this is yet another example of complex SQL can be and how we can learn from SQL Server every single day.

Now let me explain you what these temp objects are which my friend is talking about. These are also temp tables created by user but inside a Stored Procedure. To illustrate the same I am going to show you inside a code block below:

Let us first create the stored procedure that creates a temp table inside the SP:

USE AdventureWorks2012
DROP PROCEDURE TempTable_Objects
#temptable_in_sp (
name CHAR(30) NOT NULL
-- Do what ever you want with the temp table :)
-- WAIT, so that the SP doesn’t complete fast :)
WAITFOR DELAY '00:00:05'

Once the stored procedure is created, let us open two sessions and execute the stored procedure in one window and then execute the objects created inside TempDB on the second window. The two session windows are:

Session 1:  Run the SP

-- Execute the SP in Session 1
EXEC TempTable_Objects
WAITFOR DELAY '00:00:05'
EXEC TempTable_Objects

Session 2: Track the Object creation inside TempDB

-- Results when SP started
SELECT * FROM tempdb.sys.sysobjects WHERE TYPE = 'U'
WAITFOR DELAY '00:00:05'
-- Results when the first SP call ended
SELECT * FROM tempdb.sys.sysobjects WHERE TYPE = 'U'
WAITFOR DELAY '00:00:05'
-- Results when the SP was called again
SELECT * FROM tempdb.sys.sysobjects WHERE TYPE = 'U'

The output for the same look like:

As you can see, the object name is similar to the previous article. But unlike the standard temp table creation, here SQL Server is caching and keeping this so that the subsequent users can reuse the same. Hence in this particular case, since my friends said he saw a number of such values in TempDB, it means there are lots of temp tables created via the stored procedure which are still there post the session ending. The temp table object ID confirms our assumption that it is getting reused. On further quizzing my friend, I found there were reporting queries which are generating temp tables every 15-20 mins for in a scheduled manner. These were a constant behavior in their environment.

If you check SQL Server Management Studio Object Explorer, we can see these objects there too. Here is a typical snapshot of the same.

As I said before, these are great learning experience for me too and hope you learnt something new today with me in this exploration.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Customization of New Query in SQL Server Management Studio

Recently I was working with one of my friends who handles production server databases as a senior DBA. He told that recently one of the Jr. DBA was fired from his company because he has run a drop table command on a production server. He was scared and from that time onwards, every DBA has been asked to run select @@servername commands before executing any query on any server. Similar to this situation, many times a DBA wants a set of command, which they use very frequently, should be available as soon as SQL Server Management Studio is opened.

Both of the above situation can be handled by the trick explained in this blog.

Whenever we launch SQL Server Management Studio, it loads “SQLFile.sql” from the operating system and shows the text in the New Query Window. The location of this file depends on location of SSMS.exe and version of SQL Server. My machine has SQL Server 2012 and SQL Server 2014.

Here is the location of SSMS.exe on my 64 bit machine for SQL 2012

E:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio

If you are running 32 bit operating system then it would be

<Drive>:\Program Files\Microsoft SQL Server\110\Tools\Binn\ManagementStudio

Here is the mapping of SQL versions and internal version numbers (which is 110 from SQL 2012 in the above example).

Microsoft SQL Server 2014 120
Microsoft SQL Server 2012 110
Microsoft SQL Server 2008 R2 100
Microsoft SQL Server 2008 100

Once you find SSMS.exe, then we can navigate to SqlWorkbenchProjectItems\sql folder as shown below.

The file highlighted “SQLFile.sql” is the file which is loaded as a new query window.

On my machine, I have modified the file and written below

select @@version
select @@servername
Select *
from   sys.dm_exec_requests
where  blocking_session_id <> 0

After modifying, whenever you open new windows, it would be as below.

As we can see that this is EXACTLY same text which we have written in the SQLFile.sql file.

Be Aware: This file is used by all user profiles on the machine. If you delete this file by mistake, you would get below error whenever a new query window is attempted.

Microsoft SQL Server Management Studio
Cannot find template file for the new query (‘E:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\ManagementStudio\SqlWorkbenchProjectItems\SQL\SQLFile.sql’).

To fix this, you can create an empty file with the same path and name in the location as per error message. The path of the error message would vary based on installation on your machine.  Hope you found this tip on SSMS useful and do let me know if you will be using the same.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Identifying T-SQL Missing Parameter Data Types with SQL Server

I like challenges and when we are faced with a challenge – I just cannot sleep. I am in constant search for solution. More importantly I look forward for the learning experience these bring to me. Recently a senior developer was talking to me and got me thinking.

Senior Developer: Hey Pinal.

Pinal: Yes, tell me.

Senior Developer: I wanted to ask you something from SQL Server point of view.

Pinal: Sure, go ahead. Let me check if I know it.

Senior Developer: I have seen your session where you said, inappropriate data types while comparing can get into trouble in terms of performance.

Pinal: Yes, you want to see a demo?

Senior Developer: No, I understand your point of view. Having said that, I have a number of junior developers joined my team.

Pinal: Ok, now is that a problem?

Senior Developer: hahaha, no Pinal. That is not the problem. Since our code has a number of Dynamic SQL’s as part of our code. We have made sure we use parameterization techniques of sp_executesql.

Pinal: That is nice.

Senior Developer: Here is the problem, we still see our developers use these sp_executesql but they pass the parameters with wrong data types unknowingly. And I see sub-optimal plans being generated and hampering our performance from time to time during code review process.

Pinal: Ok, what is the help you need from me?

Senior Developer: Is there a way for my developers to validate if they are using the right data type when they are executing their Dynamic SQL inside SQL Server?

Pinal: Oh sure, if you are on SQL Server 2012 and above – there is a neat trick. But it involves your developers do an extra step of validating their parameter data types. Is that ok?

Senior Developer: Oh sure. Please tell me – I am all ears and will add it to our code review process and unit testing by developers.

Pinal: Don’t worry, I am going to write it as a blog post.

Knowing sp_describe_undeclared_parameters

This stored procedure returns a result set that contains metadata about undeclared parameters in a TSQL batch. It considers each parameter that is used in the @tsql batch, but was not declared in @params. It returns one row for each such parameter, with the deduced type information for that parameter. The procedure returns an empty result set if the @tsql input batch has no parameters except those declared in @params.

The template query is:

sp_describe_undeclared_parameters @tsql, @params

MSDN Documentation: sp_describe_undeclared_parameters (Transact-SQL)

Let me start by showing a Dynamic SQL where we are not sure of the parameters and what datatypes are for the statement finally. If we execute the statement as-is this will give us an error.

sp_executesql N'SELECT object_id, name, type_desc
FROM sys.indexes
WHERE object_id = @id OR NAME = @name OR type_desc = @typedesc'

The error we will get is:

Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@id".

Now if the developer wants to know what the datatypes of each of the parameters, then he can use the sp_describe_undeclared_parameters stored procedure. To start with, don’t pass the @params list.

EXECUTE sp_describe_undeclared_parameters @tsql =
N'SELECT object_id, name, type_desc
FROM sys.indexes
WHERE object_id = @id OR NAME = @name OR type_desc = @typedesc'

This will output 3 rows. One for each of the parameter defined. It deduces the type information. And this is super cool if you have never used.

Now when I showed this to the Senior Developer, he was pleasantly surprised that SQL Server can do a great job is helping us get the code right.

If you add the @params with possible parameters, those are not displayed in the output. I would generally send it without the parameter list to know the complete sequence. This is a great way to learn from SQL Server.

Do let me know if you found this useful and if you are planning to use the same in your environments.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Proof of Concepts with Windows Azure – Notes from the Field #056

[Note from Pinal]: This is a 56th episode of Notes from the Fields series. If you are DBA and Developer there is always a situation when you want to prove yourself by building a small proof of concepts of your idea. However, most of the time, it is way more complicated than we think. Building proof of the concepts required many different resources and skills. Above all there are chances that what we have built is not upto the mark and we have to rebuild the example one more time. Trust me it is never simple those tasks which looks simple from the beginning.

In this episode of the Notes from the Field series database expert John Sterrett (Group Principal at Linchpin People) explains a very common issue DBAs and Developer faces in their career – how to build proof of concepts and how to maximize the power of Azure. Linchpin People are database coaches and wellness experts for a data driven world. Read the experience of John in his own words.

Whether you know it or not cloud services are here and they are changing the way we will provide information technology services. For example, in many information technology shops it can take weeks if not months to get an instance of SQL Server up and running.  Here are some minimal action items that must be completed before DBA’s get access server to install SQL Server.  You have to order a physical server, your procurement team must approve the order, and the server has to be shipped. Once the server is received the server must be racked in the data center, cables must be connected, and the data center team needs to document their changes. Then the operations team needs to install and configure windows. I could keep going but there are a lot of things that should be done to a server before the DBA team gets its hands on it. What are you going to do if you’re a DBA and you need instance up in 30 minutes for a proof of concept? It’s becoming more common that the cloud is the answer.

Every time I need a server for a proof of concept I jump to Windows Azure.  I can quickly build a Windows Azure Machine with SQL Server provided within 30 minutes.  In this tip, I am going to walk through the steps to create your first Windows Azure Machine.

1. Get Azure Windows Account. If you don’t have one you can get a free trial. At the time of writing this tip your free trial would include $220 of credit.

Are you an MSDN Subscriber who isn’t leveraging Microsoft’s cloud service? If so, you can sign up for free monthly credit.

2. Log In to Azure Portal

3. Create New Virtual Machine

On the left hand side, click on Virtual Machines and then the add button on the bottom of the left side of the screen. This will load our wizard for creating our first virtual machine.

Now that wizard is loaded as you can see below we can select virtual machine and create it from the gallery. In the Gallery we will be able to select one of many images used that already includes SQL Server baked in.

Looking at the SQL Server images you will see you can access Enterprise Edition, Standard Edition and Web Edition for SQL 2014 down to SQL 2008 R2.


Next you can customize your image by release date. This will allow you to have different service packs or CUs. You can also select between two different tiers and sizes.  You will have to create a user name and password and you will want to keep this credential as it will be your first account.


Next you will be able to select more machine configuration options. You will get to determine where the Azure Virtual Machine is located.  Below you will see I am using my MSDN Subscription.


Finally, you will get to configure more configuration extensions to help automate or secure your virtual machine.

Finally, you will see your server being provisioned. What once use to take weeks or months can now be done in the cloud in minutes.

Are your servers running at optimal speed or are you facing any SQL Server Performance Problems? If you want to get started with the help of experts read more over here: Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Inside Temp Table Object Creation

When I wrote the article around tempdb like normal database, I had a number of people asked me how it is so simple to understand? Well, they were asking how are the tables created? How will same tables created by different sessions created inside tempdb? Are there anything different about temp tables when compared to normal tables?

These are all valid questions and sometimes we need to check the output to understand them better. So here is a simple test we are going to do to understand how temp tables are created inside SQL Server. For my example I will create a temp table of the following definition.

-- Session 1: Table creation
CREATE TABLE #temptable_test (

After the table is created, we will go ahead and check the object definition created inside tempdb database. We can look at the metadata using:

SELECT * FROM tempdb.sys.sysobjects WHERE TYPE = 'U'

The important point to note is the negative object id for the table created. Also we need to see that the table created has a long number of underscores and a number (___02). Now why is SQL Server doing this? Why couldn’t SQL Server create the table using the same name we created? The answer is simple. We have a single tempdb for the whole instance and if there are multiple sessions creating the same object, then we don’t want to have conflict in the table created.

To mimic this, let us go ahead and create a second table with the same name but different schema to check what SQL Server does. Our second table definition looks like:

-- Session 2: Table creation
CREATE TABLE #temptable_test (
FullName CHAR(100) NOT NULL

Though the names are same but the column definitions are different, what we see inside SQL Server tempdb metadata is:

As you can see, the names have different auto number extension making each of these objects unique. This is the reason why SQL Server adds the extra extension. For simplicity sake and as explained in the precious article, the temp table is removed as soon as the session is closed / completed. So let me go ahead to check the same. We will close the session 1 and see the effect.

As you can see, temp tables are similar to normal tables with the exception that the table is disposed as soon as the session is closed.

Do let me know if you have seen this behavior in your environments and how many such objects are available at any point in time in your environments today? Let me know and it will be a great learning about your very own environment.

Reference: Pinal Dave (http://blog.sqlauthority.com)