Microsoft Sql Server Tutorials

SQL Server 2012 Query Pagination

noreply@blogger.com (Pravinkumar Dabade) — Wed, 12 Jun 2013 18:05:00 +0000

In SQL Server 2012, Microsoft introduced a couple of T-SQL Enhancements. One of them is Query Pagination which we will explore today.

For this demonstration, I am using the Northwind database to demonstrate this new feature. Now in most of the applications, a common requirement is how to fetch the data from the database servers, page wise.
In earlier versions of SQL Server like SQL Server 2005/2008/R2, we can implement Pagination by using different techniques. For example, we implement pagination using ROW_NUMBER() function or CTE - Common Table Expression.

In SQL Server 2012, Microsoft has introduced Pagination as a part of Select query in a Order By clause. Now you will have to use OFFSET and FETCH NEXT with the order by clause.

Let's take a look at a few examples. I am using Northwind database for this demonstration. I have created a Stored Procedure which takes two parameters. First parameter takes the page number and the second parameter ask you to fetch the no. of records for that page. The stored procedure code is as below -

If you execute the above stored procedure

EXEC FetchPagedRecords 2,10

you will get the following results -

The OFFSET specifies the number of rows to skip before it starts returning the rows and FETCH NEXT specifies the number of rows to be returned.

Microsoft has introduced an easy way of implementing Data Paging in SQL Server 2012 by adding OFFSET and FETCH NEXT in an Order By clause. I hope you will use it in your applications.

Get Temporary Table Column Names in SQL Server

noreply@blogger.com (Suprotim Agarwal) — Sat, 01 Jun 2013 05:10: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!

Finding Last Backup Date in SQL Server 2005

noreply@blogger.com (Suprotim Agarwal) — Tue, 14 May 2013 04:37:00 +0000

I saw a question on the SO forums asking for a simple way to find the last backup date of a SQL Server 2005 database. Here's one approach.

During a backup operation, the following tables are updated in SQL Server 2005:

msdb.dbo.backupfile,
msdb.dbo.backupmediaset ,
msdb.dbo.backupmediafamily and
msdb.dbo.backupset.

Here we are doing a join between sys.sysdatabase and msdb.dbo.backupset which gets us the database name, the last backup date and who took the backup. sys.sysdatabase is queried as it contains one row for each database in an instance of Microsoft SQL Server.

OUTPUT

SQL Server NTILE Function

noreply@blogger.com (Suprotim Agarwal) — Wed, 01 May 2013 09:25:00 +0000

The SQL Server NTILE() function divides the result set into a specified number of even sized group (approximate division) and assigns a ranking value to these groups.

The NTILE() function is similar to other ranking functions like the RANK() and DENSE_RANK(), except that NTILE takes one parameter of type int/bigint that specifies the number of groups into which each partition must be divided.

Let us see an example. We will query the Products table of the Northwind database and divide it into 5 groups ordered by the UnitsInStock:

OUTPUT

On running the query, the results are divided into 5 groups, but because the total number of rows (48) is not divisible by the number of groups (5), NTILE puts 10 rows in the first three group and the remaining two groups have 9 rows each.

Activity Monitor in SQL Server

noreply@blogger.com (Suprotim Agarwal) — Thu, 25 Apr 2013 11:46: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 Management Objects 2008 (SMO) New Features

noreply@blogger.com (Pravinkumar Dabade) — Mon, 15 Apr 2013 09:55:00 +0000

MSDN defines SMO as - SQL Server Management Objects (SMO) is a collection of objects that are designed for programming all aspects of managing Microsoft SQL Server. In this article we will practically explore some features of SQL Server Management Objects.

To start with, let’s create a ‘Windows Application’ using Visual Studio and name the application as ‘SMOExamples’ as shown below –

Now let’s add the following references to our project.

Microsoft.SqlServer.ConnectionInfo.dll
Microsoft.SqlServer.Smo.dll
Microsoft.SqlServer.SmoExtended.dll
Microsoft.SqlServer.SqlEnum.dll
Microsoft.SqlServer.Management.Sdk.Sfc.dll

All these references can be found in the path – ‘C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies’.

