Microsoft Sql Server Tutorials

Using sys.dm_os_performance_counters to measure Transactions Per Second

noreply@blogger.com (Suprotim Agarwal) — Wed, 15 Feb 2012 05:17:00 +0000

Performance Monitor tool in SQL Server represents aspects of system performance, such as CPU Usage % , Memory Paging etc. The sys.dm_os_performance_counters Dynamic Management View exposes data of all performance counters for the particular instance of SQL Server. This view is extremely useful when you have to present the performance data of your database in your dashboard.

Note: If you are handling multiple databases, check out the handy SQL Diagnostic Manager tool from Idera which monitors and manage your entire SQL Server environment from a single console

We have touched upon the usefulness of the sys.dm_os_performance_counters in my previous post Removing Deprecated Code and Future Proofing your Queries. In this post, we will see another use of the sys.dm_os_performance_counters view to measure SQL transactions per second for a database. Here’s a very handy query written by Joe Stefanelli

DECLARE @ctr bigint

SELECT @ctr = ctr
    FROM sys.dm_os_performance_counters
    WHERE counter_name = 'transactions/sec'
        AND object_name = 'SQLServer:Databases'
        AND instance_name = 'SomeDBName'

WAITFOR DELAY '00:00:01'

SELECT ctr - @ctr
    FROM sys.dm_os_performance_counters
    WHERE counter_name = 'transactions/sec'
        AND object_name = 'SQLServer:Databases'
        AND instance_name = 'DotNetCurry2'

As you can see, were taking the difference of two values for a delay of one second to get the number of transactions per second. In future posts, we will see some more queries using this useful DMV.

SQL Server 2012 Free Cook Book and Training

noreply@blogger.com (Suprotim Agarwal) — Tue, 07 Feb 2012 04:38:00 +0000

Microsoft has created some free content designed to get started with SQL Server 2012. You can use the SQL Server 2012 Early Adoption Cook Book Wiki at http://social.technet.microsoft.com/wiki/contents/articles/6967.sql-server-2012-early-adoption-cook-book.aspx. You can bookmark this link as Microsoft to update this wiki with the latest SQL Server 2012 information as and when it is available.

Microsoft also released the SQL Server 2012 Developer Training Kit which includes technical content including labs, demos and presentations designed to help you learn how to develop SQL Server 2012 database and BI solutions.

You can download SQL Server 2012 Training Kit over here

Declaring Length for VARCHAR and NVARCHAR datatypes IS Necessary

noreply@blogger.com (Suprotim Agarwal) — Mon, 23 Jan 2012 07:51:00 +0000

I have seen some new SQL Server developers declaring Varchar and Nvarchar data types without specifying a length, since it is optional. Although this works in some other programming languages like C#, SQL Server behaves differently and this is a bad practice overall. Let us see with an example of what’s wrong with not declaring a length for the Char or Varchar data type.

Consider the following t-sql code

OUTPUT

Surprised seeing the output? When a length is not specified while declaring a variable, the default length is 1 and when a length is not specified when using the CAST and CONVERT functions, the default length is 30. That’s the reason you get the output you see above.

Note: Try doing the same when you create a table with a CHAR column, without specifying the length.

To get the desired results, rewrite the t-sql code in the following manner:

DECLARE @v1 AS char(30)
DECLARE @v2 AS varchar(60)
SET @v1 = 'abcdefghijkl'
SET @v2 = 'The quick brown fox jumped over the lazy dog'
SELECT
DATALENGTH(@v2),
DATALENGTH(CAST(@v2 as varchar(60)))
GO

Now you get the desired results

Another place where you got to be careful is while creating stored procedures with parameters. If you have created a stored procedure that accepts a parameter with a VARCHAR datatype with no length, you will be in for a surprise to learn that SQL Server silently truncates the string and adds some leading characters.

Overall, always specify a length for the CHAR, VARCHAR, NVARCHAR and similar data types. It’s a good practice!

Restoring SQL Server Database: Points to Consider

noreply@blogger.com (Madhivanan) — Mon, 09 Jan 2012 11:33:00 +0000

Here are some important points to consider while restoring a database backup. Let us first create a sample database named test using the following code

A new database named test will be created in your server and data and log files are created in the H: drive.

Let us assume that you regularly take backup of this database using the below code:

backup database test to disk='h:\test.bak'

Now if you want to create another database or restore this test database to a new database named testing, you can use the following code

restore database testing from disk='h:\test.bak'

However executing the code above will give you the following error

Msg 1834, Level 16, State 1, Line 2
The file 'h:\test_dat.mdf' cannot be overwritten. It is being used by database 'test'.
Msg 3156, Level 16, State 4, Line 2
File 'test_dat' cannot be restored to 'h:\test_dat.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 1834, Level 16, State 1, Line 2
The file 'h:\test_log.ldf' cannot be overwritten. It is being used by database 'test'.
Msg 3156, Level 16, State 4, Line 2
File 'test_log' cannot be restored to 'h:\test_log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 2
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

This error occurs because the data and log files are currently being used by the database test. So you need to give different names for those files while restoring, as shown below

restore database testing from disk='h:\test.bak'
with
move 'test_dat' to 'h:\testing.mdf',
move 'test_log' to 'h:\testing.ldf'

