SS SLUG - May 2012 Meetup

The May 2012 meet-up of the SQL Server Sri Lanka User Group is happening...

SESSION #1 - Windowing Functions in SQL Server
==========
Windowing functions in SQL Server allows you to apply aggregations on partitions of a result set. Wonder how this is done? Join in and find out.
This session is developer focused and appeals to DBAs as well.

LEVEL: Intermediate/Advanced

SPEAKER
--------------
Dinesh Karunarathne
Development DBA - Pearson Lanka
MCTS - SQL Server
http://dbbitsandpieces.blogspot.com

SESSION #2 - Breaking a SQL Myth in 300 Seconds - Table Variables with less data does not go to tempdb
==========
One myth involving SQL is that table variables that hold a small data set do not go into tempdb. Join Dinesh Asanka as he breaks this myth in 300 seconds.

SPEAKER
--------------
Dinesh Asanka - MVP, SQL Server
Production DBA - Pearson Lanka
http://dbfriend.blogspot.com

LOCATION: Microsoft Sri Lanka [Level 11 - DHPL Building, No. 42 Nawam Mawatha, COLOMBO 2]
DATE: Wednesday 23rd May, 2012
TIME: 6:00 PM Onwards
ENTRANCE: FREE

On a related note, the SQLServerUniverse.com site is down for a few weeks. We are working on getting it up and running again. Till then we shall communicate via LinkedIn, Facebook and Twitter...

SQL Server 2012 CU 1 Released

SQL Server 2012 Cumulative Update 1 contains fixes released in SQL Server 2008 R2 SP1 CU 3 and 4. For customers upgrading to SQL Server 2012 from SQL Server 2008 R2 SP1 CU3 (Build 10.50.2789.00) or CU4 (Build 10.50.2796.0) should consider applying SQL Server 2012 CU 1 to ensure fixes resident on the system are available post upgrade.

• CU#1 KB Article: http://support.microsoft.com/kb/2679368
• SQL Server Support Information: http://support.microsoft.com/ph/2855

TIES vs RANK

Let us come with this scenario. I want to display student who has got highest marked.

Let us say my data set is similar to following.

You might not think this is a hard question and you will say you can go with TOP 1 with DESC for the Value column as shown in the below query.

However, the problem is what if there are more than one top scored students in the list as above will return only record.

To solve the above problem, you can go with RANK function as shown bellow.

However, in TOP syntax there is a feature called WITH TIES to address the above issue.

Following is the results for this.

You can see that though you specified TOP 1, it returns you all the records which has highest marks.

Let us analysis the query plan for the both queries. First is the query with WITH TIES and the second is with RANK function.

So, WITH TIES query is preforming better to the RANK.

CTEs for SQL Server Developers

These days my blog is hit with temp table, table variable and CTE discussions. So I did a full analysis of CTEs, how and when they can be used. Here is the full article of it.

Pivot Task in SQL Server 2012

Pivot transformation in SQL Server Integration Services has gone into user interface change to ease the pivot configurations. If you have done this in SQL Server 2008 and before, you know how difficult to configure Pivot in SSIS.

Well, above interface will tell you that how easily you can configure pivot. Sometimes, you might have dilemma as which column should I select for columns and for rows. However, with this user friendly interface, user can visualize which column coming where.

From the following image, you can see how data was pivoted using the above configuration.

What is Best for Table Scan? Clustered Index or No Index

Always we believe Clustered Index is the best. However, what it will for table scan.

Here is the simple test of it.

First let me create a data to play around.

Then let me create a table with clustered primary key.

Then populate some data. Following script will some time to populate considering the page splits.

Then let me create a table with no index.

As before let me populate same set of data to the table with no indexes.

Now let us select data from both tables.

Following times shows that table with index has taken more time to read than table with no indexes.

and execution plan confirmed it.

Question is why?

Let us see the fragmentation on tables.

Result is,

So this tells you the story. Since you have clustered index, there are fragmentations. In the above scenario, table with clustered index has 26,330 pages while table with no index has 18,519 which is why table with no index is faster.

Ok, let us do rebuild the index and rerun all the queries with clearing the cache. Executions plans are 50/50.

Let us see number of pages for both cases.

In this you can see pages are high in table without any indexes. However, you will see that querying the entire table is slightly higher in the table with clustered index.

Here is the sample script you can play around.

Important facts about SEQUENCES

SEQUENCE is introduced with SQL Server 2012 to replace IDENTITY worries of the users. There are two important facts about SEQUENCE.

1. Following script will create a SEQUENCE named seqTest and in the line 7, it requests for the next value which will be the first value.

‘

What do you think you will get as the value? Many answers will be either 0 or 1. But the value is

-9223372036854775808. I don’t think you guess it.

Let us go into the details. When you create a SEQUENCE without specifying the data type by default it will take BIGINT as the data type. If you didn’t specify the starting number it will start from the least value which is the –9223372036854775808 (least value for BIGINT is

-9,223,372,036,854,775,808).

Let us look at another scenario with following script.

What do you think about the above script? Now, it’s type is SMALLINT while it has increment with –1. Since we have not specified any start values, it will start from the -32,768 which is the least value for SMALLINT data type and since our sequence specifies to INCREMENT BY –1, you will say that above script will generate an error. Sorry, you got it wrong again. Value will be 32767. When you create a sequence in, it will use the entire range available for the given data type unless you specify a range to use with the MINVALUE and MAXVALUE arguments. In addition to this, SQL Server will check whether the sequence is ascending or descending, and start the sequence at the minimum (ascending) or maximum (descending) value unless you use the START WITH argument.

Read more about SEQUENCE at http://msdn.microsoft.com/en-us/library/ff878091(v=SQL.110).aspx

Data Viewer in SQL Server 2012

Can you remember how you enabled data viewers in previous versions of SSIS? Well, that few clicks which seems like unnecessary. With SQL Server 2012 SSIS, simply right click the data flow path and select Enable Data Viewer and you are done.

Similarly, if you want to disable them follow the same path.

New T-SQL Features in SQL Server 2012

Quite number of new T-SQL features are introduced with SQL Server 2012. This article explains you about new features.

ALTER TABLE

In earlier versions, you can use later statement for tables, specifying four part table names. For example specifying the format .database.schema.table or the format ..schema.table will be succeeded. However, in SQL Server 2012 it will fail.

So in case you have referenced tables when altering them as a four part table name, make sure you change them. You might not use this type of statements directly however there are chances that applications, will use for ad-hoc queries.

What's the difference between a temp table and table variable in SQL Server?

There are lot of discussions on the above topic after my previous post on this matter.

This link gives you answers to most of your questions. Hope this will help.

Finding Replication Publications For A Table

If you want to drop or rename a table, you will need to make ensure the table is not an article of a publication. If it is article for any publication, you need to find out what are those publications.This faq will give you a query to find tables which are used for what publications.