How to identify your SQL Server version and edition

For SQL Server 2005:
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

For SQL Server 2000:
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

For SQL Server 7.0
SELECT @@VERSION

For more information: read here

Quick check all servers running or not

As a DBA, you may manage many servers. If you manage them remotely, you may want to know if they are open or not.
You may use command line ping yourserver to check. However, you are managing 4 server and are lazy to input every time you need to check.
Just create a bat file to check it for you.
For example, you are managing server1, server2, server3, and server4. Create a .bat file with the following content:
C:\Windows\cmd.exe
ping server1
ping server2
ping server3
ping server4
echo Press any key to exit...
pause

Only run this .bat file every time you want to check these servers running or not.

How to cancel the 'stubborn' job/queue in Project Server

When working with Project Server, sometimes you meet a failed process or failed job in Manage Queue Jobs. Its job state is 'Getting Queued'. You tried to cancel it but it's still there. In this case, you cannot force check-in the related project.
To 'kill' it and allow other jobs to run, what you need to do is enable an additional option in order to cancel the jobs.
- Login to PWA as an Administrator
- Going to Server Settings - Manage Queue.
- On the Manage Queue Jobs page, expand the Advanced Options
- Enable the "Cancel jobs getting enqueued."
- Select the particular item in the queue that's stuck and click the Cancel Jobs button.
This should cancel the blocked job and then allow the other correlated/related jobs to go through.

How to encrypt your stored procedure, view, function, etc in SQL Server

Sometimes you don't want others to know the source code in your stored procedures, functions, views, etc. in SQL Server.
In this case, you can include WITH ENCRYPTION followed by CREATE PROCEDURE, CREATE VIEW, CREATE FUNCTION, etc. like that:
CREATE PROCEDURE ProcedureName
WITH ENCRYPTION

CREATE VIEW ViewName
WITH ENCRYPTION

CREATE PROCEDURE FunctionName
WITH ENCRYPTION

Limit in select query

SQL Server:
SELECT TOP 10 * FROM [TableName]

Oracle:
SELECT * FROM [TableName] WHERE RowNum <= 10 MySQL:
SELECT * FROM [TableName] LIMIT 0,10 (First 10 rows start from row 1)
SELECT * FROM [TableName] LITMIT 5, 10 (First 10 rows start from row 6)

Get schema of one table

select t2.name [TABLE_NAME]
, t1.name [COLUMN_NAME]
, t4.name [TYPE_NAME]
, t1.max_length
, t1.precision
, t1.scale
, t1.is_nullable
, t1.is_identity
, t3.seed_value
, t3.increment_value
from sys.columns t1
inner join sys.tables t2 on t1.[object_id] = t2.[object_id]
left join sys.identity_columns t3 on t1.column_id = t3.column_id and t2.[object_id] = t3.[object_id]
inner join sys.types t4 on t1.[user_type_id] = t4.[user_type_id]
where t2.name = 'table_name'

Access SQL Server catalog

Sometimes, you need to know schema in details of your database. You may use system objects such as sysobjects/ sys.objects, syscolumns/ sys.columns, etc or system stored procedures to query that information.
Another way to get that information is using INFORMATION SCHEMA in SQL Server 2005:

View	Contains information about
COLUMNS	Columns accessible to the current user in the current database.
INDEXES	Indexes in the current database.
KEY_COLUMN_USAGE	Keys in the current database.
PROVIDER_TYPES	Data types supported in SQL Server Mobile.
TABLES	Tables accessible to the current user in the current database.
TABLE_CONSTRAINTS	Table constraints in the current database.
REFERENTIAL_CONSTRAINTS	Foreign constraint in the current database

Source: SQL Server Book online
For example:
If you want to know the schema of tblEmployee Table, you can query like that:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tblEmployee'

PIVOT in SQL Server

