SQL Server: Test Linked Server Connection

SQL Server: Test Linked Server Connection

If you want to have access to data stored in a different server, one of the ways to do so is to make that server as a Linked Server to the current server and then query using the linked server name. Let us assume that there is a server named test available over the network and you want to get some data from there. System stored procedure sp_addlinkedserver can be used to create a linked server and fire distributed queries.

The following code adds the server test as Linked server to the current server

EXEC sp_addlinkedserver test

If you want to know if the connection to the linked server is accessible, you can make use of system stored procedure sp_testlinkedserver as shown below

EXEC sp_testlinkedserver test

It simply checks if the connection is successful. If the result is "Command(s) completed successfully.", it means the connection is successful. This is the simplest way to check if the linked server is accessible

Also check Viewing Linked Server Information in SQL Server 2005/2008

List Empty Tables in SQL Server

List Empty Tables in SQL Server

I was recently doing a clean up of my website database. I remember creating some tables on my database but never adding any new rows to it.

Here’s a simple query to list all empty tables in your SQL Server database that uses aDynamic Management View called dm_db_partition_stats which returns page and row-count information for every partition in the current database.

;WITH EmptyRows AS
(
   SELECT SUM(row_count) AS [TotalRows],
          OBJECT_NAME(OBJECT_ID) AS TableName
   FROM sys.dm_db_partition_stats
   WHERE index_id = 0 OR index_id = 1
   GROUP BY OBJECT_ID
)
SELECT * FROM EmptyRows
WHERE [TotalRows] = 0

OUTPUT 
   


Note that the results from the view is only approximate. If there are any active transactions that are inserting or deleting rows, the count may not include it.

Working with Temporary Objects in SQL Server

Working with Temporary Objects in SQL Server

In this article, we will learn about Temporary objects like Temporary tables in SQL Server. We will also see the different scoping levels for Temporary objects.

So the first question that comes to mind is why should we learn about Temporary objects? There are various scenarios where you can take the advantage of temporary objects. Some of them are as follows – 

• The data which you don’t want to persist in the database for the future usage can be stored in temporary objects.
• You may have some Computed data which you want to use in stored procedures during business processing logic.
• Sometimes you may want to make data visible to a particular session only. In that case, you can store the data into temporary objects.
• You don’t want to clean-up the data explicitly, so you can store the temporary data into temporary objects.

Now let’s start with a demonstration by creating temporary objects. You can create various temporary objects like – 

• Temporary Tables.
• Temporary Stored Procedures.
• Temporary Variables.

