Microsoft Sql Server Tutorials

SQL Server 2012 LEAD and LAG

noreply@blogger.com (Madhivanan) — Fri, 25 May 2012 07:08:00 +0000

LEAD and LAG are two analytical functions that have been introduced in SQL Server 2012. Let’s quickly see the usage of these functions with an example.

Sometimes you may need to display the previous or next row values for a column in the same row. In earlier versions, we had to use a Self join to do this. But from SQL Server 2012, we can make use of the analytical functions – LEAD and LAG

Consider the following set of data.

create table #sales (sales_id int identity(1,1), sales_date datetime, product_id int, qty int, sales_amount decimal(12))
insert into #sales(sales_date , product_id , qty,sales_amount)
select '20001111',1,12,48882 union all
select '20001112',1,33,65544 union all
select '20001113',1,9, 31289 union all
select '20001114',1,22,58860

Suppose you want to show the sales amount for a day as well as sales amount for the next day. Use the LEAD function as shown below

select product_id,sales_date,sales_amount, lead(sales_amount) over (order by sales_date) as next_sales_amount from #sales

Similarly, if you want to show the sales amount for a day as well as sales amount for the previous day, use the LAG function as shown below

select product_id,sales_date,sales_amount, lag(sales_amount) over (order by sales_date) as previous_sales_amount from #sales

Note that like other windows functions row_number(), rank(), etc, both LEAD and LAG functions need Order by clause. It determines the order of rows so that SQL Server picks previous and next rows easily.

The following queries show values based on qty column

select product_id,sales_date,sales_amount, lead(sales_amount) over (order by qty) as next_sales_amount from #sales

select product_id,sales_date,sales_amount, lag(sales_amount) over (order by qty) as next_sales_amount from #sales

Using Sequence in SQL Server 2012

noreply@blogger.com (Madhivanan) — Mon, 21 May 2012 15:25:00 +0000

Continuing my series on SQL Server 2012, today we will explore Sequence. Sequence is an object in SQL Server 2012 which can be used to generate customized sequence numbers. Sequence is an independent object and is not associated with any table by default. When you are inserting data into the table, we can make use of sequence values from the sequence object.

Consider the following Sequence and data

create sequence my_seq
    as int
    start with 1
    increment by 1

The above creates the sequence named my_seq which has initial value of 1 and will increment by 1

create table sales(sales_id int, product_id int,qty int,sales_amount decimal(12,2))

Let us add some data to this table generating sales_id value using sequence

insert into sales(sales_id,product_id,qty,sales_amount)
select next value for my_seq,10001,3,1200 union all
select next value for my_seq,10002,2,600 union all
select next value for my_seq,10003,10,200 union all
select next value for my_seq,10001,200,1200 union all
select next value for my_seq,10001,10,1200

In the above insert statement, values are taken from sequence for sales_id column and every time a value is selected from the sequence, its value gets incremented by 1

Now run this select statement and see the result

select * from sales

OUTPUT

If you want to restart the sequence values, you can use ALTER sequence statement

alter sequence my_seq restart with 1

Adventure Works Database for SQL Server 2012

noreply@blogger.com (Suprotim Agarwal) — Thu, 17 May 2012 07:20:00 +0000

SQL Server 2012 introduces many new technologies and features. One of the best ways to learn about these features is to try them out on a database. Luckily for us, we have the AdventureWorks database available since the year 2000. The best part is that the SQL Team has been regularly maintaining and updating the AdventureWorks sample database for all new versions of SQL Server, as they are released.

To download versions of Adventure Works databases for SQL Server 2012, go to AdventureWorks for SQL Server 2012 on CodePlex.

AdventureWorks Community Samples Databases for SQL Azure

SQL Azure is the relational data store for the Azure platform. The SQL team has also released a AdventureWorks2012 Full database on CodePlex containing the full version of the AdventureWorks2012 database along with an AdventureWorks samples database that is scaled-out using SQL Azure Federations.

AdventureWorks2012Full_SQLAzure

There are some other downloads available for OLTP, Multidimensional models, Datawarehouse etc. Do check them out!

SQL Server 2012 Format Function

noreply@blogger.com (Madhivanan) — Fri, 11 May 2012 02:51:00 +0000

SQL Server 2012 has introduced many new functions. We will see some of them in this series.

One of the most common challenge for a SQL Developer is to format a date. In earlier versions, we had to use the CONVERT function with format style number. In SQL Server 2012, we have a new function named FORMAT which can format dates in various formats.

The following codes are self explanatory

declare @d datetime
set @d='20110119 12:33:22'

--Format date in dd-mm-yyyy format
select format(@d,'dd-MM-yyyy') as [dd_mm_yyyy]

--Format date in mm/dd/yyyy format
select format(@d,'MM/dd/yyyy') as [mm/dd/yyyy]

--Format date in mmm-yyyy format
select format(@d,'MMM-yyyy') as [MMM-yyyy]

--Format date in MMM dd,yyyy format
select format(@d,'MMM dd,yyyy') as [MMM dd,yyyy]