The above code will work fine and new database will be created with the name testing.

Just be aware of this point while restoring a backup of existing database!

Most Popular SQL Server Articles in 2011

noreply@blogger.com (Suprotim Agarwal) — Thu, 29 Dec 2011 17:50:00 +0000

With 2012 fast approaching and 2011 drawing to an end, we've put together our list of the Most Popular SQL Server articles on SQLServerCurry.com this year.

I would like to thank each one of you who has visited my blog or contributed to it by submitting a Guest post, Subscribing to RSS Feed, by joining me on Twitter, retweeting posts or promoting the articles and giving regular feedbacks via rating, comments or Emails. A special note of thanks to Madhivannan and Pravin Kumar for their contributions.

Here are some articles that were liked the most by readers like you. Have a very Happy New Year 2012!

SQL Server Administration Articles

Why is RAID So Important for Databases?

SQL Server: Removing Deprecated Code and Future Proofing your Queries

Every .NET Developer Should Know About the Database they are working with

SQL Server: Move Data to a Different Table using OUTPUT clause

LocalDB Denali: New version of SQL Express

SQL Server: Top 10 Cached Queries

Troubleshoot Deadlocks using SQL Server Profiler 2005/2008

Monitor Running Processes in SQL Server 2005/2008

SQL Server: Move Table to a new File Group

SQL Server: Identify Memory and Performance Issues in T-SQL Queries and Fix them

Repair SQL Server Database marked as Suspect or Corrupted

Fastest Way to Update Rows in a Large Table in SQL Server

SQL Server: Export Table to CSV

Load Comma Delimited file (csv) in SQL Server

Backup Compression in SQL Server 2008

T-SQL Articles

SQL Server Bit Data Type and how it Stores Values

SQL Server: DateTime vs DateTime2

SQL Server Tutorials on Date Time

SQL Server: Calculate Summary and Column Summary

Concatenate Strings in SQL Server - Different ways

SQL Server CLR User Defined Function using Visual Studio 2010

SQL Queries – beyond TRUE and FALSE

SQL CLR Stored Procedure using Visual Studio 2010

SQL Server: Search Similar String in a Table

SQL Server: Count based on Condition

SQL Server: Insert Date and Time in Separate Columns

SQL Server: Combine Multiple Rows Into One Column with CSV output

SQL Server: First and Last Sunday of Each Month

SQL Server: Group By Year, Month and Day

SQL Server: Return Multiple Values from a Function

UNPIVOT example in SQL Server

Date Difference in SQL Server in Days, Hours, Minutes and Seconds

MINUS Keyword in SQL Server – Alternatives

Rollback Nested Transactions in Stored Procedure - SQL Server

Rollback Transaction in SQL Server

Select TOP With TIES in SQL Server

Assign Result of Dynamic SQL to a Variable in SQL Server

noreply@blogger.com (Madhivanan) — Sat, 24 Dec 2011 09:47:00 +0000

Suppose you have dynamic sql that returns a single value and you want to copy it to a variable. For eg: you want the total count of the table copied to a variable. This is possible in SQL Server using the following methods:

Method 1 : Use sp_executesql system stored procedure

declare @counting int
execute sp_executesql
          N'select @count=count(*) from sys.objects',
          N'@count int output',
          @count =@counting output;
select @counting as counting

In the above method, count is assigned to the variable @count which outputs to @counting

Method 2 : Use table variable

declare @t table(counting int)
declare @counting int
insert into @t
exec('select count(*) from sys.objects')
select @counting=counting from @t
select @counting as counting

The above t-sql code copiesthe resultset to table variable @t and the count is copied to variable @counting

OUTPUT

Why is RAID So Important for Databases?

noreply@blogger.com (Suprotim Agarwal) — Mon, 19 Dec 2011 08:08:00 +0000

A good server design has no, or very few, single points of failure. One of the most common server component that fails, are disks. So data redundancy becomes essential to recoverability. Redundant Array of Independent/Inexpensive Disks (RAID) is a disk system that provides better fault-tolerance by making use of redundancy of disk(s).

RAID is a disk system that contains multiple disk drives, called an array, to provide greater performance, fault tolerance, storage capacity, at a moderate cost. While configuring your server system, you typically have to make a choice between hardware RAID and software RAID for the server’s internal disk drives

RAID systems are widely used as storage solutions to get the best I/O performance, depending whether the application is write intensive or read intensive. DBA RayRankins mentions in his book that for database-related applications, in order to minimize disk head movement and maximize I/O performance, it’s a good practice to spread random I/O’s (data changes) and sequential I/O’s (for the transaction log) across different disk subsystems. I agree and endorse his view, as SQL Server, or for that matter any other database, is very much an I/O intensive system.

I will be using SQL Server as an example to explain the importance of RAID in databases, however you can implement these concepts in your choice of database. The concepts more or less, remain the same

Although RAID is not a part of a database like SQL Server, implementing RAID can directly affect the way SQL Server performs.There are many RAID arrays available such as RAID 0, RAID 1, RAID 3, RAID 4, RAID 5, RAID 6, RAID 10 and RAID 01. In this article, we will discuss the ones you will likely encounter as a SQL Server DBA i.e. RAID levels 0, 1, 5 and 10 and also discuss their advantages and disadvantages from a fault tolerance and performance perspective