The temporary objects can be created as Local objects (using # sign) or global objects (using ## sign). The local temporary objects are available at session level where as the global temporary objects are available across all the sessions.

For this demonstration, I am using SQL Server 2012 with the demo database Northwind. Let’s open SQL Server Management and start creating the temporary objects as described below – 

· We will first of all create a temporary table and insert the data in the table from existing table. The script is as shown below – 



Now save the query file with the name TempObjects.sql and close the query window. After this, open the query window and then try executing the select statement

SELECT * FROM #CustomerCopy

You will get an error as shown below – .



The error says that object does not exist. If you see the script, we have created a temporary table using (#) sign. The single ‘#’ denotes that you have created a Local Temporary table. The life time of the local temporary table is till the session is on. Since we closed the query window, we lost the session.

Now lets rerun the above script and try to access the temporary object in a new query window by using the same SELECT command we just used -



The same error occurs as we lost the current session when we opened a new query window.

Global Temporary Table in SQL Server

Now lets create the same demonstration using Global (##) temporary table and analyze the life time of the table. In the above script, while creating a temporary table, use ‘##’ sign and create the table. The rest of the script will remain same except the table name with ‘##’ sign during insertion and selection. The table script will look like below – 



Run the Select statement into another query pad. You will see the following result – 



Now let’s see some examples on Select * INTO. Start by writing the following queries – 



The above query will create a temporary table which will copy all the rows from Customers table into our CustomerCopy table. If you need only schema of the table, you will have to apply a false condition at the end of the query. For example – WHERE 1=2. This condition is always set to false which will result into a copy of the table without any data.

Try viewing the temporary table metadata. For the non-temporary tables, we will make use of sp_help stored procedure. But for the temporary tables, we will have to make use of TempDB database and make use of sp_help stored procedure to view the metadata as below – 

Temporary Table with User Data Types

Let’s use the User Data Type in our Temporary table. Write the following script – 



When you try to execute the above script, you will get an error as described below – 



The above error says it cannot find the type “CustomerAddress”. But we have already created the type. Well, if you want to use User Types into Temporary tables, you will have to create them into TempDB database. So, let’s create a User Type into our TempDB and create temporary table into our Northwind database as shown below – 



The above query will get executed successfully and your temporary table will get created. You can use sp_help stored procedure to check the data types of the above created table.

Temporary Table using Dynamic Queries

Let’s now create temporary tables using dynamic query. There are couple of restrictions which you will have to consider while creating temporary tables using dynamic SQL as described below – 

• When you create temporary tables using Dynamic SQL, you cannot just first create the table and perform insert/update/delete/ select statements after creation. You will have to create dynamic SQL to create a table, then insert rows in the table and then select them and update them as per our requirements. All this has to be done using dynamic SQL query only.
• The other option could be to first create a temporary table and then use dynamic SQL to insert the data. Then use simple select statements and again use the dynamic SQL for updating the data of temporary tables. This will work. Because the temporary table is available in the Dynamic SQL.

You will have to make a clever choice of how do you want to create temporary object while working with dynamic SQL. Let’s see some examples of the same.



Now try writing a Select or Update Statement after creating the dynamic SQL and you will receive the following error.



The error shown in the above result is for the select statement which I have written after the dynamic SQL.

Create Temporary Table using Constraints

Now let’s take a look at how to create temporary tables using constraints. For this demonstration, we will create two tables as shown below – 



The above queries make uses of Primary key, Not Null and Check constraint. Now let’s try taking the reference of #Exception1 table into #Exception2 table using foreign key. The query and result of the query is as shown below – 



The above query execution tells that the Foreign key constraint cannot be enforced on local or global temporary tables. When you are creating temporary tables, you may want to create the indexes on the table for faster data retrieval operations. You can create an index for example Clustered Index, Non Clustered Index or Unique index on temporary tables. For example –

 

The above index query will create a non-clustered index on Name column of our #Exception1 table. Likewise, while creating a temporary table, you can apply identity to the column to auto generate the values, as well as you can also perform transactions with the temporary tables.

Conclusion

In this article, we have seen how to create temporary objects like temporary tables which we can use to store data which we don’t want to persist in the database. 

In the next article, we will touch base on Temporary Table Variables and also see some limitations while working with table variables compared to temporary tables.

Restore backup of database (.bak) using Sql Server 2005

Restore backup of database (.bak) using Sql Server 2005

If you intend to create a back up of your database in Server A and restore it on a Server B, here's how to go about it. Create a .bak file (Open SSMS > Database > Right Click your database > Tasks > BackUp > Specify the destination).

To do it using script, check my blog over here.

Once the .bak is created, copy this file from Server A to a Server B. We will assume that the file has been copied at the location ''C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\" at Server B. It can any location that you desire.

How to restore the .bak file

Use this query:

RESTORE DATABASE [SouthWind]
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\South.BAK'
WITH REPLACE,
MOVE 'SouthWind_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\SouthWind_Data.MDF',
MOVE 'SouthWind_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\SouthWind_Log.LDF'

where FromDisk is where the .bak file is kept on Server B and MOVE specifies the location of the data and log file.

Change the Default Language for SQL Server

Change the Default Language for SQL Server

I sometimes get questions like – How do I change the default language of SQL Server 2005/2008 from English to Spanish, so as to display the SQL Server system messages in that language.

The answer is that if you have installed a Localized version of SQL Server (say in Spanish), then you get the system messages in two languages – US English and Spanish. In this case, you just have to make sure that the Default Language selected is Spanish.

There are 3 common ways to do so: Using SSMS, T-SQL and PowerShell. We will use SSMS and T-SQL in this article.

USING SSMS

To do so, open SQL Server Management Studio (SSMS) > Right click Server in Object Explorer > Properties > Advanced > Check the ‘Default Language’ property and make sure it is set to Spanish.

Select the language from the DropDown and click OK. Then Stop and Start SQL Server for the new language settings to take effect.

USING T-SQL

If you want to change the default language of SQL Server to a language of your choice using T-SQL, follow these steps:

1. Look up the sys.messages catalog view to check if SQL Server supports a message in your local language.

SELECT msg.language_id, lang.langid, alias 
FROMsys.messages AS msg
JOINsyslanguages AS lang
ON lang.msglangid = msg.language_id
GROUP BY  msg.language_id, lang.langid, alias

You get only 12 rows, which means SQL Server supports system messages and user-defined messages in only 12 local languages. (FEEDBACK: SQL Server should support more local languages!! By the way, check the sp_addmessage which offers some respite)

2. Once you know the LanguageID for your local language, for Spanish it is 5, just use this command to change the default language of SQL Server

EXEC sp_configure "default language", 5
RECONFIGURE WITH OVERRIDE

Start and Stop SQL Server. You can now verify if the local language has changed using the following command

sp_configure 'default language'

If the config_value shows 5, then you are good to go!

Alternatively, if you do not have a localized version of SQL Server, then you can use the SELECT @@language command

SET Language Spanish
SELECT @@language, @@langid

Strangely, if you do not explicitly say ‘SET Language Spanish’ and just run the @@language, @@langid command, the message still shows US_English, even if you have followed the steps I had shown above. That’s why i have explicitly used SET Language Spanish option.

Update: Please note that the contents of this post is valid if you have installed a Localized version of SQL Server, in our case Spanish.

If you have not installed the localized version of SQL Server, the default language is US English. If you need to change the default language on this machine, then you will have to change the default language for individual logins, as doing it on a server level won't work. Also as Geri Reshef points out in the comments section, you will have to use SET LANGUAGE to change the language to Spanish.

SQL Server: All About SQL Server Statistics : How to Detect outdated Statistics ?

SQL Server: All About SQL Server Statistics : How to Detect outdated Statistics ?

In the last post, we have discussed about the auto update statistics threshold and concluded that auto updated statistics threshold are good enough to get optimal performance in some workload. In many cases,a manual update of statistics will help to obtain better performance. In this post let us discuss, how to detect the outdated statistics.

In SQL server 2005 onwards, SQL server uses the ColModCtr to keep track of the changes in the leading column of the statistics. Unfortunatly that is not exposed through any DMV or system view in SQL server 2005 or SQL server 2008. In SQL server 2008 R2 (SP2) onwards, sys.dm_db_stats_properties will give the details of statistics along with the changes in the leading column of the statistics.

For older versions of SQL server users, we need to depends on rowmodctr available in the sys.sysindexes. As per BOL : In SQL Server 2005 and later, rowmodctr is not fully compatible with earlier versions.In earlier versions of SQL Server, the Database Engine maintained row-level modification counters. Such counters are now maintained at the column level. Therefore, the rowmodctr column is calculated and produces results that are similar to the results in earlier versions, but are not exact.

The below query will give an estimate of changes in the statistics.

SELECT 
     TableName=OBJECT_NAME(i.OBJECT_ID)
    ,ObjectType=o.type_desc
    ,StatisticsName=i.[name]
    ,statisticsUpdateDate = STATS_DATE(i.OBJECT_ID, i.index_id)
    ,RecordModified=si.rowmodctr
    ,NumberofRecords=si.rowcnt
FROM sys.indexes i 
JOIN sys.objects o ON    i.OBJECT_ID=o.OBJECT_ID
JOIN sys.sysindexes si ON    i.OBJECT_ID=si.id
    AND i.index_id=si.indid 
WHERE  o.TYPE <> 'S' AND STATS_DATE(i.OBJECT_ID, i.index_id) IS NOT NULL
UNION ALL
SELECT 
     TableName=OBJECT_NAME(o.OBJECT_ID)
    ,ObjectType=o.type_desc
    ,StatisticsName=s.name
    ,statisticsUpdateDate= STATS_DATE(o.OBJECT_ID, s.stats_id)
    ,RecordModified=si.rowmodctr
    ,NumberofRecords=ir.rowcnt
FROM sys.stats s INNER JOIN sys.objects o ON s.OBJECT_ID=o.OBJECT_ID
JOIN sys.sysindexes si ON s.OBJECT_ID=si.id AND s.stats_id= si.indid
INNER JOIN (SELECT id,rowcnt FROM sys.sysindexes WHERE indid IN (0,1)) IR
ON IR.id=o.OBJECT_ID  WHERE  o.TYPE <> 'S'  
AND (s.auto_created=1 OR s.user_created=1)AND STATS_DATE(o.OBJECT_ID, s.stats_id) IS NOT NULL




By keeping this as a base, based on the workload/types of query running, we can schedule the statistics update at appropriate interval instead of blindly updating all statistics.

SQL Server: Performance Tuning :Understanding Set Statistics Time output

SQL Server: Performance Tuning :Understanding Set Statistics Time output

In the last post we have discussed about Set Statistics IO and how it will help us in the performance tuning. In this post we will discuss about the Set Statistics Time which will give the statistics of time taken to execute a query.

Let us start with a example.

USE AdventureWorks2008
GO
            DBCC dropcleanbuffers
            DBCC freeproccache

GO
SET STATISTICS TIME ON
GO
SELECT *
    FROM Sales.SalesOrderHeader SOH INNER JOIN  Sales.SalesOrderDetail SOD ON
            SOH.SalesOrderID=SOD.SalesOrderID
    WHERE ProductID BETWEEN 700
        AND 800
GO
SELECT *
    FROM Sales.SalesOrderHeader SOH INNER JOIN  Sales.SalesOrderDetail SOD ON
            SOH.SalesOrderID=SOD.SalesOrderID
    WHERE ProductID BETWEEN 700
        AND 800




            
















There aretwo select statement in the example .The first one is executed after clearing the buffer. Let us look into the output.

SQL Server parse and Compile time : When we submit a query to SQL server to execute,it has to parse and compile for any syntax error and optimizer has to produce the optimal plan for the execution. SQL Server parse and Compile time refers to the time taken to complete this pre -execute steps.If you look into the output of second execution, the CPU time and elapsed time are 0 in the SQL Server parse and Compile time section. That shows that SQL server did not spend any time in parsing and compiling the query as the execution plan was readily available in the cache. CPU time refers to the actual time spend on CPU and elapsed time refers to the total time taken for the completion of the parse and compile. The difference between the CPU time and elapsed time might wait time in the queue to get the CPU cycle or it was waiting for the IO completion. This does not have much significance in performance tuning as the value will vary from execution to execution. If you are getting consistent value in this section, probably you will be running the procedure with recompile option.

SQL Server Execution Time: This refers to the time taken by SQL server to complete the execution of the compiled plan. CPU time refers to the actual time spend on CPU where as the elapsed time is the total time to complete the execution which includes signal wait time, wait time to complete the IO operation and time taken to transfer the output to the client.The CPU time can be used to baseline the performance tuning. This value will not vary much from execution to execution unless you modify the query or data. The load on the server will not impact much on this value. Please note that time shown is in milliseconds. The value of CPU time might vary from execution to execution for the same query with same data but it will be only in 100's which is only part of a second. The elapsed time will depend on many factor, like load on the server, IO load ,network bandwidth between server and client. So always use the CPU time as baseline while doing the performance tuning.