How to find all the IDENTITY columns in a database?

Here is the easiest way to list all the IDENTITY Columns of any database:

SELECTOBJECT_NAME([object_id]) as TableName,

name as ColumnName

FROM [DatabaseName].sys.columns

WHERE is_identity = 1

Function to Convert Decimal Number into Binary, Ternary, and Octal

In this article I am sharing user defined function to convert a decimal number into Binary, Ternary, and Octalequivalent.

IF OBJECT_ID(N'dbo.udfGetNumbers', N'TF') IS NOT NULL
DROP FUNCTION dbo.udfGetNumbers
GO


CREATE FUNCTION dbo.udfGetNumbers
(@base [int], @lenght [int])
RETURNS @NumbersBaseN TABLE
(
  decNum [int] PRIMARY KEY NOT NULL,
  NumBaseN [varchar](50) NOT NULL
)
AS
BEGIN
  WITH tblBase AS
  (
    SELECT CAST(0 AS VARCHAR(50)) AS baseNum
    UNION ALL
    SELECT CAST((baseNum + 1) AS VARCHAR(50))
    FROM tblBase WHERE baseNum < @base-1
  ),
  numbers AS
  (
    SELECT CAST(baseNum AS VARCHAR(50)) AS num
    FROM tblBase
    UNION ALL
    SELECT CAST((t2.baseNum + num) AS VARCHAR(50))
    FROM numbers CROSS JOIN tblBase t2
    WHERE LEN(NUM) < @lenght
  )

  INSERT INTO @NumbersBaseN
  SELECT ROW_NUMBER() OVER (ORDER BY NUM) -1 AS rowID, NUM
  FROM numbers WHERE LEN(NUM) > @lenght - 1

  OPTION (MAXRECURSION 0);
  RETURN
END
GO


-- Unit Test --
-- Example with decimal, binary, ternary and octal


SELECT
   U1.decNum   AS Base10,
   U1.NumBaseN AS Base2,
   U2.NumBaseN AS Base3,
   U3.NumBaseN AS Base8
FROM dbo.udfGetNumbers(2, 10) U1
JOIN dbo.udfGetNumbers(3, 7) U2
  ON u1.decNum = u2.decNum
JOIN dbo.udfGetNumbers(8, 4) U3
  ON u2.decNum = u3.decNum


Here is the output:

Fun with TRANSACTION

What will be output of below T-SQL code:

CREATE TABLE MyTable
(
   MyId [INT] IDENTITY (1,1),
   MyCity [NVARCHAR](50)
)


BEGIN TRANSACTION OuterTran
  INSERT INTO MyTable VALUES ('Boston')
  BEGIN TRANSACTION InnerTran
    INSERT INTO MyTable VALUES ('London')
    ROLLBACK WORK
    IF (@@TRANCOUNT = 0)
    BEGIN
      PRINT 'All transactions were rolled back'
    END
    ELSE
    BEGIN
      PRINT 'Outer transaction is rolling back...'
      ROLLBACK WORK
    END
    DROP TABLE MyTable

Here are the options:
1.   All transactions were rolled back
2.   Outer transaction is rolling back...
3.   ERROR: Incorrect syntax near 'WORK'.

How to get SQL Server Restore history using T-SQL?

How to find SQL Server databases Restore History?
How to find Last Restore Date od SQL Server database?

Few developers asked me how to find the last time a database was restored using T-SQL. I have formed the following query using MSDB..RestoreHistory table.

SELECT *
FROM MSDB..RestoreHistory WITH (nolock)
WHERE destination_database_name = 'DatabaseName'
ORDER BY restore_date DESC

What is lock escalation?

Lock escalation is the process of converting a lot of low level locks (For example row locks, and page locks) into higher level locks (e.g. table locks). Every lock is a memory structure too many locks would mean, more memory being occupied by locks. To prevent this from happening, SQL Server escalates the many fine-grain locks to fewer coarse-grain locks. Lock escalation threshold was definable in SQL Server 6.5, but from SQL Server 7.0 onwards it's dynamically managed by SQL Server.

In the hierarchy of the objects in a SQL Server instance, at the top level we have Database, followed by Schema, Tables, Table Partitions, Pages and then finally the individual Rows. If you acquire a lock at higher level, it can cover more resources there by you consume fewer lock resources (each lock structure takes approximately 100 bytes) and the locking overhead but this comes at a price of lower concurrency. So for example, if you want to select all the rows of a table, if you acquire a lock at table level, you will not need to lock individual rows or pages but then it will block any concurrent update transaction. Similarly, if you lock individual rows, you will get higher concurrency but then you will incur the overhead of acquiring/releasing locks on each row and lot more locking resources depending upon the isolation level of your transaction, as you may need to hold the locks on all the rows till the end of transaction.

Depending upon the estimates during query compilation, the SQL Server recommends the locking granularity (i.e. row, page or table) appropriately and during query execution, depending on the concurrent work load, the appropriate locking granularity is applied. User can override the locking granularity option explicitly by providing locking hints and/or by executing sp_indexoption stored procedure. While locking granularity is chosen at the start of query execution but during the execution, the SQL Server may choose to escalate the lock to lower level of granularity depending on the number of locks acquired and the availability of memory at run time.

T-SQL Challenge

What will be the output of below T-SQL code:

CREATE TABLE #TestDate
(
   [ID] int IDENTITY(1,1)
   ,[FullDate] datetime DEFAULT (GETDATE()),
)
GO
INSERT INTO #TestDate VALUES
('01/07/2010'),('2010/07/01'),('07/01/2010')
GO
SELECT COUNT([FullDate]) FROM #TestDate
WHERE CAST([FullDate] as int) = 40358
GO
DROP TABLE #TestDate
GO

Answer this without cheating (without executing the Query). Here are the options:

A.   1
B.   2
C.   3
D.   Error
F.   None of the above