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

Create SQL Server linked server to PostgreSQL database

1. Download ODBC Driver at this address http://www.postgresql.org/ftp/odbc/versions/msi/

Choose the suitable version with your PostgreSQL database. I’m using PostgreSQL 9, so I chose latest version. (^_^)

2. Install ODBC Driver for PostgreSQL

3. Create connection to PostgreSQL database with ODBC Driver

4. Now, it’s time to create linked server to PostgreSQL database:

You can create with GUI

Or can create with scripts

EXEC master.dbo.sp_addlinkedserver @server = N'OPENERP', @srvproduct=N'PostgreSQL', @provider=N'MSDASQL', @datasrc=N'OpenERP';

GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'OPENERP',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL;

GO

Finally, you can query PostgreSQL database inside SQL Server

5. Verify the linked server

Query Excel spreadsheet on SQL Server 2008 64 bit with Linked Server

For SQL Server 64 bit, if you use Microsoft.Jet.OLEDB.4.0, you may experience this issue:

OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

To fix this, please follow the following:

1. Download and install Microsoft Access Database Engine 2010 Redistributable at this address http://www.microsoft.com/en-us/download/details.aspx?id=13255 

2. After installing, create a linked server to your Excel file

EXEC master.dbo.sp_addlinkedserver @server = N'EXCEL_LINKED_SERVER_NAME'
                    , @srvproduct=N'Excel'
                    , @provider=N'Microsoft.ACE.OLEDB.12.0'
                    , @datasrc=N'C:\Your_Excel_File.xls'
                    , @provstr=N'Excel 12.0';

GO

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'EXCEL_LINKED_SERVER_NAME',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL;

GO

3. Run OPENQUERY to get data from your Excel file

SELECT * FROM OPENQUERY(EXCEL_LINKED_SERVER_NAME,'SELECT * FROM [Sheet1$]')

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

Stored procedure: why use & why not use

Use Stored Procedure
● Reusability
– Avoid rewriting sub queries and improve readability.
– If you can't store a query in a library that all the applications can access, you can put that query in a stored procedure.
● Separation of duties
– You don't trust non-DBA's to write queries.
● Data integrity
– Use triggers or constraints to prevent bad data from entering.
– Run several interdependent queries in a transaction in a single stored procedure.
● Event handling
– Log changes.
– Notify other systems of new data.

Not Use Stored Procedure
● Views may be all you need.
● An object-relational mapping (ORM) can help write queries safely.
● Difficult to version control stored procedures.
● Software rollouts may require more db changes.
● Could slow software development process.

Join quiz 1

Look at the above picture and choose the correct answer:
A. SELECT * FROM TableA t1 INNER JOIN TableB t2 ON t1.ID = t2.ID
B. SELECT * FROM TableA t1 LEFT JOIN TableB t2 ON t1.ID = t2.ID
C. SELECT * FROM TableA t1 RIGHT JOIN TableB t2 ON t1.ID = t2.ID
D. SELECT * FROM TableA t1, TableB t2 WHERE t1.ID = t2.ID
E. Both A and D
F. None of the above

Answers: [E]

CLR Quiz

You're developing an application which has a feature to read data from a spreadsheet, performs some calculations, and returns the data to a SQL Server computer. You decide to use CLR function and need to register the assembly with SQL Server by using the CREATE ASSEMBLY statement and the least privileged security permission set. Which permission set should you use?

A. Default
B. SAFE
C. EXTERNAL_ACCESS
D. UNSAFE
E. Both B and C
F. None of the above

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

Login failed

You're developing a website with 5 members. A member needs access to a SQL Server database that is located on a server named SQL1. You create a login named member1 by using the following Transact-SQL statement. CREATE LOGIN member1 WITH PASSWORD = 'member$1'. The member reports that when he logs in, he receives the following error message: "Login failed. The user is not associated with a trusted SQL Server connection." You need to resolve the error and allow him to access to SQL1. What should you do?

A. Change the SQL Server security mode from Windows Authentication mode to SQL Server and Windows Authentication mode.
B. Change the SQL Server security mode from SQL Server and Windows Authentication mode to Windows Authentication mode.
C. Give the login access to a specific database by using the CREATE USER Transact-SQL statement. 
D. Ensure that the login name is created with square brackets ([]).
E. Both A and C
F. None of the above

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

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.

Data type precedence

Do you remember data type precedence?
Which of the following data types has the highest data type precedence in SQL Server 2008?

A. DATETIME2
B. XML
C. BIGINT
D. UNIQUEIDENTIFIER
E. DATETIME
F. NTEXT

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

XOR operator

What will the output of the following scripts?
DECLARE @A INT, @B INT
SELECT @A = 12, @B = 15
SET @A = @A^@B
SET @B = @B^@A
SET @A = @A^@B
SELECT @A, @B

A. 12,15
B. Error occurs: no operator ^
C. Error occurs: incorrect syntax
D. 15,12
E. 13,14

Answer:[D]
Highlight to find out the answer.

Create linked server to Access 2007 accdb file

EXEC master.dbo.sp_addlinkedserver @server = N'AccessLinkedServerName', @srvproduct=N'Access', @provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=N'D:\Projects\MyAccessDB.accdb'
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'AccessLinkedServerName', @locallogin = NULL , @useself = N'False'

Get numbers from 1 to 999 with a query

WITH tblDigit(d) AS (
   SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION
   SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION
   SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION
   SELECT 0)
SELECT t1.d + t2.d * 10 + t3.d*100 AS Number
FROM   tblDigit t1, tblDigit t2, tblDigit t3
WHERE t1.d + t2.d + t3.d >0
ORDER BY t1.d + t2.d*10 + t3.d*100

Find output

How many rows will be returned in the following scripts?
DECLARE @Test TABLE(TestValue CHAR(1))
INSERT INTO @Test
SELECT 'A'UNION SELECT 'B'
UNION SELECT 'C'UNION SELECT 'D'
UNION SELECT 'E'
SELECT DISTINCT CAST(5 * RAND(CHECKSUM(NEWID())) + 1 as INT) AS RandomValue
FROM @Test

A. 1
B. 2
C. 3
D. 4
E. 5
F. It depends

Answer:[F]
Highlight to find out the answer.

Find output

How many rows will be returned in the following scripts?
DECLARE @Test TABLE(TestValue CHAR(1))
INSERT INTO @Test
SELECT 'A'UNION SELECT 'B'
UNION SELECT 'C'UNION SELECT 'D'
UNION SELECT 'E'
SELECT DISTINCT CAST(5 * RAND() + 1 as INT) AS RandomValue
FROM @Test

A. 1
B. 2
C. 3
D. 4
E. 5
F. It depends

Answer:[A]
Highlight to find out the answer.

Find output

How many rows will be returned in the following scripts?

DECLARE @DBMS TABLE(DBMS VARCHAR(15))
INSERT INTO @DBMS
SELECT 'SQL Server'
UNION SELECT 'MySQL'
UNION SELECT 'DB2'
UNION SELECT 'Oracle'
UNION SELECT 'PostgreSQL'
UNION SELECT 'SQLite'
UNION SELECT 'Access'
UNION SELECT 'EnterpriseDB'
DECLARE @DBMSValue VARCHAR
SET @DBMSValue = 'SQL'
SELECT * FROM @DBMS WHERE DBMS LIKE '%' + @DBMSValue + '%'

A. 0
B. 2
C. 4
D. 6
E. 8
F. Error will be generated

Answer:[D]
Highlight to find out the answer.