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