How to get primary key name from table name in SQL.

We all know that how to check primary key of table in SQL server using graphically(using object explorer in SQL) but sometime you need to know how to find primary key of specific table using SQL query. There are many ways to find it out, some of it mention below:

Ex 1:-

SELECT column_name FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE

WHERE OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') = 1

AND table_name = 'TableName'

Ex 2:-

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc

JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu

ON tc.CONSTRAINT_NAME = ccu.Constraint_name

WHERE tc.CONSTRAINT_TYPE = 'Primry Key'

Ex 3(Using system stored procedure):-

EXEC sp_pkeys ‘TableName’

What is verbatim string literal in C#?

Verbatim strin is any regular string which is starts from @ character.

For example: @”Hello World”

Verbatim string used when you want to print multiline text in C#. When we used verbatim literal then any escape sequence character in string neglect by C# compiler.

Example:

       1)   string strText = “Hello \t World”      //OP is: Hello                    World

In this example C# complier consider \t as escape sequence so which is used as horizontal tab.

       2)   string strText1 = @“Hello \t World”  //OP is: Hello \t World

In this example C# complier neglect escape sequence as we used verbatim literal(@) so what we write after verbatim literal its print as it is.

CHARINDEX() function in SQL.

CHARINDEX is a SQL function that searches an expression (char or string) in given expression and return start position if matches found. If matches not found it return 0.

Syntax:

CHARINDEX(Expressio1, Expression2, [Start_Location])

Where,

Expression1: String to be look for in expression2

Start_Location: It means from where to start looking for expression1 in expression2.

NOTE:

It returns bigint if expression2 is of verchar(max), nvarchar(max), varbinary(max) datatypes, else it returns int.

Example:

Using SET ANSI_NULLS in SQL Server

The ANSI_NULLS used to determine that how SQL Server handles the comparison operations with NULL values using = or <> operator.

Syntax: SET ANSI_NULLS ON/OFF

1) SET ANSI_NULLS ON:

This option always return FALSE when comparison with NULL using = or <> operator. So to do the comparison with NULL values we need to use IS NULL and IS NOT NULL.

2) SET ANSI_NULLS OFF:

Using this option, we can compare NULL using = and <> and will work as usual.

NOTE: The default option is ON. 

Examples:

How to transfer existing table data into new table in SQL server?

We can easily transfer/copy one SQL table data into new SQL table without creating table definition using SELECT-INTO-FROM query format.

Suppose I have one SQL table named Employee with lots of data, now if I want to replicate Employee table into new table named Employee_New. So using below query we can easily achieve our task:  

Syntax:

SELECT * INTO [New_Table_Name]

FROM [Existing_Table_Name]

Example:

SELECT * INTO Employee_New

FROM Employee

How to Right Align Numeric Values in Result Panel in SQL Server?

Generally we apply right alignment to numeric values in our project for easily access and its one of convention to apply right align for numeric. We can also apply this in SQL.

Steps:

1) Open SSMS à Tools à Options..

2) Query Result à SQL Server à Result to Text

3) Check ‘Right align numeric values’ à save it and see result by executing query in new Query Window.

Note: It only works for ‘Result to Text’ option.

Result:

How to reset/set new Identity value in SQL?

We can easily change, reset, view identity value using DBCC(Database Console Commands) of SQL.

1) To Reset Current Identity Value, if needed

Syntax: DBCC CHECKIDENT ("Table_Name")

Example: DBCC CHECKIDENT ("tblCustomers")

2) To View Current Identity Value

Syntax: DBCC CHECKIDENT ("Table_Name", NORESEED)

Example: DBCC CHECKIDENT ("tblCustomers", NORESEED)

3) To Change Identity Value with New Value

Syntax: DBCC CHECKIDENT ("Table_Name", RESEED, New_Value)

Example: DBCC CHECKIDENT ("tblCustomers", RESEED, 25)

