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

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)

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.

SQL Quiz

You discovered that the schema changes that were recently made to your SQL Server database have caused your applications (website, mobile, or desktop apps) to stop functioning. It is unclear who made the changes.  You need to implement a mechanism that will track schema changes in your database. What should you do?

A. Implement a stored procedure that writes data about schema changes to a log table.
B. Implement DDL AFTER triggers that write user and schema information to a log table.
C. Implement a DML INSTEAD OF trigger that writes data about schema changes to a log table.
D. Implement a DML AFTER trigger that writes data about schema changes to a log table.
E. Both A and C
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.

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.

DBCC OPENTRAN

You manage a database named LTDB. Today, you received a warning that the drive on which the LTDB log file is located is near capacity. Although the transaction log is backed up every five minutes, you observed that it is steadily growing. You think that an uncommitted transaction might be the cause and you want to investigate. You need to identify both the server process ID and the start time of the oldest active transaction in LTDB. What should you do?

A. Connect to the LTDB database. Execute DBCC OPENTRAN. View the SPID and Start time rows.
B. Connect to the master database. Execute DBCC OPENTRAN. View the SPID and Start time rows.
C. In SQL Server Management Studio, open the Activity Monitor. Select the Process Info page and apply the following filter settings.
Database = LTDB, Open Transactions = Yes, View the Process ID and Last Batch columns.
D. Open a query window. Connect to the master database. Execute the following statement
SELECT TOP 1 spid, last_batch
FROM sys.sysprocesses WHERE dbid = db_id('LTDB') AND open_tran > 0
ORDER BY last_batch

Answer:[A]
Highlight to find out the answer

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.

Read a SQL Server table into a list using Python + pyodbc

import pyodbc as p
server = 'ServerName'
database = 'DatabaseName'
userid = 'UserName'
pwd = 'UserPassword'

connStr = ( r'DRIVER={SQL Server};SERVER=' +
            server + ';DATABASE=' + database + ';' +
            'UID=' + userid + ';PWD='+pwd+';')        
lst = []
conn = p.connect(connStr)
dbCursor = conn.cursor()
sql = ('SELECT ColumnName AS FieldValue FROM tblTableName') 
dbCursor = conn.cursor()
dbCursor.execute(sql)
for row in dbCursor:
    lst.append(row.FieldValue)        
conn.close()
print lst

Shrinking Truncate Log File

USE YourDatabaseName
GO
--Run this script to get your TransactionLogName
SELECT * FROM sys.sysfiles
GO
DBCC SHRINKFILE(TransactionLogName, 1)
BACKUP LOG YourDatabaseName WITH TRUNCATE_ONLY
DBCC SHRINKFILE(TransactionLogName, 1)
GO

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'

SET NUMERIC ROUNDABORT

What will be the output of the following scripts?

SET NUMERIC_ROUNDABORT ON
SET ARITHABORT ON
GO
DECLARE @c NUMERIC(5, 2),
   @a NUMERIC(5, 4), 
   @b NUMERIC(5, 4)
SET @a = 1.1234
SET @b = 1.1234 
SELECT @c = @a + @b
SELECT @c

A. 2.24
B. 2.25
C. Error message: Arithmetic overflow error converting numeric to data type numeric.
D. 2.2468

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

Logical join

Do you know the difference between logical join and physical join?
What are not logical join?

A. INNER JOIN
B. LEFT JOIN
C. RIGHT JOIN
D. MERGE JOIN
E. SELF JOIN
F. CROSS JOIN

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