Pages

Saturday 31 August 2013

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’

Saturday 27 July 2013

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.

Sunday 9 June 2013

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:


Saturday 25 May 2013

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:

Saturday 20 April 2013

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

Sunday 31 March 2013

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:

Saturday 16 March 2013

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)