T-SQL, SQL Administration Articles Link List – July 2011

Here’s a quick wrap up of the articles published on SQLServerCurry.com in the month of July 2011

SQL Server Administration Articles

SQL Server: Top 10 Cached Queries - We have covered Dynamic Management views (DMV’s) many times earlier. In this article, we will see how to view the most cached statements on the server.

SQL Server Bit Data Type and how it Stores Values - SQL Server supports the bit datatype which can be used to store flag values like 'true' or 'false'. However it should be noted how the values are stored in the bit column. This article gives you an overview of the same.

SQL Server 2008 R2 SP1 and Denali CTP 3 Released - Download links - Microsoft recently released the first service pack of SQL Server 2008 R2 for it’s RTM as well as Express Edition.

SQL Server Migration Assistant for Oracle v5.1 - Microsoft SQL Server Migration Assistant (SSMA) for Oracle is a tool to automate migration from Oracle database to SQL Server. Microsoft recently released an update to this tool

T-SQL Articles

SQL Server 2008: Divide Time Column with Another Column - Here’s a simple example that divides a SQL Server 2008 Time column with another to calculate the time taken per employee to finish the task.

SQL Server: DateTime vs DateTime2 -  The datetime2 datatype was introduced in SQL Server 2008 along with the date and time datatypes. Unlike the datetime datatype in SQL Server, the datetime2 datatype can store time value down to microseconds and avoids the 3/1000 second rounding issue.

SQL Server: Common mistake while Calculating Quarter Sales - When it comes to calculating quarter sales, I have always seen developers grouping only by quarter which is incorrect.

SQL Server: Distinct Count across Multiple Tables - One of my clients had a SQL Server database with an email column repeated across multiple tables. He wanted two queries – all email’s that are not repeated across the table, i.e. distinct ones as well as a count of the email addresses across multiple tables in the database.

SQL Server: @@RowCount and Zero Count - The SQL Server system function @@rowcount is used to return the number of rows affected by the last executed statement. There are atleast three cases where @@rowcount will return the value 0. Let us explore these cases in this post.

SQL Server: Find Current Language of the Server - Suppose you are working in an environment in which your server supports various languages and you want to find out what is the current language of server.

SQL Server: Behavioral Change in Union Operator - There is a behavioral change in the SQL Server UNION operator from version starting from 2005.

SQL BI Articles

SSIS: What’s New in SQL Server Denali - I was looking for an introduction to what’s new in SQL Server Integration Services (SSIS) in Denali, the next version of SQL Server. I stumbled upon a nice video which gives an overview of the new features of SSIS in the next CTP of Denali

SQL Server Denali Videos - After my last post on What’s New in SSIS: SQL Server Denali, I got some requests via twitter to list videos that cover more on SQL Server Denali. Here are some videos I like, that were recorded during Tech-Ed and other tech events:

Other Articles

Microsoft MVP Award – 4 Years in a Row - I was awarded the Microsoft MVP title again for the 4th consecutive year, and I thank Microsoft for bestowing this award on me.

Feedback:

SQL Server: Behavioral Change in Union Operator

There is a behavioral change in the SQL Server UNION operator from version starting from 2005.

In versions prior to SQL Server 2005, the union operator would not only remove duplicates but also order the resultset by the first column.

For eg: run this code in SQL Server version 2000

select 2 as number
union
select 1


and observe the results. The result set is ordered by number in an ascending order



But the same code will not order the result set in version 2005. Run the same code  and see the result set




Now run same query in SQL 2008, and observe the results! Also note that when there is a duplicate, union will behave the same way in all versions.

Feedback:

SQL Server: Top 10 Cached Queries

We have covered Dynamic Management views (DMV’s) many times earlier. In this article, we will see how to view the most cached statements on the server.

The view sys.dm_exec_cached_plans and function dm_exec_sql_text can be used for this purpose.

Let us execute the following query


select top 10
    db_name(sql_text.dbid) as db_name,
    cache_plan.objtype,
    sql_text.text ,
    cache_plan.usecounts
from
    sys.dm_exec_cached_plans as cache_plan
    cross apply sys.dm_exec_sql_text(plan_handle) as sql_text
order by
    usecounts desc

Here are some important points to note about this query:

• The result is based on the descending order of the column ‘usecounts’ which is used to indicate the number of times the cached objects are used.
• The column ‘object type’ indicates whether the statement is of the type procedure,view, adhoc etc.
• The column text shows the exact statement which was cached.
• The column db_name is null for the cached types adhoc, prepared, etc

OUTPUT

Feedback:

SQL Server Bit Data Type and how it Stores Values

SQL Server supports the bit datatype which can be used to store flag values like 'true' or 'false'. However it should be noted how the values are stored in the bit column. This article gives you an overview of the same.

Consider this example

declare @b1 bit,@b2 bit, @b3 bit
select @b1=1,@b2=0,@b3=null
select @b1 as bit1,@b2 as bit2,@b3 as bit3
GO



The result shows 1,0 and null.

You can also use the string values 'true' and 'false' in place of 1 and 0.

declare @b1 bit,@b2 bit, @b3 bit
select @b1='true',@b2='false',@b3=null
select @b1 as bit1,@b2 as bit2,@b3 as bit3
GO



The result shows 1,0 and null.

However other than the string values 'true' and 'false', the bit datatype will not accept any other string as shown below:

declare @b1 bit
select @b1='test'
select @b1

The above query will throw the following error:

Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the varchar value 'test' to data type bit.

You can also assign any number to the bit value, but it will convert it to 1,0 or null. Remember that a bit datatype can only store these three values.

The following example shows that how numbers other than 0 or 1 are converted by the bit datatype

OUTPUT


As the result shows, any value which is not null or 0 will be converted to 1.

Feedback:

SQL Server: Find Current Language of the Server

Suppose you are working in an environment in which your server supports various languages
and you want to find out what is the current language of server.

You can use the following methods:

Method 1: Use system function @@language

select @@language

The system function @@language contains the name of the current language of the server

Method 2 : Use sys.syslanguages view

select name from sys.syslanguages
where langid=@@langid

The system function @@langid will have the id for current language

OUTPUT

Feedback:

SQL Server: @@RowCount and Zero Count

The SQL Server system function @@rowcount is used to return the number of rows affected by the last executed statement. There are atleast three cases where @@rowcount will return the value 0. Let us explore these cases in this post.

Case 1: When used with select statement that does not return any rows

select 1 where 1=2
select @@rowcount

Case 2: When used with SET options (setting nocount on/off, datefirst values, etc)

set nocount off
select @@rowcount

Case 3: When used with USE satement which changes the current database name

use master
select @@rowcount

OUTPUT

Feedback:

SQL Server: Distinct Count across Multiple Tables

One of my clients had a SQL Server database with an email column repeated across multiple tables. He wanted two queries – all email’s that are not repeated across the table, i.e. distinct ones as well as a count of the email addresses across multiple tables in the database.

I gave him a query that will help him out to do a count across multiple tables as well as list all distinct emails. Let us consider the following sample data

Now to do a count of email across multiple tables, use the following query:

SELECT tt.email, COUNT(tt.email) as EmailCount
FROM (  SELECT email FROM @t
        UNION ALL
        SELECT email FROM @t1) tt
GROUP BY tt.email

OUTPUT




Similarly to list all distinct email addresses, i.e the ones not repeated, use the HAVING clause (there are other ways too)

SELECT tt.email, COUNT(tt.email) as EmailCount
FROM (  SELECT email FROM @t
        UNION ALL
        SELECT email FROM @t1) tt
GROUP BY tt.email
HAVING COUNT(tt.email) = 1

Feedback:

SQL Server: Common mistake while Calculating Quarter Sales

When it comes to calculating quarter sales, I have always seen developers grouping only by quarter. For eg: Assuming we have a table called SalesTable with columns – SalesDate and Qty. To calculate the quantity sold per quarter, developers usually write the query as:

Now if the data is only for a year, the query would work. However this is seldom the case. In most of the reports I have seen,  end users want to see the quarters for 'each' year. So to do it correctly, you have to add the year too in the GroupBy clause, as shown below:

Now the query will differentiate each quarter by its year and produce correct results. It's a simple tip, but very easy to forget!

Feedback:

SQL Server Migration Assistant for Oracle v5.1

Microsoft SQL Server Migration Assistant (SSMA) for Oracle is a tool to automate migration from Oracle database to SQL Server. Microsoft recently released an update to this tool which can be downloaded over here

This tool does migration assessment analysis, schema and SQL statement conversion, data migration as well as migration testing. SSMA for Oracle v5.1 support migration from Oracle 7.3 or later version to all edition of SQL Server including SQL Server 2005, 2008, 2008 R2, and SQL Server Code-Named "Denali".

Feedback:

SQL Server 2008 R2 SP1 and Denali CTP 3 Released - Download links

Microsoft recently released the first service pack of SQL Server 2008 R2 for it’s RTM as well as Express Edition. To learn what’s new in SQL Server 2008 R2 SP1, check the What's New Section in Release Notes

Download Links:

Microsoft SQL Server 2008 R2 SP1 - Express Edition

Microsoft SQL Server 2008 R2 Service Pack 1

Note : To upgrade SQL Server 2008 R2 Express Edition, you need the SP1 of Express Edition or Express Edition with Advanced Services.

SQL Server Denali CTP3

Microsoft also released CTP3 of SQL Server’s next version code-named Denali. You can download SQL Server Denali CTP3 over here.  Here are some download links

