SEQUENCE in SQL Server 2012

Up to SQL Server 2008 R2, to generate the sequential number we have one of the option is identity. As we know, identity is specific to column of a table only SO.........

What if, we want to generate sequential numbers across tables?

What if, we want to get the next sequence number even before inserting the new record?

So SQL Server 2012 (Denali) introduced, “Sequence Object” as the solution of above mention concerns.

WHAT IS SEQUENCE OBJECT?

In one line, The SEQUENCE object is one of the new T-SQL built-in types that automatically generate sequential numbers in specific conditions and settings.

A Sequence object is user-defined schema-bound object type. Sequence object generates the next sequential number as per the definition with which the sequence object has been created. The next sequential number could be either in ascending or descending order at a defined interval, as per definition. Sequence object may reuse/recycle numbers if it reaches the limit, if you have specified to recycle while creating the sequence object.

TSQL statement to create a sequence object:-

CREATE SEQUENCE  sq_Test  AS INT *     START WITH 1       INCREMENT BY 1       MINVALUE 1       MAXVALUE 10000       CYCLE       CACHE 100;   GO

*If no data type is provided, the bigint data type is used as the default.

Description of various clauses of Sequence object:-

·         START WITH 1

o   Identifies the starting number in the sequence

·         INCREMENT BY 1

o   The incrementing value of the sequence

·         MINVALUE

o   The minimum value the sequence can produce

·         MAXVALUE

o   The maximum vale the sequence can produce

·         CYCLE

o   If the MAXVALUE is set with the CYCLE argument provided, when the MAXVALUE is reached the sequence will cycle to the MINVALUE and start all over.

·         CACH 100;

o   If a CACHE argument is provided, SQL Server will cache (store in memory) the amount of values specified.

Once the sequence object has been created, you can use the “”Next Value For”” function, to generate a sequence value from it. It’s a non-deterministic function.

Unlike identity columns, a sequence is created independent of any table. A few interesting differences between sequence object and identity, simultaneously advantages of Sequence object are;

·         A Sequence object is independent of any table, whereas the Identity column property is table specific.

·         Unlike Identity, you can generate a new sequence value before using it in a SQL Statement .

·         You can define both the minimum & maximum values, as well as cycling & cache size options for a sequence.

·         Unlike Identity, a SQL object will increment its value only when it is explicitly called.

·         If we want to share a single series of numbers between multiple tables or multiple columns within a table.

·         The application must restart the number series when a specified number is reached. For example, after assigning values 1 through 10, the application starts assigning values 1 through 10 again.

·         The application requires sequence values to be sorted by another field. The NEXT VALUE FOR function can apply the OVER clause to the function call. The OVER clause guarantees that the values returned are generated in the order of the OVER clause's ORDER BY clause.

·         An application requires multiple numbers to be assigned at the same time. For example, an application needs to reserve five sequential numbers. Requesting identity values could result in gaps in the series if other processes were simultaneously issued numbers. Calling sp_sequence_get_range can retrieve several numbers in the sequence at once.

Limitations:

The NEXT VALUE FOR function cannot be used in the following situations:

·         When a database is in read-only mode.

·         As an argument to a table-valued function.

·         As an argument to an aggregate function.

·         In subqueries including common table expressions and derived tables.

·         In views, in user-defined functions, or in computed columns.

·         In a statement using the DISTINCT, UNION, UNION ALL, EXCEPT or INTERSECT operator.

·         In conditional expressions using CASE, CHOOSE, COALESCE, IIF, ISNULL, or NULLIF.

·         In a VALUES clause that is not part of an INSERT statement.

·         In the definition of a check constraint.

·         In a statement using TOP, OFFSET, or when the ROWCOUNT option is set.

·         In the WHERE clause of a statement.

Example:-

-- Create a SEQUENCE object on schema "dbo".
CREATE SEQUENCE test;

-- Verify if the test is created.
SELECT * FROM sys.sequences WHERE name = ‘test’;

-- To generate the next value from your Sequence object

SELECT NEXT VALUE FOR test;

-- To restart a Sequence object to its initial or specified value

ALTER SEQUENCE test   

RESTART;

-- To specify a restart value

ALTER SEQUENCE test   

RESTART WITH 10;

reachable: agrawals1307@gmail.com



How to get Index Name and Index Column in SQL SERVER

SELECT OBJECT_NAME(ix.Object_id) 'Table Name'          

 , ix.name 'Index Name'           

, col.name 'Index Column Name'           

, ix.type_desc 'Index Type'

FROM sys.indexes ix

INNER JOIN sys.columns col     

ON col.OBJECT_ID = ix.OBJECT_ID

WHERE ix.OBJECT_ID = OBJECT_ID ('<TABLE_NAME>')

AND col.name = '<COLUMN_NAME>'

