How to get all the Tables with or without an Identity column in Sql Server?

Scripts, Sql Server, , , , , , , ,

This article provides the script to find all the Tables with or without an Identity column

Tables with Identity column

We can write a query like below to get all the Tables with Identity column:

SELECT name 'Table with Identity column'
FROM SYS.Tables
WHERE OBJECTPROPERTY(object_id,'TableHasIdentity') = 1
      AND type = 'U'

Tables without Identity column

We can write a query like below to get all the Tables without Identity column:

SELECT name 'Table without Identity column'
FROM SYS.Tables
WHERE OBJECTPROPERTY(object_id,'TableHasIdentity') = 0
      AND type = 'U'

EXAMPLE

Let us understand the above scripts with an example. To understand this, let us create a demo database SqlHintsDemoDB with Customers table having an identity column and an Orders table without identity column.

CREATE DATABASE SqlHintsDemoDB
GO
USE SqlHintsDemoDB
GO
/*Let us create Customers table with Identity column.*/
CREATE TABLE dbo.Customers (
    CustomerId int IDENTITY (1, 1) 
              PRIMARY KEY CLUSTERED NOT NULL ,
    FirstName Varchar(50),
    LastName Varchar(50))
GO
/*Let us create Orders Table without and Identity column.*/
CREATE TABLE dbo.Orders (
    OrderId int NOT NULL ,
    CustomerId int NOT NULL ,
    CreationDT DATETIME NOT NULL)
GO

Now let us run the queries to get the list of all Tables with or without identity column and verify the result:

Sql List of Tables with or without Identity column

Difference between Len() and Datalength() functions in Sql Server

Sql Server, , , , , , , , , , , , , , , ,

The LEN() and DATALENGTH() functions are there for completely different purpose. But it often confuses many in identifying which one to use when. The aim of this article is to clear all these confusions by presenting a comparative analysis of LEN() and DATALENGTH() Sql Serer functions.

[ALSO READ] Difference Between Sql Server VARCHAR and NVARCHAR Data Type

Below table lists out the comparative analysis between LEN() and DATALENGTH() Sql Server functions:

LEN() DATALENGTH()
DEFINITION
The LEN() Sql Server function returns the number of characters in the specified string expression The DATALENGTH() Sql Server function returns the number of bytes used/required to represent an expression
SYNTAX
LEN ( string_expression ) DATALENGTH ( expression )
INPUT PARAMETER
Input parameter is a string expression, it can be a constant or variable or column of character or binary data Input parameter is an expression of any data type
RETURN TYPE
bigint if expression is of the varchar(max), nvarchar(max) or varbinary(max) data types; otherwise, int bigint if expression is of the varchar(max), nvarchar(max) or varbinary(max) data types; otherwise int
EXAMPLE 
SELECT LEN('Shree') AS 'LEN'

RESULT:
LEN

 
SELECT DATALENGTH('Shree') 
 AS 'DATALENGTH'

RESULT:
DATALENGTH
EXCLUDES TRAILING BLANK SPACES?
YES. LEN() function excludes the trailing blank spaces while calculating the number of characters in the specified string expressionEXAMPLE: 

SELECT LEN('Shree     ')  
 AS 'LEN'

RESULT:
Sql LEN function Trailing Spaces

 NO. DATALENGTH() function includes the trailing blank spaces while calculating the number of bytes used/required to represent an expressionEXAMPLE: 

SELECT DATALENGTH('Shree     ') 
 AS 'DATALENGTH'

RESULT:
Sql DATALENGTH function Trailing Spaces
INPUT PARAMETER IS NULL?
LEN(NULL) is NULL
Sql LEN of NULL		 DATALENGTH(NULL) is NULL
Sql DATALENGTH of NULL
WHEN INPUT IS A UNICODE (i.e. double byte) CHARACTER STRING CONSTANT
For LEN() function it doesn’t matter whether input is a single byte or double byte (i.e. unicode) charcter strings, it always counts the number of characters.
EXAMPLE: 

SELECT LEN(N'Shree') AS
 'LEN of Unicode chars'

RESULT:
Sql LEN of Unicode Character Constant expression

Note: The prefix N for any character string denotes that the following charcter string is of Unicode or double byte type

 As we know each character in the UNICODE character string requires two bytes of storage. So DATALENGTH of UNICODE character string will be equal to the number characters including spaces multiplied by 2.
EXAMPLE: 