How to use "UNION" and "ORDER BY" together in SQL?

This is one of tricky question and I love tricky question so I am going to write this post. How if I run below query in sql server:

SELECT TOP 1 * from Employee ORDER BY EmpId ASC

UNION

SELECT TOP 1 * from Employee ORDER BY EmpId DESC

This query result in error due to incorrect position of ORDER BY cause we only use ORDER BY clause to the final result set.

Error Message:

There are many ways to recreate above query without error but the easiest one is following:

SELECT *

FROM (

    SELECT TOP 1 * FROM Employee ORDER BY EmpId ASC) FIRST

UNION

SELECT *

FROM (

    SELECT TOP 1 * FROM Employee ORDER BY EmpId DESC) LAST

How to make your own keyboard shortcut in SSMS?

Today I am going to show you how to create your own keyboard shortcut in SQL server management studio to increase your productivity in development.

We all write SELECT * FROM query very frequently while using SQL so instead write it we can create it as a keyboard shortcut for quick access. Finally it’s depends on you which query you uses most frequently and create shortcut according to it.

STEPS:

1) Open SQL server management studio.

2) Goto Tools à Options.. à Environment à Keyboard.

3) Select your shortcut and enter your query.

How To Use:

Now in Query editor, you need to just type [Table_Name] and select that [Table_Name] then just press shortcut key that you create. That’s it. Enjoy.!!

LEN vs DATALENGTH in SQL Server

LEN() Function: It will return number of character in string that you mention without considering trailing space.

DATALENGTH() Function: It will return number of bytes used to represent the expression. So it will consider space as well in counting.

Please take a look at below image for more idea:

Quick way to put and use your most reusable code.

Today i am going to show one tips of visual studio which i love most. This tips related to quick access of most usable/common code that we used more frequently. We generally waste time to find code across pages and project that we need actually while development so it’s better to save most frequently used code in Toolbox of Visual Studio. You can just Drag your block of code and Drop into toolbox which shown in below figure:

So when you need this code just Drop them into page from toolbox rather then search it out across many pages and projects. You can also rename it if you have many code block in toolbox.

Happy Coding!!  

Delay Function in SQL Server

The Delay function used to execution of a batch in block, stored procedure, or transaction until a specified time or time interval is reached, or a specified statement modifies or returns at least one row.

While executing the WAITFOR statement, the transaction is running and no other requests can run under the same transaction.

Remarks:

Ø  If a query cannot return any rows, WAITFOR will wait forever or until TIMEOUT is reached, if specified.

Ø  WAITFOR statements not used in Views and Cursor.

Ø  When the query exceeds the query wait option, the WAITFOR statement argument can complete without running. You can create a Deadlock by running a query with WAITFOR.

Example:

SELECT GETDATE() CurrentDateTime
WAITFOR DELAY '00:00:05' ---- 5 Second Delay
SELECT GETDATE() CurrentDateTime

Note: You can also perform update, DB Backup and Restore and many important operation easily using this command.

What is X and Z report in POS(Point of Sale)

The format and contents of X report and Z report are mostly same. It may contain following main content and it may vary based on requirement:

1) Date and Time the report was perform

2) Identification Information (Shift/Batch related information)

3) Opening and closing balance

4) Discount, Total sales, Void transaction and many more

5) Tax, promotions, vouchers related information etc

Now the main question is what is the difference between X report and Z report, which is listed below:

X Report	Z Report
It shows current shift/batch information in details without closing shift/batch.	It closed current running shift and open new shift.
User can print it any time in a day to check transaction details.	It’s more preferable to print it at the end of day.

SET FMTONLY in SQL Server

It returns only SQL table definition to user. It can be used to test the format of the response without actually running the query. It returns the column information only; no rows of data are returned.

Syntax: SET FMTONLY { ON | OFF }

1) SET FMTONLY ON Example:

2) SET FMTONLY OFF Example: