Same query for Fiscal and Normal calendar

tblFiscalMapping

Assume that you have a mapping table between Fiscal and Normal calendar above.

Sometimes, you need to write one stored procedure with a param @IsFiscal to determine when to return order info on Fiscal year or Normal calendar year.

You can implement as the following

DECLARE @StartMonth SMALLDATETIME;   
DECLARE @EndMonth SMALLDATETIME;

IF ISNULL(@IsFiscal,0) = 0
    SELECT @StartMonth = CAST(CAST(@Year AS VARCHAR) + '-01-01' AS SMALLDATETIME);
ELSE
    SELECT @StartMonth = CAST(CAST([Year] AS VARCHAR) + '-' + CAST(NormalMonth AS VARCHAR) + '-01' AS SMALLDATETIME)
    FROM dbo.tblFiscalMapping
    WHERE FiscalYear = @Year;

SET @EndMonth = DATEADD(m, 11, @StartMonth);

SELECT OrderID, OrderNumber, OrderDate, CustomerID, TotalAmount

FROM dbo.tblOrder

WHERE DATEDIFF(m, @StartMonth, OrderDate) >= 0
                                AND DATEDIFF(m, @EndMonth, OrderDate) <= 0

Get current queries executing on SQL Server

SELECT t1.session_id
 , t2.text
, t1.[status]
, t1.blocking_session_id
, t1.cpu_time
, t1.total_elapsed_time  
FROM sys.dm_exec_requests t1  
     CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS t2

Get rows without duplicate values

CREATE TABLE T1(a INT,b INT,c INT)

INSERT INTO T1(a,b,c)

SELECT 1,2,3

UNION

SELECT 2,2,4

UNION 

SELECT 3,3,3

UNION

SELECT 4,3,5

SELECT *

FROM T1

WHERE a NOT IN (SELECT MAX(a)

                FROM T1

                GROUP BY b)

Add new column as NOT NULL to existing table

You are modifying a table named tblEmployee in a SQL Server database. You want to add a new column named JobTitle to the tblEmployee table. The table currently contains data. The HR Department has not yet created a job title for each employee. JobTitle is a required value for each employee. You want to add this new column by using the least amount of effort. What should you do?

A. Define the new column as NULL. Update the JobTitle column to the same value as 'Undefined'. Modify the JobTitle column to be NOT NULL.
B. Define the new column as NOT NULL with a default value of 'Undefined.'
C. Define the new column as NULL. Use application logic to enforce the data constraint.
D. Define the new column as NULL with a default value of 'Undefined.'
E. Both A and B
F. None of the above

Answer: [B]
Highlight to find out the answer.
Visit here to practice your SQL Server skills.

Create View Quiz

You are creating a view to join the tblEmployee and tblDepartment tables in a SQL Server database. You need to ensure that the view cannot be affected by modifications to underlying table schemas. You want to accomplish this goal by using the least possible amount of overhead. What should you do?

A. Create CHECK constraints on the tables.
B. Create a DDL trigger to roll back any changes to the tables if the changes affect the
columns in the view.
C. Create the view, specifying the WITH SCHEMABINDING option.
D. Create the view, specifying the WITH CHECK option.
E. Both C and D
F. None of the above

Answer: [C]
Highlight to find out the answer.
Come here to know more about CREATE VIEW
Visit here to practice your SQL Server skills.

Find temp tables

Local temporary tables are only available to the current connection for the user; and they are automatically deleted when the user disconnects from instances.
Local temporary table name starts with hash ("#") sign.
If you want to find all local temporary tables in current connction, you can use the following script

SELECT *  
FROM tempdb.sys.objects  
WHERE name LIKE '#%';

Trigger quiz

Your company uses a SQL Server database to manage data on e-Commerse site. This database contains a trigger named tgInsertOrder, which fires when order data is inserted into the tblOrder table. The trigger is responsible for ensuring that a customer exists in the tblCustomer table before data is inserted into the tblOrder table. You need to configure the trigger to prevent it from firing during the data import process. You must accomplish this goal while using the least amount of administrative effort.
Which T-SQL statements can you use to achieve this goal?

A. ALTER TABLE tblOrder DISABLE TRIGGER tgInsertOrder
B. DROP TRIGGER tgInsertOrder
C. DISABLE TRIGGER tgInsertOrder ON tblOrder
D. ALTER TRIGGER tgInsertOrder ON tblOrder NOT FOR REPLICATION
E. Both A & C
F. None of the above

Answer: [E]
Highlight to find out the answer.
Visit here to practice your SQL Server skills.

Query transactions by date

You work for a supermarket that uses a SQL Server database to store line items from point of sales (POS) transactions. The POS processes 150,000 transactions every day. The application requires a clustered index on the TransactionID column. You need to create a table that supports an efficient reporting solution that queries the transactions by date.
What will you do to achieve this goal?

A. Place a nonclustered index on the date column.
B. Add a unique clustered index on the date column.
C. Map each partition to a filegroup, with each filegroup accessing a different physical drive.
D. Create a partitioning scheme that partitions the data by date.
E. Both A and D
F. None

Answer: [E]
Highlight to find out the answer.
Visit here to practice your SQL Server skills.

SQL Server quiz

You're managing a SQL Server computer that was installed using default settings. After a power failure, the SQL Server (MSSQLSERVER) service on your database server does not start. You need to find out the cause of the problem. Which action should you perform?