SELECT DATALENGTH(N'Shree') AS
 'DATALENGTH of Unicode chars'

RESULT:
Sql DATALENGTH of Unicode Character Constant expression
WHEN INPUT IS A DOUBLE BYTE VARIABLE (EX: NVARCHAR DATATYPE) 
DECLARE @Name NVARCHAR(50) 
 = 'Shree'
SELECT LEN(@Name) AS
 'LEN of Unicode chars'

RESULT:
Sql LEN of Unicode Character Variable

 
DECLARE @Name NVARCHAR(50)
 = 'Shree'
SELECT DATALENGTH(@Name) AS
 'DATALENGTH of Unicode chars'

RESULT:
Sql DATALENGTH of Unicode Character Variable
WHEN INPUT IS A DOUBLE BYTE VARIABLE (EX: NVARCHAR DATATYPE) HAVING VALUE WITH TRAILING SPACES 
DECLARE @Name NVARCHAR(50) 
 = 'Shree     '
SELECT LEN(@Name) AS
 'LEN of Unicode chars'

RESULT:
Sql LEN of Unicode Character Variable trailing spaces

 
DECLARE @Name NVARCHAR(50)
 = 'Shree     '
SELECT DATALENGTH(@Name) AS
 'DATALENGTH of Unicode chars'

RESULT:
Sql DATALENGTH of Unicode Character Variable trailing spaces
SUPPORTS TEXT, NTEXT AND IMAGE DATA TYPES?
NO.

DECLARE @Customer TABLE
(TextColumn TEXT)

INSERT INTO @Customer
VALUES('100')

SELECT LEN(TextColumn) AS 'LEN'
FROM #t1

RESULT:
Msg 8116, Level 16, State 1, Line 7
Argument data type text is invalid for argument 1 of len function.

 YES.

DECLARE @Customer TABLE
(TextColumn TEXT)

INSERT INTO @Customer
VALUES('100')

SELECT DATALENGTH(textcolumn)
  AS 'DATALENGTH'
FROM #t1

RESULT:
DATALENGTH
———–
3

ALSO READ

How to get difference between two dates in Years, Months and days in Sql Server

Sql Server, , , , , , , , , , , , , , , , ,

Many a time we come across a scenario where we need to calculate the difference between two dates in Years, Months and days in Sql Server. In this article we will see how we can achieve this. This article covers the following:

  • Difference between two dates in Years
  • Difference between two dates in Months
  • Difference between two dates in Days
  • Difference between two dates in X years Y Months and Z days (For example: Age in Years, Months and days)

1. Difference between two dates in Years

Approach 1:

We can use DATEDIFF() function like below to get the difference between two dates in Years in Sql Server. DATEDIFF() functions first parameter value can be year or yyyy or yy all will return the same result. Below example shows how we can get difference between two dates in Years

DECLARE @FromDate DATETIME = '2014-12-31', 
        @ToDate DATETIME = '2015-01-01'
SELECT @FromDate 'From Date', @ToDate 'To Date',
 DATEDIFF(YEAR, @FromDate, @ToDate)
   -
 (CASE
   WHEN DATEADD(YEAR, 
           DATEDIFF(YEAR, @FromDate,@ToDate), @FromDate)
       > @ToDate THEN 1 
         ELSE 0 END) 'Date difference in Years'

RESULT:
Difference between dates in years

Approach 2:

You may be thinking why such a complex logic is used to calculate the difference between two dates in years in Apporach 1 instead of using just a single DATEDIFF() function. The reason for using such a complex logic is, DATEDIFF() function returns the number of boundaries crossed by the specified datepart between the specified fromdate and enddate. Basically it calculates the difference between two dates by ignoring all the dateparts smaller than the specified datepart from both the dates. Let us understand this with an example

DECLARE @FromDate DATETIME = '2014-12-31', 
        @ToDate DATETIME = '2015-01-01'
SELECT @FromDate 'From Date', @ToDate 'To Date',
 DATEDIFF(YEAR, @FromDate, @ToDate) 'Date difference in Years'

RESULT:
Date Difference in Years using DateDiff function

From the above result it is clear that even-though @ToDate is the next day from the @FromDate, but still the difference returned is 1 year. So based on the need use the appropriate approach. If we need to calculate age, then we should be using the Approach1.

2. Difference between two dates in Months

We can use DATEDIFF() function like below to get the difference between two dates in Months in Sql Server. DATEDIFF() functions first parameter value can be month or mm or m all will return the same result. Below example shows how we can get difference between two dates in Months