--Format date in HH:MM:SS format
select format(@d,'HH:mm:ss') as [HH:mm:ss]

--Format date in long format
select format(@d,'dddd, dd MMMM yyyy') as [long format]

All you have to do is to give the format in a specific way you want. Here’s the output

Retrieve Column Names From Temporary Table in SQL Server

noreply@blogger.com (Suprotim Agarwal) — Tue, 08 May 2012 05:58:00 +0000

When a temporary table is created, a record gets created in tempdb’s sys.tables and tempdb.sys.columns system table. You can query these tables to get meta data information from a temporary table.

Here’s how to retrieve a Temporary Table’s Column Names. Let’s first create a temporary table with some columns

create table #SomeTmpTbl
(
col1 int,
col2 varchar(20),
col3 datetime
)

Now query the tempdb.sys.columns in the following manner

select * from tempdb.sys.columns where object_id =
object_id('tempdb..#SomeTmpTbl');

and there you go!

Information Schema - SQL Server vs MySQL

noreply@blogger.com (Madhivanan) — Thu, 26 Apr 2012 08:36:00 +0000

Continuing my series on how same things can be done differently in SQL Server and MySQL, in this post, we will see how Information Schema is used in SQL Server vs MySQL.

To view information about a column for a table or list of tables available in the database or primary and foreign key information etc. etc. we can use information_schema views like - information_schema.tables, information_schema.columns and so on. This option is available in both SQL Server and MySQL. However there are some differences in their usage.

In SQL Server

information_schema is a schema and available in all databases by default.
In views like information_schema.columns, the column table_catalog will show database name and column table_schema will show the schema name.
By default, it shows the result for the current database. If you want to show results for a different database, you need to qualify a database name like db_name.information_schema.columns

In MySQL

information_schema is a database that has information for all databases
In views like information_schema.columns, the column table_catalog will be NULL and column table_schema will show the database_name.
By default it shows the result for all the databases. If you want to show results for a specific database, you need to filter on
the column table_catalog ex WHERE table_catalog='db_name'

Bulk Insert - SQL Server vs MySQL

noreply@blogger.com (Madhivanan) — Thu, 19 Apr 2012 03:39:00 +0000

Continuing my series on how same things can be done differently in SQL Server and MySQL, in this post, we will see how bulk insert can be done in SQL Server vs MySQL.

We often need to import data from a text file to the server. Consider that the file D:\test.txt has data for three columns and you want to import this data into a table

In SQL Server we can use the BULK INSERT command

In MySQL we can user Load Data Command

load data local infile 'D:/test.txt' into table test
fields     escaped by '\\'
    terminated by '\,'
    lines terminated by '\n'

Here both of them consider comma as a field separator and new line as the line separator

Note : By Default SQL Server looks for the existence of the file in Server's directory. In MySQL, we need to specify keyword local to instruct that file is available in the local system

SET and SELECT - SQL Server Vs MySQL‏

noreply@blogger.com (Madhivanan) — Thu, 12 Apr 2012 14:32:00 +0000

Continuing my series on how same things can be done differently in SQL Server and MySQL, in this post, we will see the usage of SET and SELECT commands in SQL Server vs MySQL.

SET and SELECT commands can be used to assign values to the variables. But the usage is different in SQL Server and MySQL.

In SQL Server, SET can be used to assign a value to single variable only. SELECT command can be used to assign values to multiple variables.

Consider the following examples

Declare @a int, @b int
set @a=1
set @b=2
select @a,@b

The following will also work

Declare @a int, @b int
select @a=1,@b=2
select @a,@b

OUTPUT

In MySQL, declaration is not needed. Any number of variables can be assigned using a single SET command

set @a:=1, @b:=2;
select @a,@b

The SELECT command can be used to assign values and select the values too. The following is equal to the previous code

select @a:=1, @b:=2;

The above command assigns values to the variables and also returns values assigned

OUTPUT

Temporary Tables - SQL Server vs MySQL

noreply@blogger.com (Madhivanan) — Mon, 09 Apr 2012 04:02:00 +0000

Continuing my series on how same things can be done differently in SQL Server and MySQL, in this post, we will see temporary table support in SQL Server vs MySQL.

We may often need to create a temporary table while processing data to provide a workspace for storing intermediate results. Both SQL Server and MySQL support temporary tables.

In SQL Server, all temporary tables should be prefixed by the # sign

Consider this table

create table #test
(
id int,
names varchar(100)
)

insert into #test(id, names)
select 1,'test'
select * from #test

We can drop this table by using a DROP command

DROP table #test

In MySQL, we have to use the keyword 'temporary' when creating a temporary table

Consider the following code

create temporary table if not exists test
(
id int,
names varchar(100)
)

insert into test(id, names)
select 1,'test'
select * from test

The above creates a temporary table called test in the current session if it is not already available. To drop a temporary table in MySQL, we can use the following code

drop temporary table test

Dense Rank - MySQL vs SQL Server

noreply@blogger.com (Madhivanan) — Thu, 05 Apr 2012 11:53:00 +0000

Continuing my series on how same things can be done differently in SQL Server and MySQL, in this post, we will see how to implement Dense Rank in SQL Server vs MySQL.

Generating a dense_rank is a common requirement when showing resultsets. In SQL Server, starting from version 2005, we can make use of the dense_rank() function. Dense_rank() will generate the serial number for each set of values and keep the same number if the value is duplicated

Consider the following set of data

create table test(names varchar(100))
insert into test
select 'Suresh' union all
select 'Ramesh' union all
select 'Kant' union all
select 'Jerald' union all
select 'Clara' union all
select 'Ramesh' union all
select 'Kant' union all
select 'Jerald' union all
select 'John'

SQL Server

Using the dense_rank() function, we can generate a serial number and reset for each name

select dense_rank() over (order by names) as sno,names from test

MySQL

Using a variable, we can generate the serial number, and use another variable that keeps same value for duplicates

set @sno:=0;
set @names:='';
select @sno:=case when @names=names then @sno else @sno+1 end as sno,@names:=names as names from test
order by names;

In the above example, variable @sno gets incremented by 1 for each set of values thus keeping the same value for duplicates.

Reset Row Number For Each Group - SQL Server Vs MySQL

noreply@blogger.com (Madhivanan) — Mon, 26 Mar 2012 06:51:00 +0000

Continuing on my SQL Server vs MySQL series, we will see how same things can be done differently in SQL Server and MySQL

Generating a row number or a serial number and resetting it on each group is a common requirement when showing result sets. In SQL Server, starting from version 2005, we can make use of the row_number() function with the partition clause

Consider the following set of data

SQL Server

Using the row_number() function, we can generate the serial number and reset for each names

select row_number() over (partition by names order by names) as sno,names from test

MySQL

Using a variable, we can generate the serial number, and use another variable that resets first variable for each group

set @sno:=0;
set @names:='';

OUTPUT

Generate Row Number – SQL Server vs MySQL

noreply@blogger.com (Madhivanan) — Mon, 12 Mar 2012 10:10:00 +0000

In this series, we will see how same SQL tasks can be achieved differently in SQL Server and MySQL. Generating a row number or a serial number is a common requirement when showing the resultsets.

In SQL Server, starting from version 2005, we can make use of the row_number() function

Consider the following set of data

SQL Server

Using the row_number() function we can generate a serial number as follows:

select row_number() over (order by names) as sno,names from test

MySQL

Using a variable in MySQL, we can generate a serial number as follows:

set @sno:=0;
select @sno:=@sno+1 as sno,names from test
order by names;

In the above example, variable @sno gets incremented by 1 for each row.

Stay tuned for more on MySQL vs SQL Server posts.

Common Myths Around SQL Server Best Practices

noreply@blogger.com (Suprotim Agarwal) — Wed, 07 Mar 2012 10:32:00 +0000

Idera has a bunch of Free SQL Server Webcasts that you can watch and learn from. One that particularly caught my attention was the webcast about SQL Server Myths Debunked…Or Are They?

Numerous SQL Server experts have done a great job of debunking many of the common myths that crop up around SQL Server best practices and usage. However in some cases, the debunking of those myths creates unintended consequences – we ignore the underlying truth which started the myth in the first place.

SQL Server expert Michael K. Campbell does a survey of misperceptions that have arisen from commonly debunked SQL Server myths. This free webcast shows you how shrinking and using multiple files can be a best practice, what AWE and locking files in memory really means, along with other best practices that you may be missing out on because of misconceptions.

In addition to this webcast, I recommend the following webcasts:

What Are You Waiting For?
Putting a Better SQL Server in Production
Under The Hood with SQL Server Fundamentals
Learning to Hate the SMO: A PowerShell Love Story
TempDB: Performance and Manageability
5 Common High-Availability Mistakes
Common SQL Server Security Mistakes
Top 10 SQL Server Backup Mistakes and How to Avoid Them

Check out all these Free SQL Server Webcasts Over Here

Find out Long Running query using sp_who2 with Dynamic Management Views‏ (DMV)

noreply@blogger.com (Madhivanan) — Thu, 01 Mar 2012 01:48:00 +0000

There are many ways to find out why your SQL server is performing slow. Suppose you know that your server is responding slowly while executing queries and you want to find out the query that takes a lot of time to execute. If you want to find out slow queries, you can use dynamic management views from version 2005 onwards. However you can still use the old system stored procedure sp_who2 along with management views, to find this info.

Run the following code

EXEC sp_who2

and see the result shown below. Find out rows with runnable status with highest CPUTime.

Sp_who2 will not display the text of the query, so we need to find out the text using dm_exec_requests and dm_exec_sql_text management views. The view dm_exec_requests will show the text of the query. All we need to do is to find out spid from the result of sp_who2 and filter it in dm_exec_requests using session_id column. The following code displays the text of the query.

SELECT
        sql.text AS statement_text
FROM
        sys.dm_exec_requests AS req
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) as sql
WHERE
    req.session_id=52

Similarly you can also use dynamic management views to find out various root causes for performance issues.

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.