Note: RAID is not a replacement for backups. Backups are very essential for any system.

Different RAID Levels (Advantages and Disadvantages)

We will discussing only RAID 0, 1, 5 and 10 (database perspective) .

RAID 0 – Also known as Disk Striping, RAID 0 does not provide redundancy or fault tolerance but instead writes data to two drives, in an alternating fashion. This provides the best read write I/O performance. If you had 8 chunks of data, for example, chunk 1, 3, 5, and 7 would be written to the first drive, and chunk 2, 4, 6, and 8 would be written to the second drive, but all in a fixed (sequential) order. RAID 0 has a simple design, easier to implement and no overheads for parity. The drawback is that any piece of data is on only one disk, so if one disk fails, data stored within those disks are lost.

RAID 1- Also known as Disk Mirroring, RAID 1 provides a redundant, identical copy of a selected disk and thus provides good fault tolerance. It can be implemented with 2 drives. The disadvantage is that it has a big storage overhead and a high cost/capacity ratio

RAID 5 - Also known as Disk Striping with Parity, stripes data across multiple drives and writes parity bits across drives. Data redundancy is provided by the parity information. It can be implemented with 3 or more disks and is a popular choice amongst DBA’s. Since data and parity information are arranged on the disk array, two types of information are always on different disks. If one disk fails, just replace it with a new disk and the array rebuilds itself. RAID 5 has a higher read rate and makes good use of capacity. The drawback of RAID 5 is slower write rates and slow rebuild times.

RAID 10 - Also known as mirroring with striping, RAID 10 is a combination of RAID1 + RAID0. RAID 10 uses a striped array of disks that are then mirrored to another identical set of striped disks. This array level uses at least four hard disks and additional disks must be added in even numbers. The data is first placed into mirrored pairs at the lower level. Next, the controller selects a member from each mirrored pair and stripes the data into a new logical volume. Since RAID 10 writes in a random fashion, it provides best performance with a write-intensive application (like video editing). The drawback is that it is expensive.

Which RAID is Suitable for my Database?

Now that you have an overview of RAID levels, let’s look at which RAID is suitable for a database. The answer to this question depends on a variety of factors. Do you want availability, performance or cost? What are your requirements for fault tolerance and performance? Here’s a quick snapshot of the performance and fault tolerance provided by RAID

When it comes to a database like SQL Server, no one RAID level will suit your need. In most cases, SQL Server performs large reads and small writes. So for databases, where write operations are more, RAID 5 is not a good choice. On the contrary, RAID 10 is a good option for databases with more write operations.

Here are some points and best practices to keep in mind while deciding the RAID system for your database.

Your Data, Logs, tempdb, and backups should be on separate physical drives or a set of disks (array).
RAID1 is often chosen to store operating systems, binaries, index file groups and database transaction log files. Critical to log and index performance is fault tolerance and a good write speed.
Since log file are written sequentially and are read only for recovery operations, recommended RAID for Log Files is RAID 1 or 10. If your RAID 1 is at a 100% usage, choose RAID 10 for better performance.
For data files with random access and read heavy data volumes, striping is important. So recommended RAID is 5 or 10.
For data files which require good write performance, RAID 10 is recommended. Use a battery backed caching RAID controller for better write performance
For tempdb files with good read/write performance RAID 0, 1 or 10 is recommended. Although tempdb contains temporary data and DBA’s often go in for RAID 0 for tempdb, keep in mind that SQL Server requires tempdb to carry out many of its activities. So if you want your system to be always available, rethink RAID 0.
Putting log files or tempdb on a RAID 5 array is not recommended, since RAID 5 does not perform well for write operations. DBA's although have conflicting opinions over this point.
Choose small fast drives, over large slow drives.

Make sure you do your own research, so you can make an informed decision! In a real scenario, you may not have the luxury of deciding your server configurations due to the cost associated with it. So keeping these points in mind, may help you in such scenarios.

Please use the comments section and share your opinions!

Deleting Leading Zeros in a String (SQL)

noreply@blogger.com (Madhivanan) — Wed, 14 Dec 2011 09:58:00 +0000

A common issue while importing data from different source into SQL Server is often numbers get prefixed with zeroes. If you want to delete these leading zeroes in a string, you can use the following methods:

Method 1 : CAST to Bigint

declare @s varchar(100)
set @s ='0000004007340007402100'
select cast(@s as bigint)

CASTing the string to Bigint will automatically delete the leading zeroes

Method 2 : Use Replace function

declare @s varchar(100)
set @s ='0000004007340007402100'
select replace(ltrim(replace(@s,'0',' ')),' ','0')

The T-SQL code shown above first replaces all zeroes to a single space. The LTRIM function deletes all leading spaces and the second replace function, replaces all spaces to zeroes, so that all leading zeroes are deleted.

OUTPUT

Find Last Run Query in SQL Server

noreply@blogger.com (Suprotim Agarwal) — Fri, 09 Dec 2011 09:00:00 +0000

