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.

DBCC CHECKIDENT

Though this not a big deal but thought of mentioning it here. DBCC CHECKIDENT has two options, RESEED and NORESEED.

If previous SQL Server versions output message of the DBCC CHECKIDENT will be similar for both the options as shown in the below image.

However, in SQL Server 2012 RESEED option will not provide the current IDENTITY valueas it is already specified with the parameter as shown below.

Temp Tables Vs Table Variables Vs CTE

Temporary Tables (# tables) , table variables and CTEs are commonly used for storing data temporary.

Many people believe that Table Variable will be in the memory and Temp tables will be stored in the Tempdb database.

Let us see this. Following script will declare table variable and query the sys.objects in tempdb database.

So, declaring table variable it self will create a # table in tempdb throwing away the above myth.

Another myth is, table variable will be moved once the data load is high on the table variable. However, Following is a disk usage report taken for temp table and table variable.

Above report shows, that even for 5 rows, temp tables and table variable both have same in sizes which breaks the myth.

I did another test with #tables , table variables and CTE. Bellow graph is write transactions/sec for each scenario. So CTE does not use tempdb at all.

Following is from a KB article (http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k) but this applies to SQL server 2000. Couldn’t find any thing applies to 2008 or 2005.

Are table variables memory-only structures that are assured better performance as compared to temporary or permanent tables, because they are maintained in a database that resides on the physical disk?
A4: A table variable is not a memory-only structure. Because a table variable might hold more data than can fit in memory, it has to have a place on disk to store data. Table variables are created in the tempdb database similar to temporary tables. If memory is available, both table variables and temporary tables are created and processed while in memory (data cache).

Restrictions to NEXT VALUE FOR

Sequence object was introduced with SQL Server 2012. However there are last minute changes from RC0 to RTM. NEXT VALUE FOR function is used get the next value for the SEQUENCE object.

The NEXT VALUE FOR function is now disallowed when used in statements with

• DISTINCT
• UNION / UNION ALL
• EXCEPT
• NTERSECT
• TOP
• OFFSET
• when the ROWCOUNT option is set

The NEXT VALUE FOR function is now disallowed in conditional expressions:

• CASE
• CHOOSE
• COALESCE
• IIF
• ISNULL
• NULLIF

The full list of restrictions are documented in BOL.

Usage of NEXT VALUE FOR only allowed in the DEFAULT constraint for the target column and NEXT VALUE FOR cannot be used with the MERGE statement. If it is used with MERGE statement following error will be generated.

Msg 11742, Level 15, State 1, Procedure sampleProc Line 21 NEXT VALUE FOR function can only be used with MERGE if it is defined within a default constraint on the target table for insert actions. :

SQL Server 2012 RTM Cumulative Update 1

Microsoft has announced the release of SQL Server 2012 RTM Cumulative Update 1. SQL Server 2012 RTM Cumulative Update 1 incorporates 92 issues reported by 44 unique customers.

 

Successful release of Cumulative Update 1 is a testament to the commitment of the many teams within the SQL BU, who are finding solutions to provide a better customer experience.   The coordination, determination and execution between all the SQL BU teams was instrumental in continuing the SQL Sustained Engineering team’s long standing track record for on time release of Cumulative Updates.

The associated cumulative KB article has also been published.  Customers are directed to contact CSS to get the CU build or obtain the hotfix package through the new self-service feature by clicking on the “Hotfix Download Available” button found at the top of the KB article.

·        Public KB Article

·        Hotfix Download Location

·        SQL Server 2012 RTM CU1 Fix List

·        CU Website

SSIS UI Changes in SQL Server 2012

Annotation is also easy with SQL Server 2012 where auto grow is available with high usability.

Apart from the above valuable feature, almost all the task has undergone user interface change. Following is a sample of a SSIS package.

In this version of SSIS, you have the ability to execute dataflow tasks without a destination. This is indeed a valuable feature at the time of designing. Previously, either you have to have row count task as a destination or else you need to have third party task Thrash Destination task. Thrash Destination is a famous task simply because you must need a destination in previous versions of SSIS.

SSIS packages now have the auto save feature so that SSIS packages are easily recoverable. Also packages now have the zooming control built into the package.

Undo/ Redo facility is introduced to ease the development of SSIS. also twenty undo /redo times are supported.

Resource Governor in SQL Server 2012

Major improvement in Resource Governor is the increase of Maximum number of resource pools to 64 in 64 bit version of SQL Server. In the previous version it was only 20 pools. However, for the 32 but version still the previous limit exists.

Now you can configure MAX_CPU_PERCENT for a resource pool. With new parameter CAP_CPU_PERCENT, users can cap CPU usage by a pool, even when there is no contention on the box.

ALTER RESOURCE POOL resource_pool_name

WITH (CAP_CPU_PERCENT = 40);

Above code will set the resource pool resource_pool_name for maximum cpu percentage of 40 regardless of whether there are any workloads are running or not.

Users now have the option of setting the affinity to a scheduler, group of schedulers, or a NUMA node. If user wanted to set the affinity of a resource pool to schedulers 5 through 7 only, user can use following code.

ALTER RESOURCE POOL resource_pool_name

WITH (AFFINITY SCHEDULER = (5 TO 7));

Startup Parameters in SQL Server 2012

In Previous editions of SQL Server configuring Startup Parameters is night mare since missing ; means that you are not able to restart the SQL Server.

This is how you configure Startup Parameters in previous editions of SQL Server.

In SQL Server 2012, there is a separate tab called Startup Parameters if you navigate to service Properties dialog box.

This makes it easy to add / remove startup parameters and trace flags

Change in RowCount UI in SQL Server 2012

This has become much much simpler where you will be asked the variable name. In the previous versions, there are many options but mostly you enter the variable only. Therefore it is worth to have interface where you select the variable only.

Sample Database Modifications in SQL Server 2012

From SQL Server 2008 onwards, users have to download sample databases separately. This step was taken as security measure. However, it is still notice that these sample databases still exists in many production instances. So it is recommended to not to install these sample databases to production instances as a security measure.

In this book, most of the samples if not all are referring to these sample databases. So it is advisable to download these sample databases from download.codeplex.com.

There are two sample databases dedicated to SQL Server 2012 RC0. AdventureWorks2008R2_Data.mdf and AdventureWorksDWDenali_Data.mdf are those files. So as in the previous version of SQL Server, you won’t get an installer to install sample database. Instead, you need to attach these mdf files to your SQL Server instance.

You can attach these databases only to the SQL Server 2012 RC0. There is no schema nor data changes are made to the AdventureWorks2008R2 with compared to previous sample database. Only change is the removal of the FILESTREAM property from the Product.Document table. Do not confuse with the naming of this mdf since it is still named as 2008R2.

Though there is neither schema nor data changes in AdventureWorks2008R2, there are few changes on the DW end. New fact table called FactProductInventory introduced to hold inventory fact table. Also, DimDate table was filled to match latest dates.

SQL Server 2012 Licensing

According to Microsoft, they have simplified licensing model of the SQL Server. They have two licensing options called Cored based and Server + CAL. Following table shows which edition offers which licensing model.

 

Edition	Server + CAL	Core Based
Enterprise		X
Business Intelligence	X
Standard	X	X

Both Enterprise and Standard editions will be available under core-based licensing. Cored-based licenses will be sold in two-core packs. In addition to code based licensing, Standard Edition will also be available as a Server+ CAL licensing option.

The Business Intelligence and Standard Editions will be available under the Server + Client Access License (CAL) model. To access a licensed SQL Server in the server + CAL model, each user must have a SQL Server CAL that is the same version or newer.

Each SQL Server 2012 CAL can provide access to multiple licensed SQL Servers, including the new SQL Server 2012 Business Intelligence Server as well as prior versions of the Enterprise Servers.

To license a physical server, you must license all the cores in the server, with a minimum of 4 core licenses required for each physical processor in the server.

Following table shows you prices for SQL Server at the timing of writing this. This may subjected to change.

 

Edition	Pricing
Enterprise	$ 6,874 per core
Business Intelligence	$ 8,592 per Server
Standard	$ 1,793 per core OR $ 898 per Server
Client Access License	$209 per CAL

Source: MSDN

Price of the Developer edition is unknown the time of writing but in previous version of SQL Server, copy of Developer edition is 50 $. Though you can to all your development using this edition, it is prohibited to use this edition in a production environment.

LocalDB

LocalDB is light weight Express where it will perform as a local database to the application. It requires zero configurations and it comes with MSI installer.

You can download a LocalDB as SQLLocalDB.msi. Both 32 bit and 64 bit versions have the same file name. When you are installing LocalDB manually, wizard of three dialog boxes will be shown and you can easily install. If you want to install LocalDB from an application then obviously you need to install this in a quite mode.

msiexec /i SqlLocalDB.msi /qn IACCEPTSQLLOCALDBLICENSETERMS=YES

You might see a new parameter here which is IACCEPTSQLLOCALDBLICENSETERMS=YES. This will let the installer know that you accept the End User License Agreement (EULA). Without this option LocalDB installation will fail. By default, LocalDB will be installed into C:\Program Files\Microsoft SQL Server\110\LocalDB\Binn

Another important thing is with this you don’t need any SQL Server services to run which will reduce unnecessary load to the small scale applications. LocalDB processes are started automatically when it needs to connect to the database and stopped automatically. So the connection string to the LocalDB is,

"Data Source=(localdb)\v11.0;Integrated Security=true;AttachDbFileName=C:\ProjectData\myDatabase.mdf".

Important thing is to note is that LocalDB is not a replacement for the Express.