UniqueIdentifier

Uniqueidentifier is a data type in SQL Server, is stored natively as a 16-byte binary globally unique value. If we are saying, Uniqueidentifier is globally unique then it means Uniqueidentifier doesn’t have its duplicate value in any system of the world (but in very very less chances, exception can be there because of the procedure to generate the global unique value). The main purpose of Uniqueidentifier, is assign a value to a record to identify uniquely across the network.

A GUID value for a uniqueidentifier column is usually obtained by one of the following ways:

1)      In a Transact-SQL statement, batch, or script by calling the NEWID function.

2)      In application code by calling an application API function or method that returns a GUID.

The above mentioned method, generate global unique value based on the identification number of network card plus a unique number from the CPU clock.

The uniqueidentifier data type does not automatically generate new globally unique value for inserted records. To get the new value, we have to use newid function. The values, returned by newid function have the below format:-

SELECT NEWID()

-- RESULT
DD8567C5-E0A0-4F65-B03A-7ED50E98CB64

A table can have multiple uniqueidentifier columns.

Advantage of UniqueIndentifier : –

1)      Global Unique ID will be generated (very less chances of for duplication).

2)      Can be used when assigning an identifier that must be unique in a network that has many computers at many sites.

The uniqueidentifier data type has the following disadvantages:

1)      The values are long and obscure. This makes them difficult for users to type correctly, and more difficult for users to remember.

2)      The values are random and cannot accept any patterns that may make them more meaningful to users.

3)      There is no way to determine the sequence in which uniqueidentifier values were generated. They are not suited for existing applications that depend on incrementing key values serially.

4)      At 16 bytes, the uniqueidentifier data type is relatively larger than other data types, such as 4-byte integers. This means indexes that are built using uniqueidentifier keys might be relatively slower than indexes using an int key.

Example of Uniqueidentifier :-

-- CREATION OF TABLE HAVING COLUMN OF UNIQUEIDENTIFIER DATA TYPE

CREATE TABLE tbl (col uniqueidentifier)

-- RECORD/VALUE INSERTION

INSERT INTO tbl VALUES (NEWID())

-- GET THE RECORDS

SELECT *

FROM tbl

-- RESULT

 DAC60E7B-9457-41AD-982E-DA3ADC04C695 

reachable: agrawals1307@gmail.com

SQL Server Query Optimizer

What is SQL Server Query Optimizer:-

SQL Server Query Optimizer is a built-in mechanism of SQL Server to optimize the query, submitted by user.

Query Optimizer try to prepare the best optimized plan for execution of SQL Queries. As exceptions are always there so statement that can’t be optimized such as flow-of-control and Data Definition Language commands, are compiled into an internal format so now here we can say there is no optimization for flow-of-control and Data Definition Language. Query Optimizer mainly concern with Data Manipulation Language statements.

Query Optimizer always try to use best optimized plan to execute a query but again sometimes it cannot be true depends on various conditions. There might be various other ways to execute the same query. So in other words we can say that SQL Query Optimizer’s job is determine the best execution plan out of various other ways. Execution plans are a result of the optimizer's calculations.

How SQL Server Query Optimizer works:-

SQL Server Optimizer is a cost based optimizer. It analyzes various ways of execution and estimates the cost of each of them and selects the lowest cost of choices considered.

When user submit a t-SQL query then at very first step to produce the execution plan, Query Optimizer normalize the query, which means that query potentially break down into multiple fine grained query.

After normalizing a query, Optimization step take place. Query Optimizer determines a plan to execute that query. If we are saying Query Optimizer is Cost based optimizer then it means it consider the cost of internal metrics that include estimated query requirements, CPU utilizations, number of required I/Os, availability of data in required tables, availability of indexes on required column and table. Based on the available information, the Query Optimizer considers the various access methods and processing strategies that it could use to resolve a query and chooses the most cost-effective plan.

Simultaneously, Query Optimizer also makes sure that it doesn’t take longer time to choose the execution plan.

Once normalization and optimization get completed, now this the time, when execution plan get compiled. Each command included in it, specifies exactly which table will be affected, which index going to be used (if there is any indexes), impact of constraints, A simple request to insert one row into a table with multiple constraints can result in an execution plan that requires many other tables to be accessed or expressions to be evaluated as well.

Existence of partition function for given value

SELECT ‘partition function exist for given value’

FROM sys.partition_functions  fn

INNER JOIN sys.partition_range_values val

ON fn.function_id = val.function_id

WHERE fn.name = '<partition function name>'

AND val.value = '<value>'

Alter partition scheme to use another file group

ALTER PARTITION SCHEME <partition scheme name>

NEXT USED <another file group name>

Existence of Partition Function:-

SELECT *

FROM sys.partition_functions

WHERE name = '<partition function name>'