Now let’s design our windows form as shown below –

Declare an object which will make a connection to our SQL Server instance. For this, we will use a class called ‘Server’ as shown below –

Server srv = new Server(Environment.MachineName);

Now to test the connection to the SQL Server, let’s write the following code on the Form Load event as shown below –

Now let’s create a Database and Table with the name ‘SMOExample’ and ‘SMOTable’ respectively by writing the following code on the click event of ‘Create Database And Table’ button –

For creating a database, we have used a class called ‘Database’ which takes two parameters in the constructor. First one is the SQL Server instance in which we have to create a database. Second is the database name.

Now let’s create a script for all the tables available in our database. Write bee following code on the click event of ‘Generate Script’ button –

For generating the script files we are using a ‘Scripter’ class.

Now let’s write the code to take a backup of the complete database. Write the following code on the click event of ‘Backup Database’ button –

To take a backup of the database, we are using a ‘Backup’ class and to decide what will be the device of backup, we are using ‘BackupDeviceItem’ class.

Now the last thing we will explore is how to verify the backup, which is taken before we restore it to another server/same server. Write the following code on the ‘Verify Backup’ button –

Now run your project. This will show you the date and time created for ‘AdventureWorks’ database as shown below –

Now let’s click the button ‘Create Database and Table’ and observe the results in SQL Server Management
Studio Object Explorer –

Now click on the button ‘Generate Script’. Go to ‘C:\’ and verify the script file –

Finally click the button ‘Backup Database’ and check your ‘C:\’ drive. Your backup file should have been created. Similarly click on ‘Verify Backup’ button and it will show you a success message.

Summary – In this article we saw few capabilities of SQL Server Management Objects (SMO).

Download the source code

List Empty Tables in SQL Server

noreply@blogger.com (Suprotim Agarwal) — Tue, 09 Apr 2013 05:52:00 +0000

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 a Dynamic 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.

Does my SQL Server Database Support Compression?

noreply@blogger.com (Suprotim Agarwal) — Thu, 28 Mar 2013 05:45:00 +0000

Some time back, Praveen Dabade had written a nice article on SQL Server Compressed Tables and Indexes in SQL Server where he explained how compression is now supported on ‘ROW and PAGE’ for tables and indexes. However did you know that compression is an enterprise-level feature?

How do determine what Enterprise Edition features are enabled on your database? Well you can use the sys.dm_persisted_sku_features DMV to find what Enterprise Edition features are
enabled on your database.

Learn more about Dynamic Management Views (DMV’s) here

Here’s the query for the same

SELECT feature_name,feature_id
FROM
sys.dm_db_persisted_sku_features;

Running this query will list all edition-specific features that are enabled in the current database. Some of the database changing features restricted to the SQL Server Enterprise or Developer editions are Compression, Partitioning, ChangeCapture etc.

This DMV is also useful in situations where you are planning to move a database from a higher to a lower edition. Eg: From Enterprise to Standard edition. Running the query will tell you if there are any Enterprise Edition features enabled that may not work when you move to a lower edition.

The DMV will return no rows if no features restricted to a particular edition are used by the database.

Order By Clause and Literal Values in SQL Server

noreply@blogger.com (Madhivanan) — Tue, 12 Mar 2013 05:40:00 +0000

The literal value in an ORDER BY Clause can be an ordinal position of the column or just literal values depending on the usage

Ordinal position in a table refers to the position of the column in a table. It can also point to the column number in a SELECT statement

Consider the following set of data

create table testing(id int, names varchar(100))
insert into testing(id,names)
select 18,'Charles' union all
select 20,'Ashok' union all
select 19,'Birla' ;

The select statement

select * from testing order by 1

returns the following result

The number 1 in Order by clause refers to the first column listed in the SELECT statement so the result is ordered by column ID

The select statement

select * from testing order by 2

returns the following result

As you see the resultset is ordered by second column NAMES

But if you use a CASE expression and a number, it will behave differently. Suppose you want to keep the name Birla in first row and sort other rows by alphabetical order of remaining names, you can use the following statement

select * from testing order by case when names='Birla' then 1 else 2 end,names