Microsoft SQL Server code name 'Denali' PowerPivot for Microsoft Excel Community Technology Preview 3 (CTP 3)

Microsoft SQL Server code name 'Denali' Express Core Community Technology Preview 3 (CTP 3)

Product Documentation for Microsoft SQL Server “Denali” CTP3

Feedback:

SQL Server Denali Videos

After my last post on What’s New in SSIS: SQL Server Denali, I got some requests via twitter to list videos that cover more on SQL Server Denali.

Here are some videos I like, that were recorded during Tech-Ed and other tech events:

What's New in Microsoft SQL Server Code-Named "Denali" for SQL Server Analysis Services and PowerPivot

What’s New in Microsoft SQL Server Code-Named “Denali” for SQL Server Integration Services

What’s New in Microsoft SQL Server Code-Named “Denali” for Reporting Services

Microsoft SQL Server Code-Named "Denali" AlwaysOn Series,Part 1

Microsoft SQL Server Code-Named "Denali" AlwaysOn Series,Part 2

What's New in Manageability for Microsoft SQL Server Code-Named Denali

Performance Tuning and Optimization in Microsoft SQL Server 2008 R2 and SQL Server Code-Named Denali

What's New in SQL Server "Denali" for Developers | DevDays 2011

What’s New in Security for Microsoft SQL Server Code-Named Denali

Microsoft SQL Server: The Data and BI Platform for Today and Tomorrow

What's Cool in Microsoft SQL Server 2008 R2 and New in SQL Server Code-Named Denali

I will keep updating this list as I find more videos!

Feedback:

SSIS: What’s New in SQL Server Denali

I was looking for an introduction to what’s new in SQL Server Integration Services (SSIS) in Denali, the next version of SQL Server. I stumbled upon a nice video which gives an overview of the new features of SSIS in the next CTP of Denali