Have you ever wondered what SQL query was last executed by your users across all SQL Server databases on your server? I have seen some solutions on the internet that use the sysprocesses view to retrieve this information. In this post, I will show you how this information can be retrieved better using Dynamic Management Views.

Please use this query:

SELECT conn.session_id, sson.host_name, sson.login_name, 
 sqltxt.text, sson.login_time,  sson.status
FROM sys.dm_exec_connections conn
INNER JOIN sys.dm_exec_sessions sson 
ON conn.session_id = sson.session_id
CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS sqltxt
ORDER BY conn.session_id

Here I have utilized the sys.dm_exec_connections Dynamic Management View, in conjunction with the sys.dm_exec_sessions DMV and sys.dm_exec_sql_text Dynamic Management Function (DMF) to return the last query executed against all SQL Server databases, in that server.

Here’s a quick overview of what these DMV’s and DMF do

sys.dm_exec_connections - Returns information about the connections established to this instance of SQL Server and the details of each connection

sys.dm_exec_sessions - Returns one row per authenticated session on SQL Server. sys.dm_exec_sessions is a server-scope view that shows information about all active user connections and internal tasks. This information includes client version, client program name, client login time, login user, current session setting, and more

sys.dm_exec_sql_text - Returns the text of the SQL batch that is identified by the specified sql_handle

In the last statement, we are passing the value contained in the most_recent_sql_handle column of this DMV to the sys.dm_exec_sql_text DMF. The DMF returns the text of the sql query, whose sql_handle we passed to it. This sql_handle that we passed, uniquely identifies the query.

Here’s the output

Also check out Find the Most Time Consuming Code in your SQL Server Database

Activity Monitor in SQL Server

noreply@blogger.com (Suprotim Agarwal) — Sun, 04 Dec 2011 12:39:00 +0000

Activity Monitor is a handy tool in SQL Server to quickly see performance hot spots in the server, network and database activity. One of the most commonly performed tasks in Activity Monitor is to kill a stubborn connection that will not release its resources. In SQL Server 2008, this tool has undergone a major facelift from the 2005 version and helps in tracking your SQL server performance even better than before.

To view Activity Monitor in SQL Server 2008, right-click the instance name and choose Activity Monitor or click the Activity Monitor icon on the standard toolbar.

In SQL Server 2005, Activity Monitor can be viewed in SSMS by connecting to the server with Object Explorer, expanding ‘Management’, and then double-click ‘Activity Monitor’.

Once opened, the Activity Monitor Dashboard View contains four graphs which can help identity any abnormal activity in the Database. The graphs include % Processor Time (SQL Server), Waiting Tasks, Database I/O and Batch Requests. The default graph refresh rate is 10 seconds, but you change that easily by right-clicking any of the four graphs and selecting the appropriate refresh interval.

This snapshot is very helpful to get a quick performance snapshot without the need to use other monitoring tool for the same purpose.

This dashboard also contains expandable/collapsible panes to view detailed information about Processes, Resources, I/O and Expensive Queries. Just click on the expand button to the right of each pane to view the detailed information. Here’s a quick overview of the different graphs and what they show.

Processor Time - The percentage of elapsed time that the processor spends to execute non-idle threads for the instance across all CPUs. The Processes pane gives information on what processes are running, what resources are being utilized etc. You can even right-click a process and choose Trace Process in SQL Server Profiler. Presto!

Waiting Tasks - The number of tasks that are waiting for processor, I/O, or memory resources. The Resource Waits pane details the processes waiting for other resources on the server. It shows the latest information from several DMVs like the sys.dm_os_wait_stats

Database I/O – Information on data and log files for system and user databases. Provides the transfer rate in MB/Sec, of data from memory to disk, disk to memory, or disk to disk. The pane contains information to quickly detect a contention in disk I/O.

Batch Requests/sec - The number of SQL Server batches that are received by the instance. The Expensive Query pane lets you find and tune expensive queries. You can even right-click any query and view its graphical execution plan. Also see Find the Most Time Consuming Code in your SQL Server Database

Note: To view the Activity Monitor in SQL Server, a user must have VIEW SERVER STATE permission. Also make sure you close the Activity Monitor tool in SSMS when it is not required.

SQL Server Download Links All Editions

noreply@blogger.com (Suprotim Agarwal) — Wed, 30 Nov 2011 04:47:00 +0000

With SQL Server 2012 RC0 recently announced and a plethora of previous editions already available, it can get tedious to keep track of all these downloads. In this post, I will attempt to share the download links of all SQL Server versions - SQL Server 2012, SQL Server 2008 & R2 and SQL Server 2005. I will update this post and the links as and when applicable.

SQL Server 2012 Download Links

Microsoft SQL Server 2012 RC0 enables a cloud-ready information platform.

SQL Server 2012 Release Candidate 0 (RC0)

SQL Server 2012 Express RC0

SQL Server 2012 PowerPivot for Microsoft Excel 2010 Release Candidate 0 (RC0)

SQL Server 2012 RC0 Master Data Services Add-in For Microsoft Excel

SQL Server 2012 Release Candidate 0 (RC0) Manageability Tool Kit

SQL Server Data Tools CTP4

SQL Server 2012 Developer Training Kit Web Installer Preview

SQL Server 2008 R2 Download Links

SQL Server 2008 R2 Evaluation

SQL Server 2008 R2 Service Pack 1

SQL Server 2008 R2 RTM – Express

SQL Server 2008 R2 SP1 - Express Edition

SQL Server 2008 R2 RTM - Management Studio Express

SQL Server 2008 R2 RTM - Express with Management Tools

SQL Server 2008 R2 RTM - Express with Advanced Services

SQL Server 2008 R2 VHD

SQL Server 2008 Download Links

SQL Server 2008 Trial

SQL Server 2008 Service Pack 3

SQL Server 2008 Express

SQL Server 2008 Express Edition Service Pack 3

SQL Server 2008 Management Studio Express

SQL Server 2008 Express with Tools

SQL Server 2005 Download Links

SQL Server 2005 Evaluation is no longer available. You can use the Express Edition

SQL Server 2005 SP4 RTM

SQL Server 2005 Express Edition

Microsoft SQL Server 2005 Express Edition Service Pack 3

SQL Server Management Studio Express

SQL Server: Test Linked Server Connection

noreply@blogger.com (Madhivanan) — Thu, 24 Nov 2011 03:38:00 +0000

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

SQL Server: Create Objects Automatically in New Databases

noreply@blogger.com (Madhivanan) — Sat, 19 Nov 2011 03:29:00 +0000

Suppose whenever a new database is created, you also want to create a stored procedure automatically (by default) in this new database. Let us see the simplest way to do this.

The Model database is a system database which SQL Server uses as a model to create new databases. So if you create any object in the model database, that object will be created automatically in all newly-created databases. Let us see this with the following examples

Create this test procedure in model database

use model
GO
create procedure test
as
select 100 as number

Now execute this procedure

EXEC test

It displays the value 100.
Now create another database named test

CREATE database test
GO

Now execute the same procedure in test database

use test
GO
EXEC test

As you can see, executing the above statement displays the value 100 since this value was taken from the stored procedure in the model database and is now available with test database too.

Feel free to share other approaches to create objects automatically in a database.

Generate AlphaNumeric Identity Column in SQL Server

noreply@blogger.com (Madhivanan) — Mon, 14 Nov 2011 04:58:00 +0000

Let us suppose you have a requirement where you want to have an alphanumeric identity column that has characters and numbers and you want these numbers to be incremental. Eg: Customer ids like cus0000001, cus0000002, etc. You can use any of the following methods

Method 1 : Store alphanumeric data in column

declare @t table(id char(10), names varchar(100))
insert into @t
select 'cus0000001','ramesh' union all
select 'cus0000002','suresh'

declare @id int
select @id=MAX(substring(id,4,len(id)))*1 from @t

insert into @t
select 'cus'+RIGHT(REPLICATE('0',7)+cast(@id+1 as varchar(7)),7),'nilesh'
select * from @t

In the above code, @id is assigned the maximum value of the number excluding characters "cus", which is used again in the INSERT statement

Method 2 : Use only identity column of int datatype and append alphabets in select statement

declare @t table(id int identity(1,1), names varchar(100))
insert into @t (names)
select 'ramesh' union all
select 'suresh' union all
select 'nilesh'

select 'cus'+right(replicate('0',7)+cast(id as varchar(7)),7) as id,names from @t

In the above code, an alphanumeric string is built dynamically in the select statement

SQL Server: Find Common Rows in Tables using INTERSECT

noreply@blogger.com (Madhivanan) — Mon, 07 Nov 2011 06:46:00 +0000

Suppose you have a SQL Server database with tables having similar structures and you want to find out similar rows among these tables. The most common method is to use a JOIN on all the columns.

Consider the following tables

create table #t1 (id int, names varchar(100))
create table #t2 (id int, names varchar(100))
create table #t3 (id int, names varchar(100))

insert into #t1
select 1,'test1' union all
select 2,'test2'
insert into #t2
select 1,'test1' union all
select 3,'test2'
insert into #t3
select 1,'test1' union all
select 20,'test2'

select t1.* from #t1 as t1
inner join #t2 as t2 on t1.id=t2.id
inner join #t3 as t3 on t1.id=t3.id

OUTPUT

The above select statement joins all these three tables by all columns to get similar rows among these tables. Then these similar rows in all three tables are displayed.

The problem with this approach is that if the table has many columns, you will need to specify all the columns in the join statement, which will make maintaining these queries a nightmare.

Another easy method to find common rows without actually specifying any columns is to use the INTERSECT Operator

OUTPUT

SQL Server: Handling Multiple Result sets in a Procedure

noreply@blogger.com (Madhivanan) — Thu, 03 Nov 2011 05:03:00 +0000

Suppose you have a SQL Server stored procedure that returns multiple result sets and you want to store all these results into another table. I will show you how to do so. Consider the following stored procedure

create procedure test
as
select 1 as id, 'test1' as name
select 2 as id, 'test2' as name

When you execute this procedure, it returns two result sets. The following code will copy these two result sets in a table variable

declare @t table(id int, names varchar(100))
insert into @t
exec test
select * from @t

Note: In order to copy multiple result sets from a stored procedure, both result sets should have the same number of columns and datatype which are compatible with the target table. If not, an error message will be thrown for invalid number of columns/data type