A. In Event Viewer, view the system log or the application log
B. In Notepad, view the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ErrorLog.1 file.
C. In Notepad, view the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ErrorLog file.
D. In Notepad, view the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLAgent.out file.
E. Both A and C
F. Both B and C

Answer: [E]
Highlight to find out the answer.
Visit here to practice your SQL Server skills.

Full text search with FORMSOF

Find all employees where their notes contains any form of the word graduate

SELECT * FROM Employees
WHERE CONTAINS (Notes, 'FORMSOF(INFLECTIONAL, "graduate")')

Delete all foreign keys in a database

DECLARE @Table VARCHAR(255)
DECLARE @ForeignKey VARCHAR(255)
DECLARE @STRSQL VARCHAR(8000)

DECLARE Cur CURSOR FOR   
 select t1.name as [Table], t2.name as [ForeignKey]
 from sysobjects t1 inner join
  sysobjects t2 on t1.id = t2.parent_obj
 where t1.xtype = 'U' and t2.xtype = 'F'
OPEN Cur
FETCH FROM Cur
INTO @Table, @ForeignKey
WHILE @@FETCH_STATUS = 0
BEGIN
 SET @STRSQL = 'ALTER TABLE ' + @Table +
   ' DROP CONSTRAINT ' + @ForeignKey
 EXECUTE(@STRSQL)

 FETCH FROM Cur
 INTO @Table, @ForeignKey
END
CLOSE Cur
DEALLOCATE Cur

Find a text inside stored procedure, trigger, view, etc.

Sometimes, you need to find a specific text in your stored procedures, this script may be useful to help you.

SELECT DISTINCT t2.name [Object Name],
    CASE t2.type
        WHEN'U' THEN 'User Table'
        WHEN'S' THEN 'System Table'
        WHEN 'V' THEN 'View'
        WHEN 'TR' THEN 'Trigger'
        WHEN 'P' THEN 'Stored Procedure'
        WHEN 'C' THEN 'Constraint - Check'
        WHEN 'D' THEN 'Default'
        WHEN 'K' THEN 'Primary Key'
        WHEN 'F' THEN 'Foreign Key'
        WHEN 'L' THEN 'Log'
        WHEN 'R' THEN 'Rule'
        WHEN 'RF' THEN 'Replication Filter stp'
        ELSE 'Other: ''' + t2.type+ ''''
    END [Object Type],
 t2.crdate [Created Date]
FROM syscomments t1
    INNER JOIN sysobjects t2 ON t1.id = t2.id
WHERE t1.[text] LIKE '%Your text need searching%'

Create mutiples linked servers

When you need to work with multiple databases from Oracle, for example: 100 databases with name like that DB01, DB02, ..., DB100. All of them use the same user name and password to query data such as scott/tiger. Your job is to connect them from SQL Server. First, you need to create 100 linked servers. It takes much time to create 100 linked servers like that by using create linked server screen.
You can do this by this script.

DECLARE @LINK_NAME VARCHAR(10)
DECLARE @SERVER_NAME VARCHAR(50)
DECLARE @COUNT INT
SET @COUNT = 1;
WHILE @COUNT < 101
BEGIN
SET @LINK_NAME = 'DB' + RIGHT('00' + CAST(@COUNT AS VARCHAR),2);
SET @SERVER_NAME = @LINK_NAME + '{Some name}';
EXEC master.dbo.sp_addlinkedserver @server = @LINK_NAME
, @srvproduct=N'Oracle', @provider=N'OraOLEDB.Oracle', @datasrc=@SERVER_NAME;
EXEC sp_addlinkedsrvlogin @LINK_NAME, 'false', 'domain\user', 'scott', 'tiger';

SET @COUNT = @COUNT + 1;
END

LEFT JOIN in Oracle and SQL Server

You have two tables:
tblEmployee(EmployeeID, EmployeeName, DepartmentID, ...) contains all employee information
tblDepartment(DepartmentID, DepartmentName) contains all departments in your company
Each employee belongs to one department. Sometimes, new employees don't have related department. You want to display all employees with their related departments. If someone does not have any department, display null
In this case, you have to use LEFT JOIN to query your data
In SQL Server:
SELECT t1.EmployeeID, t1.EmployeeName, t2.DepartmentName
FROM tblEmployee t1 LEFT JOIN tblDepartment t2 ON t1.DepartmentID = t2.DepartmentID

In Oracle:
SELECT t1.EmployeeID, t1.EmployeeName, t2.DepartmentName
FROM tblEmployee t1, tblDepartment t2
WHERE t1.DepartmentID = t2.DepartmentID(+)

How to query to linked server by using SQL Server

Assume that you have a linked server MyERP which connects to an Oracle database.
To be able to access data in this database in SQL Server, you can use OPENQUERY
Syntax for OPENQUERY:
OPENQUERY(MyERP,'Your query')

Some examples:
1. Select all employees in table tblEmployee:
SELECT *
FROM OPENQUERY(MyERP,'SELECT * FROM tblEmployee');

2. Update tblEmployee
UPDATE OPENQUERY(MyERP, 'SELECT EmployeeName FROM tblEmployee WHERE EmployeeID = 1')
SET EmployeeName = 'Barak Obama'

3. Delete tblEmployee:
DELETE OPENQUERY(MyERP, 'SELECT EmployeeName FROM tblEmployee WHERE EmployeeID = 1')

4. Insert to tblEmployee:
INSERT OPENQUERY(MyERP, 'SELECT EmployeeName FROM tblEmployee')
VALUES('Barak Obama')

For more information, click here

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)

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