The result is

In the above statement, number 1 and 2 are literal values and do not refer any columns. If the name is Birla, a value 1 is assigned otherwise 2 is assigned and ordered by these values first, then ordered by names.

Computed columns - Persisted Vs Non-persisted‏

noreply@blogger.com (Madhivanan) — Wed, 27 Feb 2013 06:26:00 +0000

Computed columns are derived columns based on other existing columns in the same table. Refer to this post http://www.sqlservercurry.com/2012/11/computed-columns-in-sql-server.html for more information:

There are two types of computed columns namely persisted and non-persisted.

There are some major differences between these two

1. Non-persisted columns are calculated on the fly (ie when the SELECT query is executed) whereas persisted columns are calculated as soon as data is stored in the table.

2. Non-persisted columns do not consume any space as they are calculated only when you SELECT the column. Persisted columns consume space for the data

3. When you SELECT data from these columns Non-persisted columns are slower than Persisted columns

Consider the following set of code

create table #t1(col1 int, col2 as col1*0.20)
insert into #t1 (col1)
select top 5000 row_number() over (order by s1.name) from sys.objects as s1 cross join sys.objects as s2

create table #t2(col1 int, col2 as col1*0.20 persisted)
insert into #t2 (col1)
select top 5000 row_number() over (order by s1.name) from sys.objects as s1 cross join sys.objects as s2

Run the following code to understand that table with persisted computed columns consumes more space when compared to a table with non-persisted computed columns. Refer the column named data

exec tempdb..spaceused #t1
exec tempdb..spaceused #t2

Parsename to Extract Precision and Scale values‏ From Decimal Numbers

noreply@blogger.com (Madhivanan) — Thu, 14 Feb 2013 08:30:00 +0000

The numeric datatype stores numbers with precision and scale. Suppose you want to extract only a precision or a scale, you can do it via many ways. One of the ways is to make use of the PARSENAME function.

Consider the following example

declare @amount decimal(12,2)
set @amount=87234.50
select parsename(@amount,2) as precision, parsename(@amount,1) as scale

The result is

precision            scale
---------            --------
87234                  50

Parsename is used to extract specified part of a name. In general, it is used to extract names from four part object names separated by a dot. Argument number 1 extracts the last part of a string, and the 2nd argument extracts the next last part.

This way we can effectively make use of parsename function to extract precision and scale values from the decimal numbers.

Error Handling in SQL Server with THROW

noreply@blogger.com (Suprotim Agarwal) — Sat, 09 Feb 2013 08:15:00 +0000

Continuing our series on SQL Server 2012, today we will talk about THROW. In versions prior to SQL Sever 2012, we used @@RAISE_ERROR to generate error messages dynamically or using the sys.messages catalog.

Consider the following example

SELECT ROUND(800.0, -3)

On executing this statement, you get the following error:

because the value does not fit into the decimal data type.

You can use @@RAISERROR to raise a message

BEGIN TRY
SELECT ROUND(800.0, -3)
END TRY
BEGIN CATCH
DECLARE @ErrorMsg nvarchar(1000), @Severity int
SELECT @ErrorMsg = ERROR_MESSAGE(),
@Severity = ERROR_SEVERITY()
RAISERROR (@ErrorMsg, @Severity, 1)
END CATCH

Note: The old syntax of RAISERROR syntax specifying the error number and message number got deprecated (RAISEERROR 50005 ‘Exception Occurred’). Instead the new syntax RAISEERROR(50005, 10, 1) allowed you to specify the messageid, severity and state). For new applications use THROW.

However in SQL Server 2012, there’s a better way to this without much efforts – THROW. Consider the following code

BEGIN TRY
SELECT ROUND(800.0, -3)
END TRY
BEGIN CATCH
THROW
END CATCH

As you can see, with just one word THROW, we were able to handle the error with grace and get a result too.

A Smart Way of Using SQL Server Books On Line (BOL) without the Index Option

noreply@blogger.com (Madhivanan) — Thu, 24 Jan 2013 10:04:00 +0000