Here’s the video (http://channel9.msdn.com/Events/TechEd/NorthAmerica/2011/DBI317):



The video covers the improvements in the UI, SSIS designer, logging, troubleshooting and the new Parameter model.

Feedback:

SQL Server: DateTime vs DateTime2

The datetime2 datatype was introduced in SQL Server 2008 along with the date and time datatypes. Unlike the datetime datatype in SQL Server, the datetime2 datatype can store time value down to microseconds and avoids the 3/1000 second rounding issue. The precision with a datetime2 is upto 100 nanoseconds.

Here’s an example:



OUTPUT

As you can see, when using the datetime datatype is rounded to increments of .000, .003, or .007 seconds. However the datetime2 has a larger date range, a larger default fractional precision, and optional user-specified precision. The precision scale is 0 to 7 digits, with an accuracy of 100 nanoseconds. The default precision is 7 digits.

Moreover datetime2 supports a date range of 0001-01-01 through 9999-12-31 while the datetime type only supports a date range of January 1, 1753, through December 31, 9999. The timerange as mentioned earlier in case of datetime is 00:00:00 through 23:59:59.997 whereas in datetime2 is 00:00:00 through 23:59:59.9999999.

The MSDN documentation also recommends datetime2 over datetime:

Use the time, date, datetime2 and datetimeoffset data types for new work. These types align with the SQL Standard. They are more portable. time, datetime2 and datetimeoffset provide more seconds precision. datetimeoffset provides time zone support for globally deployed applications.

Feedback:

SQL Server 2008: Divide Time Column with Another Column

Here’s a simple example that divides a SQL Server 2008 Time column with another to calculate the time taken per employee to finish the task.

Let’s see a sample table first



DECLARE @Timesheet table (
NatureOfWork varchar(15),
TimeSpent time,    
NoOfWorkers int )
INSERT INTO @Timesheet
SELECT 'Plumbing', '02:56:00.0000000', 5
UNION ALL
SELECT 'Fixtures', '05:26:50.0000000', 3
UNION ALL
SELECT 'Sweeping', '0:39:40.0000000',  2

In order to calculate the average time taken per worker to complete the task, we will need to divide the ‘TimeSpent’ column with the ‘No of Workers’ column. We will use the DATEDIFF function to return the time in seconds and then divide it by no. of workers to find the time taken by each worker to complete the task.

SELECT NatureOfWork,
SUM(DATEDIFF(SECOND, '00:00:00',TimeSpent))/
SUM(NoOfWorkers)
as [Average Time Per Person (in seconds)]
FROM @Timesheet
GROUP BY NatureOfWork

OUTPUT

Feedback:

Microsoft MVP Award – 4 Years in a Row

“The key to happiness is having dreams. The key to success is making your dreams come true.” - Unknown

I was awarded the Microsoft MVP title again for the 4th consecutive year, and I thank Microsoft for bestowing this award on me. I am glad my efforts and contributions to the community via my sites DotNetCurry.com, DevCurry.com and SqlServerCurry.com were recognized by Microsoft.

About the MVP award

For those who are not aware of the MVP award, Wikipedia says:

The Microsoft Most Valuable Professional (MVP) is the highest award given by Microsoft to those it considers "the best and brightest from technology communities around the world" who "actively share their ... technical expertise with the community and with Microsoft"

Some MVP award Facts:

• Around 4000 MVP’s in the world
• Live in more than 90 countries
• Speak nearly 40 languages
• Answer more than 10 million questions each year
• Offer expertise in around 90 Microsoft technologies

MVP Award Benefits

Being an MVP is fun and rewarding too! Along with an MVP trophy and a certificate, Microsoft values its awardees by providing them with access to several technical resources such as complimentary subscriptions to MSDN or TechNet, access to MVP private newsgroups, technical support incidents, passes and discounts to MS events, insider news and an opportunity to interact with product teams and be a part of the product development cycle.

One of the primary benefits this award gives you is the opportunity to socialize with other MVP’s across the world – like the Microsoft MVP Global Summit and MVP Open Days held almost every year.

How do I Become an MVP?

“That some achieve success, is proof to all that others can achieve it as well.” – Abraham Lincolm

You may want to look at How do I become an MVP ? If you feel you have got what it takes to become an MVP, drop me a comment and we will take it further. This is an elite award, every IT Pro and Microsoft Developer dreams of getting.

Cheers to another year of success! I am an MVP and I am proud of it!

Feedback:

T-SQL, SQL Server BI Articles Link List – June 2011

Here’s a quick wrap up of the articles published on SQLServerCurry.com in the month of June 2011

T-SQL Articles

FLOOR a DateTime in SQL Server - Suppose you are given a datetime and want to emulate the FLOOR function on it eg: find first day of year, first day of month etc, you can use the code shown in this post.

Concatenate Strings in SQL Server - Different ways - There are many ways to concatenate data in a single column. This post shows some of these ways.

SQL Server NTILE Function - 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.

SQL Server: Different Ways to find SPID - There are many ways to know the Server Process id or now also known as Session ID (SPID).

Standard Deviation in SQL Server and Aggregation on Aggregates - SQL Server makes calculation of Standard Deviation relatively easy with the STDEV statistical aggregate function. What I learnt recently was that you can either apply this function to all values or can even select distinct values. You can also perform an aggregation on aggregates that can be used in reports

SQL Server: Calculate Summary and Column Summary - This post shows how to show a summary of similar rows as well as column summary for SQL Server table data. Suppose you want to generate the total of a column and display it at the end of the result set.

SQL Server Tutorials on Date Time - One of the most frequently asked question by SQL developers, is on handing Date, Time and other related datetime values in SQL Server. Developers are always looking out for solutions which demand either converting Date Time Values or finding date time ranges and so on. Madhivanan and I have already written a couple of articles on handling DateTime in SQL Server. In this post I will share some of the links with you.

SQL Server: Check if Date is End of Month - Here’s a very simple query that checks if the date is End of the Month.

SQL Server: Grouping ID Example - Let us suppose you have used the rollup operator to generate summary information along with the Details information and you now want to differentiate between summary and detail. You can make use of the SQL Server grouping_id function. Let us understand the GROUPING_ID function using some code.

SQL Server BI

SSIS Balanced Data Distributor Transform - The Balanced Data Distributor (BDD) is a new SSIS transform which takes a single input and distributes the rows to one or more outputs uniformly via multithreading. The transform takes one pipeline buffer worth of rows at a time and moves it to the next output in a round robin fashion.

Dynamic Connection String in SQL Server Reporting Services 2005/2008 - In this post we will see how to generate a report using dynamic connection string in SQL Server Reporting Services 2005/2008. The scenario is if we have different data with the same schemas, spread across different database and different SQL servers and people would like to see the data as per their preferences (like area wise), we can use a Dynamic Connection string.

SQL Server General

SQL Server 2008 R2 Training Kit (May 2011 Update) - Microsoft updated it’s SQL Server 2008 R2 Training Kit to support for Windows 7 SP1, Windows Server 2008 R2 SP1 and Visual Studio 2010 SP1.

SQL Azure Migration Wizard - For those who do not know about this tool, the SQL Azure Migration Wizard is an open source tool that helps you migrate your SQL Server 2005/2008 databases to and from SQL Azure. The tools analyzes your database for any compatibility issues and allows you to either fully or partially migrate your database schema and data to SQL Azure.

SQL Server Driver 2.0 for PHP 5 - Microsoft recently released SQL Server Drivers for PHP 2.0 which adds support for PDO with a new PDO_SQLSRV and contains bug fixes for the existing SQLSRV driver. The native APIs present in the PHP Driver for SQL Server 1.1 are still present, there is additional support for the PDO API.

Feedback: