SQL SERVER – How To Prefix zeros to an Integer

There are some cases where we have to add zeros to an integer as prefix. This can be achieved  by the Right() string function.  Let us take a table – Employee, which is having an Identity column, EmpId with values 1,2,3 and so on.

Create Table EmpDetails(EmpId int identity(1,1), EmpName varchar(25), Salary Money)

INSERT INTO EmpDetails
SELECT 'AAA', 500000
UNION ALL
SELECT 'BBB', 400000
UNION ALL
SELECT 'CCC', 900000

Now on checking the data,

SELECT EMPID,EMPNAME ,SALARY FROM Empdetails

 

Now, In order to add zeros as prefix to the Empid,we will use  Right() string function as shown below.

SELECT RIGHT('00000'+CONVERT(VARCHAR,EMPID),5) EmpId, EMPNAME ,SALARY  FROM EmpDetails

Here we have converted EmpId to VARCHAR Data type,  concatenated zeros to the EmpId and applied Right() string function to the result set.

The Output is,

SQL SERVER – Information_Schema

SQL Server’s Information_Schema view Provides a detail Information about the meta data. Every database contains the Information schema View. This View is available under System views, which contains metadata for all data objects that are stored in the database.

Let us see all these views one by one.

1.  CHECK_CONSTRAINTS : Information about all the CHECK constraints in the database.

2.  COLUMN_DOMAIN_USAGE : Information about all columns that has an alias data type in the database.

3.  COLUMN_PRIVILEGES : Information about all columns that has a privilege that is either granted to or granted by the current user in the current database.

4.  COLUMNS : Information about all the columns of all the tables that can be accessed by the current user in the current database.

5.  CONSTRAINT_COLUMN_USAGE : Information about all the constraints that are defined on all columns in the current database.

6.  CONSTRAINT_TABLE_USAGE : Information about all the constraints that are defined on all tables in the current database.

7.  DOMAIN_CONSTRAINTS : Information about all the alias data types in the current database that has a rule bound to it by using sp_bindrule

8.  DOMAINS : Information about all the alias data types in the current database

9.  KEY_COLUMN_USAGE : Information about all columns that is constrained as a key in the current database. 

10. PARAMETERS : Information about all parameters of a user-defined function or stored procedure that can be accessed by the current user in the current database.

11. REFERENTIAL_CONSTRAINTS : Information about all the FOREIGN KEY constraint in the current database.

12. ROUTINES : Information about all the stored procedures and functions that can be accessed by the current user in the current database.

13. ROUTINE_COLUMNS : Information about all the columns that are returned by the table-valued functions that can be accessed by the current user in the current database.

14. SCHEMATA : Information about all the schema in the current database.

15. TABLE_CONSTRAINTS : Information about all the table constraints in the current database. 

16. TABLE_PRIVILEGES : Information about all the privileges on each table that is granted to or granted by the current user in the current database.

17. TABLES : Information about all the tables in the current database.

18. VIEW_COLUMN_USAGE : Returns all column details in the current database that is used in all view definitions.

19. VIEW_TABLE_USAGE : Returns all table details in the current database that is used in all view definitions.

20. VIEWS : Information about all the views that can be accessed by the current user in the current database.

SQL SERVER – String or Binary Data would be Truncated

In SQL Server,While inserting the data in to a table using the insert statement, Occasionally we will see the following error message:

“String or Binary Data would be Truncated”

We will get this error, when the length of the value entered into a CHAR / VARCHAR / NCHAR / NVARCHAR column is longer than the maximum length of the column.

Let us see this through an example. First we will create  a table Product :

Create Table Product (ProdId INT,ProdName VARCHAR(16), ProdDesc VARCHAR(25))

Now inserting records,

Insert Into Product Values (1,'Adjustable Race' ,'Adjustable Race' )                                                                      Go                                                                         Insert Into Product Values (2,'Chain Ring Bolts' ,'Chain Ring Bolts' )                                                                      Go                                                                        Insert Into Product Values (3,'Chain Ring Nuts','Chain Ring Nuts')

On selecting the Records from the Table,

Select * From Product

Now we will insert one more record whose Product name length is longer than the maximum length of the column

Insert Into Product Values (4,'Headset Ball Bearings', 'Headset Ball Bearings')

whenever, we try to insert this record , it will throw an error specifying that “String or Binary Data would be Truncated” as the length of the Product name column is greater than the maximum length of the column.

SQL SERVER – Deterministic and Non Deterministic Functions

A function is said to be Deterministic when it returns the same result any time with the same set of input values.

A function is said to be Non Deterministic when it returns the different results each time with the same set of input values.

Let us take an user defined function which takes two integers values as input parameters and returns their sum. In this case, if the input parameters are 2 and 3 then each time this function will return the same result 5 which is deterministic.

To see this through an example, Let us first create a function ADD_NUMBERS which has two input parameters Num1 and Num2 and returns their sum.

CREATE FUNCTION ADD_NUMBERS(Num1 INT,Num2 INT) RETURNS INT
AS RETURN (NUM1 + NUM2)

Now, whenever we execute this function with the same input values 2 & 3 it returns 5.

Similarly the DATEDIFF system function always returns the same result for any given set of argument values for its three parameters.

As shown in the below query, DATEDIFF function always returns 5 for the same set of input values.

All of the Aggregate and String Built-in functions are deterministic except the CHARINDEX and PATINDEX.

Again, GETDATE() is the best example for  Non Deterministic Function because each time it returns different values with the same input parameter. As shown in the below screen shot, GETDATE() function returns different time values.

All of the configuration, cursor, meta data, security, and system statistical functions are nondeterministic.Some more examples for Non deterministic functions are : PATINDEX,CHARINDEX, CURRENT_TIMESTAMP,@@ERROR.

SQL SERVER – Date Formats using CONVERT() Function

In the Previous article, we learned about the Convert() function,  which can be used to change the date format in what ever format we like. Now, In this article we will see How to get various date formats  using Convert() function.

One of the Most frequently asked questions in any of the Interview or in any of the SQL Server forums is - How to extract only Date value from DATETIME column or How to change the DATETIME value in to a specified format. The answer to all these queries is by using the Convert() function. The CONVERT function's third optional parameter  ‘style’ plays a vital role in formatting the value while  converting the  a datetime value to a VARCHAR.

Let us see various Standard Date Formats that are available in SQL Server.

SQL statement	Output
SELECT CONVERT(VARCHAR(8), GETDATE(), 1)  'MM/DD/YY'
SELECT CONVERT (VARCHAR(8), GETDATE(), 2)  'YY.MM.DD'
SELECT CONVERT (VARCHAR(8), GETDATE(), 3) 'DD/MM/YY'
SELECT CONVERT (VARCHAR(8), GETDATE(), 4) 'DD.MM.YY'
SELECT CONVERT (VARCHAR(8), GETDATE(), 5) 'DD-MM-YY'
SELECT CONVERT (VARCHAR(9), GETDATE(), 6)  'DD MON YY'
SELECT CONVERT (VARCHAR(10), GETDATE(), 7)  'Mon DD, YY'
SELECT CONVERT (VARCHAR(8), GETDATE(), 8)  'HH:MM:SS'
SELECT CONVERT (VARCHAR(26), GETDATE(), 9)       'Mon DD YYYY HH:MM:SS:MMMAM/PM'
SELECT CONVERT (VARCHAR(8),  GETDATE(), 10)    'MM-DD-YY'
SELECT CONVERT (VARCHAR(8),  GETDATE(), 11) 'YY/MM/DD'
SELECT CONVERT (VARCHAR(6), GETDATE(), 12)  'YYMMDD'
SELECT CONVERT (VARCHAR(24), GETDATE(), 13)       'DD Mon YYYY HH:MM:SS:MMM'
SELECT CONVERT (VARCHAR(12), GETDATE(), 14)  'HH:MM:SS:MMM'
SELECT CONVERT (VARCHAR(19), GETDATE(), 20)  'YYYY-MM-DD HH:MM:SS'
SELECT CONVERT (VARCHAR(35), GETDATE(), 21)   'YYYY-MM-DD HH:MM:SS.MMM'
SELECT CONVERT (VARCHAR(20), GETDATE(), 22)  'MM/DD/YY HH:MM:SS PM(AM)'
SELECT CONVERT (VARCHAR(10), GETDATE(), 23)  'YYYY-MM-DD'
SELECT CONVERT (VARCHAR(10), GETDATE(), 24)  'HH:MM:SS'
SELECT CONVERT (VARCHAR(10), GETDATE(), 25) 'YYYY-MM-DD'
SELECT CONVERT (VARCHAR(30), GETDATE(),100)  'Mon DD YYYY HH:MMAM/PM'
SELECT CONVERT (VARCHAR(10), GETDATE(), 101)  'MM/DD/YYYY'
SELECT CONVERT (VARCHAR(10), GETDATE(), 102)  'YYYY.MM.DD'
SELECT CONVERT (VARCHAR(10), GETDATE(), 103)  'DD/MM/YYYY'
SELECT CONVERT (VARCHAR(10), GETDATE(), 104) 'DD.MM.YYYY'
SELECT CONVERT (VARCHAR(10), GETDATE(), 105) 'DD-MM-YYYY'
SELECT CONVERT (VARCHAR(11), GETDATE(), 106) 'DD Mon YYYY'
SELECT CONVERT (VARCHAR(12), GETDATE(), 107)  'Mon DD, YYYY'
SELECT CONVERT (VARCHAR(8), GETDATE(), 108)  'HH:MM:SS'
SELECT CONVERT (VARCHAR(11), GETDATE(), 109) 'Mon DD YYYY'
SELECT CONVERT (VARCHAR(10), GETDATE(), 110)  'MM-DD-YYYY'
SELECT CONVERT (VARCHAR(10), GETDATE(), 111) 'YYYY/MM/DD'
SELECT CONVERT (VARCHAR(10), GETDATE(), 112) 'YYYYMMDD'
SELECT CONVERT (VARCHAR(24), GETDATE(), 113)  'DD Mon YYYY HH:MM:SS:MMM'
SELECT CONVERT (VARCHAR(12), GETDATE(), 114) 'HH:MM:SS:MMM'
SELECT CONVERT (VARCHAR(19), GETDATE(), 120) 'YYYY-MM-DD HH:MM:SS'
SELECT CONVERT (VARCHAR(23), GETDATE(), 121) 'YYYY-MM-DD HH:MM:SS.MMM'
SELECT CONVERT (VARCHAR(24), GETDATE(), 126) 'YYYY-MM-DDTHH:MM:SS.MMM'
SELECT CONVERT (VARCHAR(110), GETDATE(), 127)   'YYYY-MM-DDTHH:MM:SS.MMM'
SELECT CONVERT (VARCHAR(26), GETDATE(), 130)  'DD Mon YYYY HH:MM:SS:MMMAM/PM'
SELECT CONVERT (VARCHAR(25), GETDATE(), 131)  'DD/MM/YYYY HH:MM:SS:MMMAM/PM'