SQL Server's help file also known as Books On Line or BOL is a great resource to learn about all the features of SQL Server. You can make use of index option for detailed search. But there is also a simple yet smart way.

Suppose you are working in SSMS (SQL Server Management Studio) and while working on it, if you want to get more information on a function, you can invoke BOL by pressing F1. If the cursor is inside a function/system procedure/system variables, the BOL opens with the topic specific from where it is invoked

For example, you have typed system procedure sp_monitor in your query window and you want to learn more about it, make sure the cursor is placed anywhere within the word and press F1. BOL opens with the topic sp_monitor.

You can also highlight any function and press F1, BOL opens with that topic

Consider the following piece of code

EXEC sp_monitor
GO
SELECT GETDATE()
GO

select * from sys.objects where create_date>'20000101'

Now highlight sp_monitor and press F1 as shown here

BOL opens with the topic sp_monitor

No highlight GETDATE() and press F1.

BOL opens displaying information about GETDATE().

Similarly highlight sys.objects and press F1. BOL opens with the topic sys.objects.

A simple yet smart way to make use of BOL without using the index option!

SQL Server SESSIONPROPERTY

noreply@blogger.com (Madhivanan) — Fri, 18 Jan 2013 09:24:00 +0000

There are various settings that can be done at the session level. For example, you can set ANSI_NULLS OFF for a session and ON for another session. If you want to know if a setting is ON or OFF before using the setting, you can make use of system function SESSIONPROPERTY.

SESSIONPROPERTY will return 1 or 0 depending on whether the SET OPTION is ON or OFF

Consider the following example

SELECT SESSIONPROPERTY('ANSI_NULLS')

If the above statement returns 1, then ANSI_NULLS is ON in the session, otherwise it is OFF
Similarly the same function can be used to know the SET OPTION for various other options specified below

QUOTED_IDENTIFIER
ARITHABORT
ANSI_NULL_DFLT_ON
ANSI_WARNINGS
ANSI_PADDING
CONCAT_NULL_YIELDS_NULL

Eg:

SELECT SESSIONPROPERTY('ANSI_WARNINGS')

If the above returns 1, then ANSI_WARNINGS is ON in the session, otherwise it is OFF.

So when you are working in SQL Server, you can check the status of these settings using the SESSIONPROPERTY function

Different Methods To Simulate Cross Join

noreply@blogger.com (Madhivanan) — Tue, 15 Jan 2013 11:14:00 +0000

A Cross join can be effectively used to produce larger dataset that can be used for various purposes. One usage is to generate number table using a CROSS JOIN.

The following code generate series of numbers starting from 1

select
    row_number() over (order by s1.name) as number
from
    sys.objects as s1 cross join sys.objects as s2

The resultset will have N*N rows where N is number of rows of catalog view sys.objects. If the rows of this view is 120, the resultset will have 120*120=14400 rows. So we will have a number table with values ranging from 1 to 14400.

The same functionality can be simulated without using CROSS JOIN

Method 1. Using Old Style Non-Ansi JOIN without WHERE clause

select
    row_number() over (order by s1.name) as number
from
    sys.objects as s1 , sys.objects as s2

Because there is no WHERE clause, it becomes CROSS JOIN producing N*N rows

Method 2. Use INNER JOIN with no column matching

select
    row_number() over (order by s1.name) as number
from
    sys.objects as s1 inner join sys.objects as s2
    on 1=1

Because no columns are JOINed and 1=1 is always true, the above INNER JOIN will become a CROSS JOIN and produce N*N rows

Similarly LEFT and RIGHT JOINs with no column matching will behave the same like method 2

SQL Server - Time difference in HH hours, MM minutes and SS seconds format‏

noreply@blogger.com (Madhivanan) — Fri, 11 Jan 2013 09:31:00 +0000

Suppose you have two datetime values and want to express the difference in the format HH Hours, MM minutes and SS seconds. You can use CONVERT function with style 108 as shown below:

declare @start_date datetime, @end_date datetime
select @start_date ='20121210 11:19:33', @end_date='20121210 19:28:12'

select stuff(stuff(convert(varchar(10),@end_date-@start_date,108),6,1,' Minutes and '),3,1,' Hours, ')+' Seconds'

