Tag Archives: Sql Server

Difference between TINYINT and SMALLINT data type in Sql Server

Both TINYINT and SMALLINT are exact numeric data types, used for storing integer value. Below table lists out the major difference between TINYINT and SMALLINT Data Types.

[ALSO READ] TINYINT Vs INT

TINYINT

SMALLINT

Storage Size 1 Byte 2 Bytes
Minimum Value 0 -32,768 (-2^15)
Maximum Value 255 32,767 (2^15-1)
Usage Example
DECLARE @i TINYINT
SET @i = 150
PRINT @i

RESULT:
150

DECLARE @i SMALLINT
SET @i = 150
PRINT @i

RESULT:
150

Example of Storage Size used by the variable to store the value
DECLARE @i TINYINT
SET @i = 150
PRINT DATALENGTH( @i)

RESULT:
1

DECLARE @i SMALLINT
SET @i = 150
PRINT DATALENGTH( @i)

RESULT:
2

Example of TINYINT out of range value
DECLARE @i TINYINT
SET @i = 260
PRINT @i

RESULT:

Msg 220, Level 16, State 2, Line 2
Arithmetic overflow error for data type tinyint, value = 260.

DECLARE @i SMALLINT
SET @i = 260
PRINT @i

RESULT:
260

Try to store Negative value
DECLARE @i TINYINT
SET @i = -150
PRINT @i

RESULT:

Msg 220, Level 16, State 2, Line 2
Arithmetic overflow error for data type tinyint, value = -150.

DECLARE @i SMALLINT
SET @i = -150
PRINT @i

RESULT:
-150

Example of both TINYINT and SMALLINT out of range value
DECLARE @i TINYINT
SET @i = 32768
PRINT @i

RESULT:

Msg 220, Level 16, State 2, Line 2
Arithmetic overflow error for data type tinyint, value = 32768.

DECLARE @i SMALLINT
SET @i = 32768
PRINT @i

RESULT:

Msg 220, Level 16, State 1, Line 2
Arithmetic overflow error for data type smallint, value = 32768.

[ALSO READ] SMALLINT Vs INT

Selecting the correct data type while creating a table is very critical. In-correct selection of the data type will result in performance and storage issues over the time as the data grows. As in-correct selection of data type results requiring more storage space to store and no. of records stored in each data page will be less. And on top if index is created on such columns, it not only takes the extra space in storing the value in a row in the data page but also requires extra space in the index. Less the no. of records stored in the data page, then to serve the queries Sql Server needs to load more no. of data pages to the memory. For example: for table column, which stores state_id, choosing an INT data type instead of TINYINT or SMALLINT column is in-efficient as the number of states in a country in worst case scenario too never exceeds a three-digit number. So, for state_id column if we choose INT data type then it will always take 4 bytes for storing it irrespective of the value stored in it. Whereas TINYINT would have taken 1 byte for storing the same value and SMALLINT would have taken 2 bytes. So, it is very crucial to select the correct data type while creating table. Hope the above differences will help you in selecting the correct data type while creating the table.

ALSO READ

Difference between TINYINT and INT data type in Sql Server

Both TINYINT and INT are exact numeric data types, used for storing integer data. Below table lists out the major difference between TINYINT and INT Data Types.

[ALSO READ] TINYINT Vs SMALLINT

TINYINT

INT

Storage Size 1 byte 4 bytes
Minimum Value 0 -2,147,483,648 (-2^31)
Maximum Value 255 2,147,483,647 (2^31-1)
Usage Example
DECLARE @i TINYINT
SET @i = 150
PRINT @i

RESULT:
150

DECLARE @i INT
SET @i = 150
PRINT @i

RESULT:
150

Example of Storage Size used by the variable to store the value
DECLARE @i TINYINT
SET @i = 150
PRINT DATALENGTH( @i)

RESULT:
1

DECLARE @i INT
SET @i = 150
PRINT DATALENGTH( @i)

RESULT:
4

Example of TINYINT out of range value
DECLARE @i TINYINT
SET @i = 260
PRINT @i

RESULT:

Msg 220, Level 16, State 2, Line 2
Arithmetic overflow error for data type tinyint, value = 260.

DECLARE @i INT
SET @i = 260
PRINT @i

RESULT:
260

Try to store Negative value
DECLARE @i TINYINT
SET @i = -150
PRINT @i

RESULT:

Msg 220, Level 16, State 2, Line 2
Arithmetic overflow error for data type tinyint, value = -150.

DECLARE @i INT
SET @i = -150
PRINT @i

RESULT:
-150

Example of both TINYINT and INT out of range value
DECLARE @i TINYINT
SET @i = 2147483649
PRINT @i

RESULT:

Msg 8115, Level 16, State 2, Line 2
Arithmetic overflow error converting expression to data type tinyint.

DECLARE @i INT
SET @i = 2147483649
PRINT @i

RESULT:

Msg 8115, Level 16, State 2, Line 2
Arithmetic overflow error converting expression to data type int.

[ALSO READ] SMALLINT Vs INT

Selecting the correct data type while creating a table is very critical. In-correct selection of the data type will result in performance and storage issues over the time as the data grows. As in-correct selection of data type results requiring more storage space to store and no. of records stored in each data page will be less. And on top if index is created on such columns, it not only takes the extra space in storing the value in a row in the data page but also requires extra space in the index. Less the no. of records stored in the data page, then to serve the queries Sql Server needs to load more no. of data pages to the memory. For example: for table column, which stores state_id, choosing an INT data type instead of TINYINT or SMALLINT column is in-efficient as the number of states in a country in worst case scenario too never exceeds a three-digit number. So, for state_id column if we choose INT data type then it will always take 4 bytes for storing it irrespective of the value stored in it. Whereas TINYINT would have taken 1 byte for storing the same value and SMALLINT would have taken 2 bytes. So, it is very crucial to select the correct data type while creating table. Hope the above differences will help you in selecting the correct data type while creating the table.

ALSO READ

How to Split a String in Sql Server without using a function

It is an usual practice to create a function to split the delimited sting in Sql Server, like the ones explained in the article Split delimited String. But if we are in a situation where you don’t have a permission to create a function, then what is the alternative to split the delimited string? This article explains how we can split a delimited string in Sql Server without needing to create a function.

Let us execute the following script to create the Player table with sample data as shown in the below image.

Table with delimited string

CREATE TABLE Player
(
	Team VARCHAR(50),
	Players VARCHAR(800)
)
GO
INSERT INTO Player(Team, Players)
VALUES  ('India','Sachin Tendulkar,Shewag,Javagal Srinath'),
		('Australia','Ricky Ponting,Michale Clarke'),
		('South Africa','AB Deviliers')
GO

Now, we can write a query like below to split the comma delimited players name without using the user defined function.

SELECT Team, Members.Member.value('.','VARCHAR(8000)') Player
FROM 
(--Convert delimited string to XML
 SELECT Team, CAST('<Players><Player>' 
        + REPLACE(Players, ',' , '</Player><Player>') 
	+ '</Player></Players>' AS XML) AS tempPlayer 
 FROM Player ) AS tempPlayer
 CROSS APPLY tempPlayer.nodes('/Players/Player') Members(Member)

RESULT:
Split string without function

[ALSO READ]
STRING_SPLIT function in Sql Server 2016
How to Split comma or any other character delimited string into a Table in Sql Server

CREATE OR ALTER DDL Statement in Sql Server 2016 SP1

CREATE OR ALTER is one of the new Transact-Sql DDL statement supported in Sql Server 2016 SP1. This statement can be used while creating or altering the Stored Procedures, Functions, Triggers and Views. Basically, if we are using this statement with a module like Stored Procedure, then if the Stored Procedure doesn’t exists it will create it and if it already exists then it will alter the the Stored Procedure. The good thing with this statement is, if the module already existing it alters the module definition but preserves the existing permissions on it.

Let us understand this feature with an example. Execute the following script to create a stored procedure WelcomeMessage and then verify the SP content by executing the SP_HELPTEXT statement.

CREATE OR ALTER PROCEDURE WelcomeMessage
AS
    SELECT 'Welcome to Sqlhints'
GO
SP_HELPTEXT WelcomeMessage

RESULT:
Sql Server 2016 SP 1CREATE OR ALTER Statement Example 1

From the above result we can see that the stored procedure WelcomeMessage is created.

Now execute the below statement, which is same as the previous statement and only difference is the change in the WelcomeMessage Stored Procedure definition:

CREATE OR ALTER PROCEDURE WelcomeMessage
AS
    SELECT 'Welcome to WWW.Sqlhints.COM'
GO
SP_HELPTEXT WelcomeMessage

RESULT:
Sql Server 2016 SP 1CREATE OR ALTER Statement Example 2

From the result we can see that this time as the WelcomeMessage stored procedure already exists. The CREATE OR ALTER statement modified the stored procedure definition. So CREATE OR ALTER statement creates the module if it doesn’t exists already, if the module already exists it will alter the module.

In the prior versions of Sql Server to achieve this we would have checked the existence of the module first, if it exists we drop the module and then create the module as shown in the below script. The problem with this approach is the re-creation of the Stored procedure requires us to re-grant the permission as the previous permissions assigned to the object are lost when object is dropped. Where as in case of CREATE OR ALTER statement, for an existing object it uses the ALTER statement, in that way the previous permissions granted to the object remains intact and no need to re-grant.

IF EXISTS(SELECT 1 FROM sys.procedures 
          WHERE Name = 'WelcomeMessage')
BEGIN
    DROP PROCEDURE dbo.WelcomeMessage
END
GO
CREATE PROCEDURE WelcomeMessage
AS
    SELECT 'Welcome to WWW.Sqlhints.COM'
GO

OUTER APPLY in Sql Server

OUTER APPLY operator in Sql Server returns all rows from the LEFT table expression of the OUTER APPLY operator irrespective of whether it produces the corresponding result in the RIGHT table expression or not. The RIGHT table expression columns value will be NULL in the final result for those rows in the LEFT table expression that don’t produce the result from the RIGHT table expression. So from the result perspective we can say that the OUTER APPLY is similar to the classic LEFT OUTER JOIN