DECLARE @FromDate DATETIME = '2014-12-31', 
        @ToDate DATETIME = '2015-01-01'
SELECT @FromDate 'From Date', @ToDate 'To Date',
   (DATEDIFF(MONTH, @FromDate, @ToDate)
   -
   (CASE WHEN DATEADD(MONTH, 
        DATEDIFF(MONTH, @FromDate, @ToDate), @FromDate)
       > @ToDate
     THEN 1 ELSE 0 END)) 'Date difference in Months'

RESULT:
Difference between dates in months

Approach 2:

You may be thinking why such a complex logic is used to calculate the difference between two dates in months in Apporach 1 instead of using just a single DATEDIFF() function. The reason for using such a complex logic is, DATEDIFF() function returns the number of boundaries crossed by the specified datepart between the specified fromdate and enddate. Basically it calculates the difference between two dates by ignoring all the dateparts smaller than the specified datepart from both the dates. Let us understand this with an example

DECLARE @FromDate DATETIME = '2014-12-31', 
        @ToDate DATETIME = '2015-01-01'
SELECT @FromDate 'From Date', @ToDate 'To Date',
 DATEDIFF(MONTH, @FromDate, @ToDate) 'Date difference in Months'

RESULT:
Date Difference in Months using DateDiff function

From the above result it is clear that even-though the @ToDate is the next day from the @FromDate, but still the difference returned is 1 Month. So based on the need use the appropriate approach.

3. Difference between two dates in Days

We can use DATEDIFF() function like below to get the difference between two dates in Years in Sql Server. DATEDIFF() functions first parameter value can be year or yyyy or yy all will return the same result. Below example shows how we can get difference between two dates in Years

DECLARE @FromDate DATETIME = '2015-07-10 23:59:59.000', 
        @ToDate   DATETIME = '2015-07-11 00:00:00.000'
SELECT @FromDate 'From Date', @ToDate 'To Date',
   (DATEDIFF(DAY, @FromDate, @ToDate)
   -
   (CASE WHEN DATEADD(DAY, 
        DATEDIFF(DAY, @FromDate, @ToDate), @FromDate)
       > @ToDate
     THEN 1 ELSE 0 END)) 'Date difference in Days'

RESULT:
Difference between dates in days

Approach 2:

You may be thinking why such a complex logic is used to calculate the difference between two dates in days in Apporach 1 instead of using just a single DATEDIFF() function. The reason for using such a complex logic is, DATEDIFF() function returns the number of boundaries crossed by the specified datepart between the specified fromdate and enddate. Basically it calculates the difference between two dates by ignoring all the dateparts smaller than the specified datepart from both the dates. Let us understand this with an example

DECLARE @FromDate DATETIME = '2015-07-10 23:59:59.000', 
        @ToDate   DATETIME = '2015-07-11 00:00:00.000'
SELECT @FromDate 'From Date', @ToDate 'To Date',
   DATEDIFF(DAY, @FromDate, @ToDate) 'Date difference in Days'

RESULT:
Date Difference in Days using DateDiff function

From the above result it is clear that even-though the @ToDate is the next second from the @FromDate, but still the difference returned is 1 year. So based on the need use the appropriate approach.

4. Difference between two dates in X years Y Months and Z days (For example: Age in Years, Months and days)

We can use a script like below to get the difference between two dates in Years, Months and days.

DECLARE @FromDate DATETIME = '2010-01-01 23:59:59.000', 
        @ToDate   DATETIME = '2015-01-02 00:00:00.000',
        @Years INT, @Months INT, @Days INT, @tmpFromDate DATETIME
SET @Years = DATEDIFF(YEAR, @FromDate, @ToDate)
 - (CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, @FromDate, @ToDate),
          @FromDate) > @ToDate THEN 1 ELSE 0 END) 

   
SET @tmpFromDate = DATEADD(YEAR, @Years , @FromDate)
SET @Months =  DATEDIFF(MONTH, @tmpFromDate, @ToDate)
 - (CASE WHEN DATEADD(MONTH,DATEDIFF(MONTH, @tmpFromDate, @ToDate),
          @tmpFromDate) > @ToDate THEN 1 ELSE 0 END) 
   