reachable : agrawals1307@gmail.com

Add file to file group

ALTER database test

ADD FILE (

NAME =’<file name>’

                                , FILENAME = '<full_Path\file_name.ndf>'

                )

TO FILEGROUP <file group name>

reachable : agrawals1307@gmail.com

Add File Group

To add the file group :-

ALTER DATABASE TEST

ADD FILEGROUP <file group name>

facebook page
reachable : agrawals1307@gmail.com

File Group existence

To check the existence of file group :-

SELECT *

FROM sys.filegroups

WHERE name = ‘<file group name>’

facebook page

reachable : agrawals1307@gmail.com

Limitations of Table Valued Parameter

In my previous post, we have gone through the Table Valued Parameter, so now this is the time to come up with its limitation. Below are the limitation of Table Valued Parameter :-

·         SQL Server does not maintain statistics on the Table Valued Parameter Columns.

·          Table-valued parameters are read-only in Transact-SQL code. We cannot update the column values in the rows of a table-valued parameter and we cannot insert or delete rows. To modify the data that is passed to a stored procedure or parameterized statement in table-valued parameter, we must insert the data into a temporary table or into a table variable.

·         Table type variable cannot perform

“INSERT INTO @TVP EXEC <procedure>”    AND

“SELECT * INTO @TVP <Table Name>” statement.

·          Table Valued Parameter feature is only available on SQL Server 2008 and above.

·          We cannot pass table-valued parameters to CLR user-defined functions.

·          Table-valued parameters can only be indexed to support UNIQUE or PRIMARY KEY constraints.

·          We cannot use ALTER TABLE statements to modify the design of table-valued parameters. The only way to change the table definition is to drop the type and recreate it.

·          A user-defined table type cannot be used as a column in a table or a field in a structured user-defined type.

·          A DEFAULT value cannot be specified in the definition of a user-defined table type.·         User-defined functions cannot be called within the definition of computed columns of a user-defined table type.

·          We cannot use a Table valued parameters as OUTPUT parameters, for example,  

“CREATE PROCEDURE proc_TableValueParam   (@p_TVP userDefinedTableType OUTPUT)”.

·          We cannot perform DML operations like UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine.

facebook page

reachable : agrawals1307@gmail.com

Table Valued parameter to Stored Procedure

SQL Server 2008 provides us ability to pass batch of parameter in the form of table valued parameter to stored proc. Earlier we could pass batch of parameter to stored proc with the help of XML only. XML construction and parsing is little bit cumbersome, now SQL Server 2008 overcome with this limitation.

Table-valued parameters are declared by using user-defined table types. Table-valued parameters are like array.

Steps to pass table valued parameter to Store Procedure:-

1)      Define the type as a table and its structure

2)      Create Proc having variable as above(in step 1) created table type as input param

3)      Declare a variable of above created table type

4)      Insert the values to variable

5)      Pass the variable to Stored Proc

Now we get more clarity on passing the Table-valued parameter to Stored Procedure with the help of below example:-

Define the type as a table and its structure:-

CREATE TYPE tbl_TableType AS TABLE (id INT

, Name VARCHAR (50)

    )

Now we can check the existence of above create user defined table type with the help of below query:-

SELECT * FROM sys.table_types

Create Proc having variable as above(in step 1) created table type as input parameter :-

CREATE PROCEDURE proc_TableValueParam ( @p_TableVariable  tbl_TableType READONLY )
AS
BEGIN
        SELECT  *
        FROM @p_TableVariable 
END
GO

Declare a variable of above created table type:-

DECLARE @v_table  tbl_TableType

Insert the values to variable:-

INSERT  INTO @v_table
VALUES  ( 1, ‘Aa’ )
                ,  ( 2, ‘Bb’ )
                ,  ( 3, ‘Cc’ )

Pass the variable to Stored Proc:-

EXEC dbo. proc_TableValueParam @ v_table

Result:-

id	name
1	Aa
2	Bb
3	Cc

reachable : agrawals1307@gmail.com
facebook page

Negative value as an Object Id

Now from SQL Server 2012 onwards, SQL Server can assign negative values as object id to any object.

So if we are using below code to check the existence of object, can be failed from SQL Server 2012 and onwards:-

IF OBJECT_ID(‘Object_Name’)>0

BEGIN

PRINT ‘Object with the name Object_Name exist in Database’

END

So better to use either of below options from SQL Server 2012 and onwards to check the existence of object:-

SELECT TOP 1 NULL

FROM sys.objects

WHERE OBJECT_ID = ‘<mention object id>’

-- Object exist if we get NULL as a result

OR

SELECT OBJECT_ID(‘Object_Name’ IS NOT NULL

BEGIN

PRINT ‘Object with the name Object_Name exist in Database’

END


reachable:  agrawals1307@gmail.com
facebook page