Add Client Name in SQL Profiler Results

noreply@blogger.com (Madhivanan) — Sun, 30 Oct 2011 03:53:00 +0000

Often you may need to analyze results from the SQL Server profiler to know blocking CPU time, Reads etc. By default the profiler shows you a set of columns in the grid. One of the columns is the ClientProcessId. This column gives information about the Id of the client that accesses the server.

If you want to know the name of the client, you can add that column in the grid using the following steps

Start SQL Server profiler > Navigate to File > Templates > Edit template

A new window will open. Click on "Events Selection" Tab. At the bottom, check the option labeled "Show all columns"

In the window, check Hostname checkbox in the Audit Login event as shown above and click the Save button. Now Restart the Profiler. It will start showing the client name too in the results.

SQL Server File Location: Points to Consider

noreply@blogger.com (Madhivanan) — Thu, 27 Oct 2011 03:55:00 +0000

In SQL Server, file locations can be specified in some commands like during bulk insert, listing directory information, using OPENROWSET function, etc. When you specify file location, you should note the following points:

1. By default SQL Server searches for the file location in Server's directory. For example, in a Bulk insert command, if you specify D:\emp.txt, it is by default searched in the Server's
directory. If your server name is myServer, SQL Server will try to find out the file path in that server only.

2. SQL Server will not recognize client's file location until specified using the UNC path. For example say your Server name is myServer and the SQL Server client is installed in your local system named myClient that connects to Server. Say you have a file named emp.txt in your computer’s D drive and you want to specify that file location in the BULK INSERT command. In this case, the command should be

3. When UNC path is specified, the file should be given atleast a read access to the server, in which

the Query is executed. In the above example, D:\emp.txt file should be given atleast read only access on the server myServer. Otherwise an error "File not found error" will be thrown

The same applies to other functions as well which accept file locations - such as xp_cmdshell, OPENROWSET etc.

SQL Server: LPAD and RPAD functions equivalent

noreply@blogger.com (Madhivanan) — Fri, 21 Oct 2011 04:53:00 +0000

Oracle has two formatting functions LPAD and RPAD which formats the number by appending leading and trailing zeros respectively. SQL Server does not have direct equivalent functions.

However you can simulate these functions using other string functions available in SQL Server. Let us take a practical example. Suppose you want to export data to fixed format file and the requirement is that the number should be 10 digits long, in such a way that if the total number of digits is less than 10, the remaining digits should be filled with zeroes.

Consider the following example

declare @num int
set @num=872382
select
right(replicate('0',10)+cast(@num as varchar(15)),10) aS lpad_number,
left(cast(@num as varchar(15))+replicate('0',10),10) as rpad_number

The above code shows numbers in two formats. The first is left padded and second is right padded. The replicate function is used to replicate 0 for 10 times and the actual number is converted to string and then appended with zeroes.

The right function picks the last 10 digits from the result, so that it has 4 leading zeroes, as the original number shown in the code above has only 6 digits. We then reverse the same by converting a number to varchar and appending leading zeroes at the end. We then use the LEFT function which picks the number where last 4 digits are zeroes.

OUTPUT

SQL Server: Schedule Jobs every Alternate Week

noreply@blogger.com (Madhivanan) — Sun, 16 Oct 2011 07:30:00 +0000

SQL Server agent is used to schedule a job that has to run periodically. If you are a DBA, one of your admin tasks is to schedule jobs. Suppose you are in a situation to run a set of codes every alternate Saturday, you can do it using the following steps:

Open SQL Server Management Studio (SSMS). Under SQL Server agent, create a new job named test. Click on Schedules and button called New.

A new window will popup. Under frequency section, select weekly for "Occurs" option. Select 2 on "Recurs every" option. Under days option choose "Saturday". Click ok.

Now the job will run every alternate Saturday on the time specified.

SQL Server: Resolving DateTime Conversion Errors

noreply@blogger.com (Madhivanan) — Tue, 11 Oct 2011 07:15:00 +0000

Sometimes your queries may return errors when using DateTime values. Consider the following select statement

select cast('19/12/2000' as datetime)

This statement will fail with the following error message:

Msg 242, Level 16, State 3, Line 2
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

The reason is that SQL Server either does not recognize the input date format or the date is invalid. It is important to know the Server's date setting before using any date formats in your queries.

There is a DBCC command which can be used to determine the current date format

DBCC USEROPTIONS

When you run the code, the third row gives you the current dateformat. It is mdy on my machine, so you should input dates in mm-dd-yyyy format. If it is dmy, you should input it in dd-mm-yyyy format. The input format in most cases, should be based on the server's date format.

Now when you run the following code by formatting dates in mdy format (in sync with your machine format), it works fine:

select cast('12/19/2000' as datetime)

Note: It is also better to use an unambiguous date format YYYYMMDD HH:MM:SS which will work for all date settings.

SQL Server: Removing Deprecated Code and Future Proofing your Queries

noreply@blogger.com (Suprotim Agarwal) — Fri, 30 Sep 2011 06:52:00 +0000

New features are added with every release of SQL Server and as a result, some features get removed or deprecated. Deprecated features are features that are still operational (for backward compatibility) but will be removed in a future version. Deprecated features can be of two types: those that will be deprecated in a future version and those that will be deprecated in the next version.

In this article, we will explore how to track deprecated code and correct it. I will also share our observations when we performed the same operation on a live production database. Please feel free to retweet and share this link with fellow developers.

Tracking Deprecated Code in your Database

Tracking deprecated features can help you identify potential future bugs and upgrade and compatibility problems in your database. SQL Server 2008 provides the following methods to detect deprecated code in your database:

SQL Server: Deprecated Features performance counter
Deprecation category of trace events that includes the Deprecation Announcement and Deprecation Final Support event classes, which can be monitored using SQL Server Profiler or SQL Trace. You can use event notification and track these events via a stored procedure.
sqlserver.deprecation_announcement and the sqlserver.deprecation_final_support Extended events
Performance Monitor Tool (perfmon.exe) to display the value of counters for SQL Server: deprecated features object.
SQL Trace Collector to automate the collection of deprecation events

We will discuss the SQL Server: Deprecated Features performance counter and PerfMon tool in this article.

Observe the following code which uses the Deprecated Feature performance counter and gives a feel of how the deprecation feature works:

OUTPUT

The sys.dm_os_performance_counters dynamic management view (DMV) maintains a count every time a deprecated feature is used. The code shown above is for a named instance MSSQL$<instancename>. For a SQL Server default instance, just replace the above code with this one

SELECT
object_name, instance_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Deprecated Features'
AND cntr_value > 0;

Another way of using this performance counter is by using PerfMon tool (Start > Run > Perfmon) on your machine. For example lets add the sp_dboption counter from the SQLServer:Deprecated Features performance object as shown below.

To test the counter, fire the following statement in SSMS which uses the deprecated feature sp_dboption

USE master
EXEC sp_dboption 'Umbra', 'read only', 'FALSE'
GO

and note the deviation and counter value when the query is fired. Microsoft recommends to use ALTER DATABASE instead of sp_dboption.

ALTER DATABASE
SET READ_ONLY
GO

It’s always best to log and save your results and the SQL Profiler gives you options to do that.

Note: If you are upgrading your SQL Server 2000 installation to SQL Server 2008, then remember to specifically search for the features that were deprecated in SQL Server 2005, since those features may no more exist in SQL Server 2008. Google/Bing is your friend here and use them to search for removed features. Another way is to look at the Backwards Compatibility section in the SQL server 2005 Books Online Installation guide and remove the deprecated features listed in that section.

What to do once Deprecated Code is detected?

Microsoft has created a page that describes all the deprecated features and has listed alternative techniques to future-proof your code. Although in the beginning of this article, I have shown only a partial screenshot of the deprecated features used in a live production database, we actually encountered over 50 deprecated features that we were using. I have attempted to list some important ones randomly and hope that this list will be useful to you too and would save you time if you were to deal with deprecated code in your database.

Here’s a list of our observations:

The COMPUTE or COMPUTE BY syntax is deprecated since it created a new summarized result set which was difficult for apps to consume. Instead now use GROUP BY with ROLLUP which occurs once per compilation.
While using locks in SQL Server 2008, although sp_lock and sys.syslockinfo are still available, their use has been deprecated and instead SQL Server favors the Sys.dm_tran_locks.
While performing locks use the rowversion data type instead of the timestamp data type
The text, ntext, and image data types are being deprecated (since SQL 2005) in favor of varchar(max), nvarchar(max) and varbinary(max) data types.
Instead of sp_change_users_login, use ALTER USER WITH LOGIN
Compatibility levels 60, 65, and 70 are deprecated and support is provided only for compatibility levels 80 and higher
DBCC DBREINDEX has been deprecated and replaced with the ALTER INDEX REBUILD command and DBCC INDEXDEFRAG has been replaced with ALTER INDEX REORGANIZE
The sp_depends system stored procedure and the sys.sql_dependencies catalog view have been deprecated. Use sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities instead
Use of WRITETEXT or UPDATETEXT is deprecated when modifying text, ntext, or image data types.
In the CREATE FULLTEXT CATALOG arguments, rootpath has been deprecated.
Full-Text Search stored procedures are deprecated. Use CREATE FULLTEXT CATALOG, ALTER FULLTEXT CATALOG, and DROP FULLTEXT CATALOG instead
If you are using XML, XDR (XML Data Reduced) schemas is deprecated
SET SHOWPLAN_ALL and SET SHOWPLAN_TEXT options are deprecated and replaced with the SET SHOWPLAN_XML option
SET STATISTICS PROFILE option has been deprecated and replaced with the SET STATISTICS XML option
The old syntax of RAISERROR syntax specifying the error number and message number is deprecated (RAISEERROR 50005 ‘Exception Occurred’). Instead use the new syntax RAISEERROR(50005, 10, 1) which allows you to specify the messageid, severity and state)
In earlier editions owner.tablename was supported. In SQL 2008, this is deprecated. If an object belongs to a schema, then it must be referenced with its associated schema name so the syntax changes to schemaname.tablename.
The sp_attach_db stored procedure has been deprecated and you should use CREATE DATABASE with FOR ATTACH clause
sp_addlogin is replaced with CREATE LOGIN. sp_defaultdb, sp_defaultlanguage, sp_password are all replaced with ALTER LOGIN
sp_droplogin and sp_revokelogin is replaced with DROP LOGIN
sp_adduser, sp_dropuser, sp_grantdbaccess, and sp_revokedbaccess are replaced with CREATE USER, DROP USER and ALTER USER
sp_addrole and sp_droprole are replaced with CREATE ROLE and DROP ROLE
The ALL clause has been deprecated.
Log shipping is deprecated and replaced with Database Mirroring
The SQL Server Surface Area Configuration tool is deprecated and replaced with Policy-Based Management.
Remote servers will soon be deprecated and you should no longer use sp_addserver, sp_addremotelogin, sp_remoteoption and sp_helpremotelogin. Use Linked server instead.
DUMP and LOAD have been replaced with BACKUP and RESTORE
sp_helpdevice is replaced by sys.backupdevices catalog view
The FASTFIRSTROW table hint is deprecated
String literals as column aliases – Microsoft discourages the use of string as a column alias in a SELECT statement as it occurs per compilation. So instead of 'RetValue'= @ret_val, you should use @ret_val as ‘RetValue’

These were just some of the deprecated features we encountered and shared with you. For a detailed list, check this documentation.

Here’s a mantra - Rewrite any pre-written code that uses deprecated features, and avoid using any deprecated features in new development! It will make your life as a database guy, easier.

SQL Server: Handling Divide By Zero Error

noreply@blogger.com (Madhivanan) — Mon, 26 Sep 2011 11:18:00 +0000

Often SQL developers encounter "Divide by zero" error. This is because in an arithmetic
expression, when the divisor is zero (0), the expression throws an error. If you want to simply return NULL instead of an error, you can use one of the following methods

Method 1 : Use CASE expression

declare @var1 int ,@var2 int
select @var1=763, @var2=0
select @var1/case when @var2 =0 then null else @var2 end

OUTPUT

Method 2 : Use NULLIF function

declare @var1 int ,@var2 int
select @var1=763, @var2=0
select @var1/nullif(@var2,0)

The NULLIF function compares the first parameter (var2) with second parameter (0). If they are same, it returns a NULL

SQL Server: Find Database File Path

noreply@blogger.com (Madhivanan) — Thu, 22 Sep 2011 11:13:00 +0000

Suppose you want to find out the path where the SQL Server database files(mdf and ldf) are located, using a query. You can use system procedure sp_helpdb and system view sysfiles to obtain this information.

The following methods shows the path of the database files for master database

Method 1 : Use sp_helpdb

EXEC sp_helpdb master

Method 2 : Use sys.sysfiles view

select filename from master.sys.sysfiles

SQL Server: Get Object Last Modified Date

noreply@blogger.com (Madhivanan) — Fri, 16 Sep 2011 23:34:00 +0000

Suppose you want to track when was the last time a particular SQL Server object (table, view, procedure etc) was modified. To do so, you can make use of the system views - sys.objects and sys.all_objects.

Consider this example

create table test(id int, names varchar(100))
GO
select name, create_date, modify_date
from sys.objects
where name='test'

The above gives you the create_date and modify_date (in this case both will be the same).

Now alter the table to change the width of the column names

alter table test
alter column names varchar(150)
GO

Now execute the same select statement again:

Executing the above statement gives you the create_date and modify_date (in this case modify_date will be greater than the create_date). Whenever there is a column change in the table, the modify_date column gets updated.

Note: You can use sys.all_objects in place of sys.objects too.

Microsoft Sql Server Tutorials

Using sys.dm_os_performance_counters to measure Transactions Per Second

SQL Server 2012 Free Cook Book and Training

Declaring Length for VARCHAR and NVARCHAR datatypes IS Necessary

Restoring SQL Server Database: Points to Consider

Most Popular SQL Server Articles in 2011

SQL Server Administration Articles

T-SQL Articles

Assign Result of Dynamic SQL to a Variable in SQL Server

Why is RAID So Important for Databases?

Different RAID Levels (Advantages and Disadvantages)

Which RAID is Suitable for my Database?

Deleting Leading Zeros in a String (SQL)

Find Last Run Query in SQL Server

Activity Monitor in SQL Server

SQL Server Download Links All Editions

SQL Server 2012 Download Links

SQL Server 2008 R2 Download Links

SQL Server 2008 Download Links

SQL Server 2005 Download Links

Other SQL Server Download Links

SQL Server: Test Linked Server Connection

SQL Server: Create Objects Automatically in New Databases

Generate AlphaNumeric Identity Column in SQL Server

SQL Server: Find Common Rows in Tables using INTERSECT

SQL Server: Handling Multiple Result sets in a Procedure

Add Client Name in SQL Profiler Results

SQL Server File Location: Points to Consider

SQL Server: LPAD and RPAD functions equivalent

SQL Server: Schedule Jobs every Alternate Week

SQL Server: Resolving DateTime Conversion Errors

SQL Server: Removing Deprecated Code and Future Proofing your Queries

SQL Server: Handling Divide By Zero Error

SQL Server: Find Database File Path

SQL Server: Get Object Last Modified Date