CREATE PROCEDURE [dbo].[spLoadEmpFiles]
@FromDate SMALLDATETIME = NULL,
@ToDate SMALLDATETIME = NULL,
@EmployeeCode NVARCHAR(50) = '',
@EmployeeName NVARCHAR(255) = '',
@CompanyName NVARCHAR(255) = '',
@DepartmentName NVARCHAR(255) = '',
@DeptUnitName NVARCHAR(255) = '',
@JobTitleName NVARCHAR(255) = '',
@TeamName NVARCHAR(255) = '',
@Gender NVARCHAR(1) = ''
AS
BEGIN
DECLARE @HRFileID INT
DECLARE @HRFileName NVARCHAR(50)

DECLARE Cur CURSOR FOR
SELECT HRFileID, HRFileName
FROM dbo.tblHRFile

OPEN Cur
FETCH FROM Cur
INTO @HRFileID, @HRFileName
DECLARE @strSQL NVARCHAR(MAX)
DECLARE @params NVARCHAR(MAX)

SET @strSQL = 'SELECT t1.EmployeeID, t1.EmployeeCode, t1.EmployeeName
, t1.Gender
, t4.CompanyName
, t5.DepartmentName
, t6.DeptUnitName
, t7.JobTitleName
, t8.TeamName '
WHILE @@FETCH_STATUS = 0
BEGIN
SET @strSQL = @strSQL +', MAX(CASE WHEN t3.HRFileID = ' + CAST(@HRFileID AS VARCHAR) + ' Then ''x'' ELSE '''' END) AS [' + @HRFileName + ']'

FETCH FROM Cur
INTO @HRFileID, @HRFileName
END
CLOSE Cur
DEALLOCATE Cur

SET @strSQL = @strSQL + ' FROM dbo.tblEmployee t1
LEFT JOIN dbo.fnAppoint(@ToDate) t2 ON t1.EmployeeID = t2.EmployeeID
LEFT JOIN (SELECT t31.EmployeeID, t31.HRFileID, t32.HRFileName
FROM tblEmployeeFile t31
INNER JOIN dbo.tblHRFile t32 ON t31.HRFileID = t32.HRFileID) t3 ON t1.EmployeeID = t3.EmployeeID
LEFT JOIN dbo.tblCompany t4 ON t2.CompanyID = t4.CompanyID
LEFT JOIN dbo.tblDepartment t5 ON t2.DepartmentID = t5.DepartmentID
LEFT JOIN dbo.tblDeptUnit t6 ON t2.DeptUnitID = t6.DeptUnitID
LEFT JOIN dbo.tblJobTitle t7 ON t2.JobTitleID = t7.JobTitleID
LEFT JOIN dbo.tblTeam t8 ON t2.TeamID = t8.TeamID
WHERE 1 = 1 '

IF LEN(@EmployeeCode) > 0
SET @strSQL = @strSQL + ' AND t1.EmployeeCode LIKE @EmployeeCode '
IF LEN(@EmployeeName) > 0
SET @strSQL = @strSQL + ' AND t1.EmployeeName LIKE @EmployeeName '
IF LEN(@Gender)> 0
SET @strSQL = @strSQL + ' AND t1.Gender LIKE @Gender '
IF LEN(@CompanyName) > 0
SET @strSQL = @strSQL + ' AND t4.CompanyName LIKE @CompanyName '
IF LEN(@DepartmentName) > 0
SET @strSQL = @strSQL + ' AND t5.DepartmentName LIKE @DepartmentName '
IF LEN(@DeptUnitName) > 0
SET @strSQL = @strSQL + ' AND t6.DeptUnitName LIKE @DeptUnitname '
IF LEN(@JobTitleName) > 0
SET @strSQL = @strSQL + ' AND t7.JobTitleName LIKE @JobTitleName '
IF LEN(@TeamName) > 0
SET @strSQL = @strSQL + ' AND t8.TeamName LIKE @TeamName '
SET @strSQL = @strSQL + ' GROUP BY t1.EmployeeID
, t1.EmployeeCode
, t1.EmployeeName
, t1.Gender
, t4.CompanyName
, t5.DepartmentName
, t6.DeptUnitName
, t7.JobTitleName
, t8.TeamName '

SELECT @params = N'@FromDate SMALLDATETIME
, @ToDate SMALLDATETIME
, @EmployeeCode NVARCHAR(50)
, @EmployeeName NVARCHAR(255)
, @CompanyName NVARCHAR(255)
, @DepartmentName NVARCHAR(255)
, @DeptUnitName NVARCHAR(255)
, @JobTitleName NVARCHAR(255)
, @TeamName NVARCHAR(255)
, @Gender NVARCHAR(1)'

EXEC sp_executesql @strSQL, @params
, @FromDate = @FromDate
, @ToDate = @ToDate
, @EmployeeCode = @EmployeeCode
, @EmployeeName =@EmployeeName
, @CompanyName =@CompanyName
, @DepartmentName = @DepartmentName
, @DeptUnitName =@DeptUnitName
, @JobTitleName =@JobTitleName
, @TeamName =@TeamName
, @Gender = @Gender
END

Convert numeric for viewing easily

CREATE FUNCTION [dbo].[fnDisplayNumber](@dblNumber real, @intNumberType smallint = 0, @intNoOfDigitsAfterDecimal smallint=0)
--@NumberTime : 0 (American), 1(Vietnamese)
RETURNS nvarchar(30) AS
BEGIN
declare @i tinyint
declare @strLeft nvarchar(30)
declare @strRight nvarchar(30)
declare @strResult nvarchar(30)

if @intNoOfDigitsAfterDecimal<0 intnoofdigitsafterdecimal =" 0">10
begin
set @intNoOfDigitsAfterDecimal = 10
end

set @strResult = ltrim(str(@dblNumber, 30, @intNoOfDigitsAfterDecimal))

if charindex('.', @strResult) > 0
begin
set @strLeft = left(@strResult, charindex('.',@strResult)-1)
end
else
begin
set @strLeft = @strResult
end

set @strRight = right(@strResult, @intNoOfDigitsAfterDecimal)

set @i=1
set @strResult = ''
while @i<=len(@strLeft) begin set @strResult = @strResult + substring(@strLeft, @i, 1) if @i % 3 = len(@strLeft) % 3 AND @i <> len(@strLeft)
begin
if @intNumberType = 1
begin
Set @strResult = @strResult + '.'
end
else
begin
Set @strResult = @strResult + ','
end
end
set @i = @i + 1
end

if @intNoOfDigitsAfterDecimal>0
begin
if @intNumberType = 1
begin
Set @strResult = @strResult + ',' + @strRight
end
else
begin
Set @strResult = @strResult + '.' + @strRight
end
end

return @strResult
END

Calculate between two points on the earth

/*
Definitions:
South latitudes are negative, east longitudes are positive
Description:
This routine calculates the distance between two points (given the latitude/longitude of those points).
Passed to function:
@lat1, @lon1 = Latitude and Longitude of point 1 (in decimal degrees)
@lat2, @lon2 = Latitude and Longitude of point 2 (in decimal degrees)
@unit = the unit you desire for results
where: 'M' is statute miles
'K' is kilometers (default)
'N' is nautical miles
*/
CREATE FUNCTION dbo.fnGetDistance
(
@lat1 float,
@lon1 float,
@lat2 float,
@lon2 float,
@unit char(1)
)
RETURNS float
AS
BEGIN
DECLARE @theta FLOAT
DECLARE @dist FLOAT
DECLARE @pi FLOAT
SET @pi = 3.14159265358979323846
SET @theta = @lon1 - @lon2
SET @dist = sin(@lat1 * @pi/180) * sin(@lat2 * @pi/180)
+ cos(@lat1 * @pi/180) * cos(@lat2 * @pi/180) * cos(@theta * @pi/180)
SET @dist = acos(@dist)
SET @dist = @dist * 180/@pi
SET @dist = @dist * 60 * 1.1515
IF @unit = 'K'--Kilometer
SET @dist = @dist * 1.609344
ELSE IF @unit = 'N'--Nautical Miles
SET @dist = @dist * 0.8684

RETURN @dist
END