The variables @start_date and @end_date have values for start and end times. We can directly substract @start_date from @end_date and the result is still in datetime. CONVERT with style 108 will only extract time part in the format HH:MM:SS.

Now replace : after HH with Hours, replace : after MM with minutes and , append ' Seconds' at the end of time and the result is at the format HH Hours, MM minutes and SS seconds.

The result of the above code is

08 Hours, 08 Minutes and 39 Seconds

SQL Server - Which is faster INNER JOIN or LEFT JOIN?

noreply@blogger.com (Madhivanan) — Mon, 07 Jan 2013 17:38:00 +0000

You may be interested to know which is faster – the LEFT JOIN or INNER JOIN. Well, in general INNER JOIN will be faster because it only returns the rows matched in all joined tables based on the joined column. But LEFT JOIN will return all rows from a table specified LEFT and all matching rows from a table specified RIGHT.

We can analyze this using the STATISTICS TIME ON option. Consider the sys.objects and sys.columns catalog views and join them using INNER and LEFT joins and see the result

set statistics time on
select
    t1.object_id, t2.object_id
from
    sys.objects as t1 inner join sys.columns as t2
on t1.object_id=t2.object_id
select
    t1.object_id, t2.object_id
from
    sys.objects as t1 left join sys.columns as t2
on t1.object_id=t2.object_id
set statistics time off

The result is of SET STATISTICS TIME ON is

Note: You may think here that since the LEFT join returns more rows, so it is taking more time. Now eliminate the NULL object_ids from sys.columns and observe the result

set statistics time on
select
    t1.object_id, t2.object_id
from
    sys.objects as t1 inner join sys.columns as t2
on t1.object_id=t2.object_id
select
    t1.object_id, t2.object_id
from
    sys.objects as t1 left join sys.columns as t2
on t1.object_id=t2.object_id
where
    t2.object_id is not null
set statistics time off

The result is

So even though they both return the same number of rows, INNER JOIN is still faster.

Remove Non-Alphabet Characters from a String–SQL Server

noreply@blogger.com (Madhivanan) — Thu, 27 Dec 2012 04:14:00 +0000

Suppose you have a string that contains numbers and other special characters and you want to keep only alphabets and remove all the other characters. One way to do this is to use a while loop that parses each character

Here is the code for the same

declare @str varchar(20)
set @str='ab12#89L(h12k'
Select @str as 'Original String'
declare @temp_str varchar(20), @i int
select @temp_str ='',@i=1
while @i<=len(@str)
begin
set @temp_str=@temp_str+case when substring(@str,@i,1) like '[a-zA-Z]' then substring(@str,@i,1) else '' end
set @i=@i+1
end
select @temp_str as 'String with Alphabets'

The code inside the While loop takes each character and check if it is in the range a-z or A-Z and appends that character with another variable. Other characters will not get appended. So at the end of while loop, the second variable will have only alphabets.

Drop All Tables in Database whose name begins with tmp

noreply@blogger.com (Madhivanan) — Mon, 17 Dec 2012 12:22:00 +0000

Sometimes in your projects, you may be using staging tables for purposes like loading ,parsing and cleaning data. Suppose you name all of these table with the prefix tmp_ and after these tables have served their purpose, you now want to drop all these tables.

Here are two ways to do this:

1. Execute Drop table statements - Copy the result of this query which generates DROP TABLE statements and execute the result again. Eg:

select 'drop table '+table_name from INFORMATION_SCHEMA.TABLES where table_name like 'tmp_%'

The above query generates drop table statements like

drop table tmp_test
drop table tmp_emp
drop table tmp_data

provided there are tables named tmp_test, tmp_test and tmp_data in the database.

2. Concatenate drop table statements and execute it as a whole

declare @sql varchar(8000)
set @sql=''
select @sql=@sql+' drop table '+table_name from INFORMATION_SCEHMA.TABLES where table_name like 'tmp_%'
exec(@sql)

The variable @sql will have drop table statements concatenated for each table. It will have the string drop table tmp_test drop table tmp_emp drop table tmp_data