SET @tmpFromDate = DATEADD(MONTH, @Months , @tmpFromDate)
SET @Days =  DATEDIFF(DAY, @tmpFromDate, @ToDate)
 - (CASE WHEN DATEADD(DAY, DATEDIFF(DAY, @tmpFromDate, @ToDate),
          @tmpFromDate) > @ToDate THEN 1 ELSE 0 END) 
   
SELECT @FromDate FromDate, @ToDate ToDate, 
       @Years Years,  @Months Months, @Days Days

RESULT:
Difference between dates in years months and days in sql

We can wrap the above script into a scalar function like below and can be reused for calculating the difference between dates

 
CREATE FUNCTION dbo.GetDateDifference
(
   @FromDate DATETIME, @ToDate DATETIME
)
RETURNS NVARCHAR(100)
AS
BEGIN
    DECLARE @Years INT, @Months INT, @Days INT, @tmpFromDate DATETIME
    SET @Years = DATEDIFF(YEAR, @FromDate, @ToDate)
     - (CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, @FromDate, @ToDate),
              @FromDate) > @ToDate THEN 1 ELSE 0 END) 
   
    SET @tmpFromDate = DATEADD(YEAR, @Years , @FromDate)
    SET @Months =  DATEDIFF(MONTH, @tmpFromDate, @ToDate)
     - (CASE WHEN DATEADD(MONTH,DATEDIFF(MONTH, @tmpFromDate, @ToDate),
              @tmpFromDate) > @ToDate THEN 1 ELSE 0 END) 
   
    SET @tmpFromDate = DATEADD(MONTH, @Months , @tmpFromDate)
    SET @Days =  DATEDIFF(DAY, @tmpFromDate, @ToDate)
     - (CASE WHEN DATEADD(DAY, DATEDIFF(DAY, @tmpFromDate, @ToDate),
              @tmpFromDate) > @ToDate THEN 1 ELSE 0 END) 
  
  
    RETURN 'Years: ' + CAST(@Years AS VARCHAR(4)) +
            ' Months: ' + CAST(@Months AS VARCHAR(2)) +
            ' Days: ' + CAST(@Days AS VARCHAR(2)) 
END
GO

Below example shows how we can use the above function in the select statement

DECLARE @FromDate DATETIME = '2010-01-01 23:59:59.000', 
        @ToDate   DATETIME = '2015-01-02 00:00:00.000'
SELECT @FromDate FromDate, @ToDate ToDate, 
       dbo.GetDateDifference(@FromDate, @ToDate) 
           AS 'Difference between dates'

RESULT:
Difference between dates in years months and days by scalar function in sql

We can as-well create a multi-statement table valued function like below to get difference between two dates in years, months and days

CREATE FUNCTION dbo.GetDateDifferenceInYearsMonthsDays
(
    @FromDate DATETIME, @ToDate DATETIME
)
RETURNS
 @DateDifference TABLE (
 YEAR INT,  MONTH INT, DAYS INT)
AS
BEGIN
    DECLARE @Years INT, @Months INT, @Days INT, @tmpFromDate DATETIME
    SET @Years = DATEDIFF(YEAR, @FromDate, @ToDate)
     - (CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, @FromDate, @ToDate),
              @FromDate) > @ToDate THEN 1 ELSE 0 END) 
   
    SET @tmpFromDate = DATEADD(YEAR, @Years , @FromDate)
    SET @Months =  DATEDIFF(MONTH, @tmpFromDate, @ToDate)
     - (CASE WHEN DATEADD(MONTH,DATEDIFF(MONTH, @tmpFromDate, @ToDate),
              @tmpFromDate) > @ToDate THEN 1 ELSE 0 END) 
   
    SET @tmpFromDate = DATEADD(MONTH, @Months , @tmpFromDate)
    SET @Days =  DATEDIFF(DAY, @tmpFromDate, @ToDate)
     - (CASE WHEN DATEADD(DAY, DATEDIFF(DAY, @tmpFromDate, @ToDate),
              @tmpFromDate) > @ToDate THEN 1 ELSE 0 END) 
   
    INSERT INTO @DateDifference
    VALUES(@Years, @Months, @Days)
   
    RETURN
END

Below example shows how we can use the above function in the select statement

DECLARE @FromDate DATETIME = '2010-01-01 23:59:59.000', 
        @ToDate   DATETIME = '2015-01-02 00:00:00.000'
SELECT * 
FROM dbo.GetDateDifferenceInYearsMonthsDays(@FromDate,@ToDate)
GO

RESULT:
Difference between dates in years months and days by table valued function in sql