Mastering SQL Server 2012 Sequences: Question and Answer

Q.1. How are Sequences Used in Sql Server 2012?

Ans: Sequences are frequently used in SQL Server 2012 when performing INSERT operations as an alternative to IDENTITY-attributed key columns for assigning new primary key values. Unlike IDENTITY columns, sequences exist as separate objects from the data tables, providing greater flexibility and fewer constraints. With sequences, you don't need to use SET IDENTITY INSERT ON/OFF statements to insert unique values into the primary key column or to get the next value in the sequence before performing the INSERT. Instead, any SELECT, INSERT, or UPDATE statement can request and increment the next value in the sequence using the NEXT VALUE FOR syntax. To gain a deeper understanding of SQL Server 2012 and its functionalities, an SQL certification would be beneficial.

Q.2. Describe using an Example, How to Generate New Primary Keys Using Sequence?

Ans: To understand how sequences function, let's examine the following code:

CREATE TABLE Customer
(Id
int PRIMARY KEY,
FirstName varchar(max),
LastName varchar(max))
Create the sequence with a start, increment, and min/max settings CREATE SEQUENCE CustomerSequence AS int
START WITH 1
INCREMENT BY 1
MINVALUE O
NO MAXVALUE
Use the sequence for new primary key values
INSERT INTO Customer (Id, FirstName, LastName) VALUES (NEXT VALUE FOR CustomerSequence, 'Bill', 'Malone'), (NEXT VALUE FOR CustomerSequence, 'Justin', 'Thorp'), (NEXT VALUE FOR CustomerSequence, 'Paul', 'Duffy')

In this example, the "Customer" table does not use the IDENTITY attribute for its primary key; instead, it uses an integer column. We create a sequence object named "CustomerSequence" using the new CREATE SEQUENCE statement in SQL Server 2012. This sequence accepts new integers with no upper limit and increments them by one, starting at one (note that it exceeds the maximum size for the int data type in this example, as it is a 32-bit int data type).

Next, we perform an INSERT operation, adding three new rows to the "Customer" table. We use the "NEXT VALUE FOR CustomerSequence" as the new Id value for each row, effectively generating new primary keys. The outcome of this operation will look like this:

SELECT * FROM Customer;
Id FirstName LastName
1 Bill Malone 2 Justin Thorp 3 Paul Duffy

Q.3. How to Emulate Identity-attributed Primary key Columns with Sequences using Default Constraint?

Ans: To automatically assign primary key values to new rows using sequences, you can set the "NEXT VALUE FOR" as a DEFAULT parameter on the Id column. 

--
Set the default for IDENTITY-behavior
ALTER TABLE Customer
ADD DEFAULT NEXT VALUE FOR CustomerSequence FOR Id
Generates customer ID 4
INSERT INTO Customer (FirstName, LastName) VALUES ('Jeff', 'Smith')

By using this approach, the INSERT statement behaves as if it were using an IDENTITY-attributed primary key column with the DEFAULT constraint in place. The "Id" value, in this case, will be automatically assigned (e.g., 4 for customer Jeff Smith). One advantage of using sequences is that you can query the "sys.sequences" catalog view to see the current value without actually using it directly:

SELECT current_value FROM sys.sequences WHERE name='CustomerSequence';
This would return:
current_value
4

Q.4. How to Change a Sequence Object Using Alter Sequence?

Ans: You can modify the behavior of an existing sequence object by using the ALTER SEQUENCE statement.

ALTER SEQUENCE CustomerSequence
RESTART WITH 1100
MINVALUE 1000
MAXVALUE 9999
CYCLE

The above code modifies the "CustomerSequence" to return the value 1100 as the next value, and it will keep incrementing by one after that. Once it reaches 9999, it will reset to 1000 and continue increasing. However, it's important to note that when recycling to 1000, you cannot use the sequence to add 100 new rows to the "Customer" table as there are already customers with primary keys starting at 1100.

Q.5. What are The Limitations of Sequences?

Ans: Sequences have some limitations that should be noted. For instance, sequences cannot be used as arguments in aggregate functions, subqueries, CTEs (Common Table Expressions), or in a TOP clause or CHECK CONSTRAINT definition. Additionally, sequences cannot be used in views, computed columns, or user-defined functions, as these object types are not allowed to cause unintended consequences in sequence number generation. Furthermore, you must drop the referencing constraints before deleting a sequence because you cannot use the DROP SEQUENCE statement directly to delete a sequence while there are tables with current DEFAULT constraints referencing the sequence.

Q.6. What Does The Sys.Sp_Describe_First_Result_Set Function Do?

Ans: The sys.sp_describe_first_result_set function allows you to submit a T-SQL statement and receive a detailed schema description of the first result set that can be returned. For example, using the same SELECT statement to retrieve information on all columns in the "HumanResources.Employee" table:

EXEC sp_describe_first_result_set @tsql = N'SELECT * FROM HumanResources.Employee'

The outcome of the above T-SQL statement will return detailed metadata about each column.

Q.7. What Does The Sys.dm_Exec_Describe_First_Result_Set Function Do?

Ans: Similar to sys.sp_describe_first_result_set in functionality, the data management function sys.dm_exec_describe_first_result_set can also be used to describe the first result set. It is implemented as a TVF (Table-Valued Function), making it easy to query and restrict the returned metadata. 

SELECT name, system_type_name
FROM sys.dm_exec_describe_first_result_set(
'SELECT * FROM Human Resources. Employee', NULL, 1) WHERE is_nullable
1

The result will display the names and data types of nullable columns.

Q.8. How to Discover a Schema Returned by a Parameterized Query?

Ans: If you have a parameterized query with the proper parameter signature, you can discover its schema. In the example provided, the T-SQL statement passes NULL for the "parameters parameter" as it has no parameters. By using the code, you can determine a parameterized query's schema.

SELECT name, system_type_name, is_hidden
FROM sys.dm_exec_describe_first_result_set(' SELECT OrderDate, TotalDue
FROM Sales.SalesOrderHeader
WHERE SalesOrderID
@OrderID int', 1)
@OrderID',

The output will provide information about each column, such as name, system_type_name, and whether it is hidden or not.

Q.9. Why is The Sys.dm_Exec_Describe_First_Result_Set_For_Object Function Used?

Ans: The data management function sys.dm_exec_describe_first_result_set_for_object can be used to discover the schema for any database object by providing the object ID and the "browse" flag, indicating whether hidden ID columns should be made available. The OBJECT_ID function is used to get the ID of the desired object.

CREATE PROCEDURE GetOrder Info (@OrderID AS int) AS
GO
SELECT OrderDate, TotalDue
FROM Sales.SalesOrderHeader
WHERE SalesOrderID = @OrderID
SELECT name, system_type_name, is_hidden
FROM sys.dm_exec_describe_first_result_set_for_object (OBJECT_ID('GetOrderInfo'), 1)

This example returns schema information about the stored procedure, displaying column names, system_type_name, and whether the column is hidden or not.

Q.10. What does The Sys.sp_Describe_Undeclared_Parameters Procedure Do?

Ans: The sys.sp_describe_undeclared_parameters stored procedure analyzes a T-SQL statement to determine its expected parameters. For instance, it can identify the @IsFlag parameter and suggest the int data type based on its usage inside the provided T-SQL statement. The stored procedure is beneficial when dealing with dynamic SQL or handling parameterized queries with undetermined parameters.

EXEC sys.sp_describe_undeclared_parameters N'IF @IsFlag 1 SELECT 1 ELSE SELECT 0'

Conclusion

The sys.sp_describe_first_result_set and sys.dm_exec_describe_first_result_set functions are valuable tools for obtaining detailed schema information about the result sets of T-SQL statements and database objects. They provide essential metadata for understanding the structure of queries and procedures.

However, it is important to be aware of the limitations of sequences, such as not being usable in certain contexts like aggregate functions, views, or user-defined functions. Additionally, when using sequences with DEFAULT constraints, attention must be paid to avoid conflicts with existing data.

Overall, this document provides a comprehensive overview of these SQL Server features, and further exploration through an SQL certification or other learning resources can deepen one's understanding of SQL Server 2012 and its functionalities.