When you execute the string generated from that variable, all those tables get dropped

SQL Server - Convert Month Number to Month Name

noreply@blogger.com (Suprotim Agarwal) — Fri, 14 Dec 2012 04:21:00 +0000

Here’s a simple way to convert a month number to month name in SQL Server

DECLARE @monthNum int;
SET     @monthNum = 7;
SELECT DateName( month , DateAdd( month , @MonthNum - 1 , '1900-01-01' ))
        as 'MonthName'
        WHERE @monthNum between 1 and 12

OUTPUT

In order to get the MonthName of the current date, just use

SELECT DATENAME(month, GETDATE()) AS 'MonthName'

OUTPUT

Aggregates without GroupBy SQL Server

noreply@blogger.com (Madhivanan) — Fri, 30 Nov 2012 04:31:00 +0000

When you use aggregate functions along with non-aggregate columns, all non-aggregate columns should be part of GROUP BY Clause. However with the new version of aggregate functions with OVER(), it can be used without the usage of GROUP BY clause.

Suppose you want to return the total number of rows along with a column, you can write count(*) as shown below

select name,count(*) over () as total_rows from sysobjects

The result shows the value for name column along with total number of rows in total_rows column. Suppose you want to return total number of object types along with a column, you can write count(*) as shown below

select name,xtype,count(*) over () as total_rows,count(*) over (partition by xtype order by xtype) as total_types from sysobjects

The results shows values for column name , xtype and total number of xtypes for each xtype value. This count differs for each xtype.

This way you can make use of COUNT function without using GROUP BY Clause. From version 2012, you can also make use of SUM function for calculating running total using this technique which you can find at http://www.sqlservercurry.com/2012/08/sql-server-2012-running-total-with-sum.html

SQL Server: Datetime vs Datetime2 Datatype

noreply@blogger.com (Madhivanan) — Wed, 21 Nov 2012 05:14:00 +0000

The datetime2 datatype was introduced in SQL Server 2008 along with the date and time datatypes.In this post, we will see some differences between the datetime and datetime2 datatypes.

SNo	DATETIME	DATETIME2
1	Minimum and maximum date ranges are 1900-01-01 and 9999-12-31 23:59:59.997	Minimum and maximum date ranges are 0001-01-01 and 9999-12-31 23:59:59.9999999
2	Returns Current date with time with 3 fractional seconds precision	Return Current date with time with 7 fractional seconds precision
3	Addition or subtraction to numbers is directly allowed. Ex SELECT GETDATE()+1, GETDATE()-1	Addition or subtraction to numbers is not directly allowed. The function DATEADD should be used Ex SELECT DATEADD(DAY,1,SYSDATETIME()),DATEADD(DAY,-1,SYSDATETIME())
4	Milliseconds are rounded to increments of .000, .003, or .007 seconds Ex declare @datetime datetime select @datetime ='2012-10-19 12:23:45.345' select @datetime The result is 2012-10-19 12:23:45.347	Not rounded until the millisecond precision exceeds 7 Ex declare @sysdatetime datetime2(7) select @sysdatetime ='2012-10-19 12:23:45.99999945' select @sysdatetime The result is 2012-10-19 12:23:45.9999995
5	Datatype cannot have variable size	Datatype can have variable size DECLARE @sysdatetime1 datetime2(3),@sysdatetime1 datetime2(5)
6	TIME values can be added directly Ex declare @datetime datetime select @datetime ='2012-12-31 12:59:59.99' select @datetime+'12:00:00'	TIME values cannot be added directly
7	8 bytes is required to store the value	Depends on the millisecond precision; 6 to 8 bytes are required to store the value

Computed Columns in SQL Server

noreply@blogger.com (Madhivanan) — Fri, 16 Nov 2012 04:33:00 +0000

Computed columns are derived columns that are bound to values of other columns. However the datatype of these computed columns depends on the nature of the end result. They may be bound to columns of either the same datatype or they could be bound to columns of different datatypes.

Consider the following set of data

declare @t table(id int, computed_id as id, computed_date as dateadd(day,id,getdate()))
insert into @t (id)
select 5
select * from @t

The result is

As you can see, the datatype of computed_id will be same as that of the ID as ID is directly used in the computed column definition. However the datatype of the column computed_date will be datetime because the expression dateadd(day,id,getdate()) will do an implicit conversion to datetime datatype as getdate() is used in the definition.

Let us consider another set of data

declare @t table(id int, computed_id as id/2.0, computed_date as id*300000000000)
insert into @t (id)
select 5
select * from @t

The output is

As you can see, the datatype of computed_id will be of decimal type because of the expression id/2.0 which results to decimal number. The datatype of the column computed_numberf will be BIGINT because the expression id*300000000000 will do an implicit conversion to the BIGINT datatype as the result won't fit into a INT datatype

So the datatpye of computed column differs based on the expression and if you want to update the value returned by a computed column to another table, you need to make sure that the datatypes match each other.

Keep these points in mind while using Computed columns in SQL Server

SET ROWCOUNT VS TOP in SQL Server

noreply@blogger.com (Madhivanan) — Wed, 07 Nov 2012 09:12:00 +0000

Both SET ROWCOUNT statement and TOP clause are used to limit the number of rows returned. However there are some significant differences between them. They are listed out here

SET ROWCOUNT statement	TOP clause
It is specific to a batch. It will affect all DML operations until it is reset to 0	It has statement level scope and it will not affect other statements until specified each for them
Variable can be used in all version. Ex SET ROWCOUNT @var	Variable can be used only from version 2005 onwards like TOP (@var)
Not possible to set percentage	Possible to set percentage option. Ex SELECT TOP 20 percent * FROM TABLE
Not possible to specify decimal value	Possible to specify decimal values along with PERCENT option.
It is executed outside of actual DML and its value is not part of query plan	The expression used in TOP clause will be considered as part of query plan.
Multiple SET ROWCOUNT statements are allowed in a single batch. However the lastly available before the statements will be used. SET ROWCOUNT 10 SET ROWCOUNT 100 SELECT * FROM SYS.OBJECTS SET ROWCOUNT 0 The count 100 will be considered for execution	Multiple TOP is not allowed however they can be nested. SELECT TOP 10 * FROM ( SELECT TOP 100 * FROM SYS.OBJECTS ) AS T The final result will have maximum of 10 rows
As this is executed as a seperate statement It can not be part of VIEW definition	It can be part of VIEW definition.
This is marked as Deprecated. Avoid using this	Always available in all versions

Scope of variables in Dynamic SQL - SQL Server Vs MySQL‏

noreply@blogger.com (Madhivanan) — Fri, 02 Nov 2012 07:32:00 +0000

I have already posted about how Dynamic SQL works in SQL Server and MySQL at http://www.sqlservercurry.com/2012/08/dynamic-sql-sql-server-vs-mysql.html

There is a significant difference between SQL Server and MySQL as far as the scope of variables is concerned. The variable declared and accessed in Dynamic SQL can be accessed out of Dynamic SQL in MySQL, whereas this is not possible in SQL Server

Consider the following set of data

create table testing(id int, names varchar(100))
insert into testing(id,names)
select 1,'test1' union all
select 2,'test2' union all
select 3,'test3'

MySQL

The purpose is to assign a value to a variable in dynamic sql and access the same variable out of dynamic sql

set @sql:='set @count:=(select count(*) from testing);';
prepare stmt from @sql;
execute stmt ;
select @count;

In the above code, the variable @count is declared and assigned in dynamic sql. But after dynamic sql is executed using the prepare statement, the variable is still accessible. The statement select @count returns the value 3

SQL Server

Create the same table testing in SQL Server. Now execute the following code

declare @sql varchar(8000)
set @sql='
    declare @count int
    set @count=(select count(*) from testing)
    '
execute (@sql)
select @count

You will get the following error

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

The code before select @count will get executed correctly. But the variable declared in dynamic sql cannot be accessed out of it. If you access it in same dynamic scope like below, it will work

declare @sql varchar(8000)
set @sql='
    declare @count int
    set @count=(select count(*) from testing)
    select @count
'
execute (@sql)

So you need to be aware of this behavior when using Dynamic SQL.