This article is half-done without your Comment! *** Please share your thoughts via Comment ***
What is an IDENTITY?
- You can use IDENTITY for auto increment value.
- We can create an identity column in a table.
- This property is used with the CREATE TABLE and ALTER TABLE Transact-SQL statements.
- We must specify both the seed and increment or neither. If neither is specified, the default is (1,1).
What are differences between @@IDENTITY, SCOPE_IDENTITY() and IDENT_CURRENT?
SELECT @@IDENTITY:
It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.
@@IDENTITY will return the last identity value entered into a table in your current session. While @@IDENTITY is limited to the current session, it is not limited to the current scope. If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.
SELECT SCOPE_IDENTITY():
It returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value.
SCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function.
SELECT IDENT_CURRENT(‘tablename’):
It returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value.
IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.
How you can reset the value of IDENTITY?
SQL Server: Script to reset Table Identity using DBCC CHECKIDENT
How to find all identity columns of a Database?
What is the solution of below error?
Cannot insert explicit value for identity column in table ‘IdentityTable’ when IDENTITY_INSERT is set to OFF.
Yes, When IDENTITY_INSERT is OFF, we cannot insert a data into an identity column of a table.
SET IDENTITY_INSERT table_name ON option, allows explicit values to be inserted into the identity column of a table.
If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value.
The setting of SET IDENTITY_INSERT is set at execute or run time and not at parse time.
What do you know about the Sequence object of SQL Server 2012?
A sequence is a user-defined schema bound object that generates a sequence of numeric values according to the specification with which the sequence was created. The sequence of numeric values is generated in an ascending or descending order at a defined interval and can be configured to restart (cycle) when exhausted.
Sequences, unlike identity columns, are not associated with specific tables. User applications can reference a sequence object and coordinate the values across multiple rows and tables.
Would you please explain the important arguments of the Sequence object?
START WITH [constant]:
The first value returned by the sequence object. The START value must be a value less than or equal to the maximum and greater than or equal to the minimum value of the sequence object.
INCREMENT BY [constant]:
Value used to increment (or decrement if negative) the value of the sequence object for each call to the NEXT VALUE FOR function. If the increment is a negative value, the sequence object is descending; otherwise, it is ascending.
MINVALUE [constant] | [NO MINVALUE]:
Specifies the bounds for the sequence object. The default minimum value for a new sequence object is the minimum value of the data type of the sequence object.
MAXVALUE [constant] | [NO MAXVALUE]:
Specifies the bounds for the sequence object. The default maximum value for a new sequence object is the maximum value of the data type of the sequence object.
CYCLE | NO CYCLE :
Property that specifies whether the sequence object should restart from the minimum value (or maximum for descending sequence objects) or throw an exception when its minimum or maximum value is exceeded. The default cycle option for new sequence objects is NO CYCLE.
How you assign the sequence object permission to the user?
Ownership of a sequence object can be transferred by using the ALTER AUTHORIZATION statement.
T-SQL Script to find the NEXT Value of the Sequence object?
|
1 |
SELECT NEXT VALUE FOR db_name.seq_name; |
T-SQL script to reset the value of Sequence object.
|
1 |
ALTER SEQUENCE Sequence_Name RESTART WITH 1; |
What is an execution plan? When would you use it? How would you view the execution plan?
An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by the SQL server’s query optimizer for a stored procedure or ad hoc query. Execution plans are very useful for helping a developer understand and analyze the performance characteristics of a query or stored procedure, since the plan is used to execute the query or stored procedure.
In many SQL systems, a textual execution plan can be obtained using a keyword such as EXPLAIN, and visual representations can often be obtained as well. In Microsoft SQL Server, the Query Analyzer has an option called “Show Execution Plan” (located on the Query drop down menu). If this option is turned on, it will display query execution plans in a separate window when a query is run.
What are the NVL and the NVL2 functions in SQL? How do they differ?
- Both the NVL(exp1, exp2) and NVL2(exp1, exp2, exp3) functions check the value exp1 to see if it is null.
- With the NVL(exp1, exp2) function, if exp1 is not null, then the value of exp1 is returned; otherwise, the value of exp2 is returned, but case to the same data type as that of exp1.
- With the NVL2(exp1, exp2, exp3) function, if exp1 is not null, then exp2 is returned; otherwise, the value of exp3 is returned.
Can a stored procedure call itself or recursive stored procedure? How much level SP nesting is possible?
Yes. Because Transact-SQL supports recursion, you can write stored procedures that call themselves. Recursion can be defined as a method of problem solving wherein the solution is arrived at by repetitively applying it to subsets of the problem. A common application of recursive logic is to perform numeric computations that lend themselves to repetitive evaluation by the same processing steps.
Stored procedures are nested when one stored procedure calls another or executes managed code by referencing a CLR routine, type, or aggregate. You can nest stored procedures and managed code references up to 32 levels.
How many columns can be created in a single table?
A normal table, can contain 1024 columns and wide table can contain 30,000 columns per table.
What is OPENXML in SQL Server?
OPENXML can parse the xml data in SQL SERVER very efficiently in SQL Server. OPENXML primarily gives the ability to insert XML data to the relational database, however we can query the data too using OpenXML. We need to specify the path of the xml element using xpath.
What do you know about the SQL Case statement?
The CASE Statement (more commonly known as the CASE Expression) allows you execute parts of a SQL statement conditionally. So, for example, you can use the CASE statement to format the results of the query conditionally – based on the values in another column.
SQL CASE Syntax:
How you can copy unique records from one table to another in SQL Server?
Would you please provide me script to find all column default value information?