To understand OUTER APPLY in Sql Server with extensive list of examples, let us create the demo tables with sample data as shown in the following image by executing the following script:

apply-operator-demonstration-tables

CREATE DATABASE SqlHintsOUTERAPPLYDemo
GO
USE SqlHintsOUTERAPPLYDemo
GO
--Create Customers Table and Insert records
CREATE TABLE Customers 
( CustomerId INT, Name VARCHAR(50) )
GO
INSERT INTO Customers(CustomerId, Name) 
VALUES(1,'Shree'), (2,'Kalpana'), (3,'Basavaraj')
GO
--Create Orders Table and Insert records into it
CREATE TABLE Orders
(OrderId INT, CustomerId INT,Amount MONEY, OrderDate DATETIME)
GO
INSERT INTO Orders(OrderId, CustomerId,Amount,OrderDate) 
VALUES(100,1,100.0,Getdate()-30), (101,1,200.0,Getdate()-20),
      (103,1,300.0,Getdate()-10), (104,2,150.0,Getdate())	
GO

Create a Table Valued Function

Now execute the following script to create an inline Table valued function fnGetLastTwoCustomerOrders, which returns order details of the last 2 orders corresponding to the input customer id.

CREATE FUNCTION dbo.fnGetLastTwoCustomerOrders
( @CustomerId AS INT )  
RETURNS TABLE 
AS 
RETURN ( 
		SELECT TOP 2 * 
		FROM Orders O 
		WHERE O.CustomerId = @CustomerId 
		ORDER BY OrderDate DESC
	)

Let us verify this functions result for the customer id = 1

--Return last two order details for the customer id = 1
SELECT * FROM dbo.fnGetLastTwoCustomerOrders(1) O
GO

RESULT:
apply-operator-table-valued-function-result

OUTER APPLY Examples

Example 1: Demonstrates how we can re-write a very basic LEFT OUTER JOIN query by OUTER APPLY operator

OUTER APPLY is not an alternative for LEFT OUTER JOIN, instead each of them have their own pros and cons. The general rule of thumb is you may like to use the OUTER JOIN if you want to evaluate/execute the RIGHT side table expression or Table Valued function for each row in the LEFT side table expression. Just to demonstrates how we can use OUTER APPLY operator with a very basic example, here in this example will re-write a very basic LEFT OUTER JOIN query by OUTER APPLY.

We can write a query like below by using LEFT OUTER JOIN to get all Customers table rows irrespective of whether a corresponding record exists in the Orders table or not. For the records in the Customers table which don’t have matching records in the Orders table, the Orders table columns in the result will have NULL values

SELECT *
FROM Customers C
        LEFT OUTER JOIN Orders O
	   ON C.CustomerId = O.CustomerId

RESULT
left-outer-join-query

We can re-write the above LEFT OUTER JOIN query using OUTER APPLY as shown below and still get the same result

SELECT *
FROM Customers C
  OUTER APPLY (SELECT * 
               FROM Orders O
     	       WHERE O.CustomerId = C.CustomerId) ORD

RESULT
outer-apply-left-outer-join-equivalent

From the above results we can see that the re-written OUTER APPLY query is returning the same result as that of the LEFT OUTER JOIN query.

[ALSO READ] CROSS APPLY in Sql Server

Example 2: OUTER APPLY operator between a Table and user defined Table Valued Function

We can write a query like below using OUTER APPLY operator to get details of all the customers with their last two order details if it is present. This query basically evaluates the function fnGetLastTwoCustomerOrders for each row in the Customer table by taking CustomerId as the input.

SELECT *
FROM dbo.Customers C
      OUTER APPLY 
        dbo.fnGetLastTwoCustomerOrders(C.CustomerId) O

RESULT:
sql-server-outer-apply-example-1

From the result we can see that for the customer with customer id =1, we have two orders in the result even though this customer has two orders because the function fnGetLastTwoCustomerOrders returns at max the last two orders for any given CustomerId. And for CustomerId =2, we see only one order detail in the result as this customer has placed only one order till now and for the Customer with CustomerId = 3 in the result we can see only the Customer details and all the Order details columns value for this customer is NULL because this customer is yet to place any order.

[ALSO]

[ALSO READ] Sql Server APPLY operator

Example 3: OUTER APPLY operator between a Table and Table Expression

In the example 2 the RIGHT side of the OUTER APPLY operator was User Defined Function, this example demonstrates how we can have a Table Expression on the right side of this operator.

The below script can also achieve the same result as the example 2, but here we have not used the Table Valued Function fnGetLastTwoCustomerOrders instead we have used the derived table.

SELECT *
FROM dbo.Customers C
	OUTER APPLY (SELECT TOP 2 * 
	  	     FROM Orders O 
		     WHERE O.CustomerId = C.CustomerId 
		     ORDER BY OrderDate DESC) ORD

RESULT:
sql-server-outer-apply-example-2