@@IDENTITY

What will be the value of @@IDENTITY in the last row?
DECLARE @Test TABLE(a INT IDENTITY(1,1), b TINYINT)
INSERT INTO @Test(b) VALUES(1)
INSERT INTO @Test(b) VALUES(256) --Error is generated here, arithmetic overflow
INSERT INTO @Test(b) VALUES(255)
SELECT @@IDENTITY

A. NULL
B. 2
C. 3
D. 1

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

MS Access & MS SQL data type

Do you know the equivalent data types between MS Access and MSSQL?
What is the equivalent of MSSQL 'text' data type in MS Access?

A. memo
B. text
C. nvarchar
D. OLE Object
E. Attachment
F. char

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

Data Type Precedence

Do you remember data type precedence in SQL Server 2005?
Which of these data types have the highest and lowest precedence in sequence?

A. sql_variant, bit
B. sql_variant, binary
C. user-defined data type, char
D. user-defined data type, binary
E. ntext, bit
F. ntext, binary

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

String data type

We need to define a column that will contain user-defined values. Their length will vary from 10 to 15 characters using English letters, both upper and lower case mixed with numeric digits, with an even distribution of lengths.

Which of these data types will be more efficient?

A. char(15)
B. varchar(15)
C. nchar(15)
D. nvarchar(15)
E. varchar(max)
F. nvarchar(max)

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

INT and SMALLINT

What will be the output of the following scripts?
DECLARE @a INT
DECLARE @b SMALLINT
SET @a = POWER(2,15)
SET @b = @a
SELECT @a

A. 32768
B. 32767
C. An error will be generated
D. NULL

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

NULLIF

Do you remember NULLIF function?
What would be the output of the following scripts?
DECLARE @Test TABLE (a INT, b INT)
INSERT @Test VALUES(1,NULL)
INSERT @Test VALUES(NULL,2)
INSERT @Test VALUES(0,3)
INSERT @Test VALUES(NULL,4)
INSERT @Test VALUES(5,0)
SELECT SUM(NULLIF(COALESCE(a,b), 0.00))
FROM @Test

A. 14
B. 6
C. 9
D. 12
E. 8
F. 7

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

LEN with NVARCHAR(MAX)

What will be the output of the following scripts if you run them in SQL Server 2005 Management Studio?
DECLARE @STR NVARCHAR(MAX)
SET @STR = REPLICATE('1',8001)
SELECT LEN(@STR)

A. 8001
B. 8000
C. An error will be generated since the nvarchar(max) data types are invalid for local variables.
D. An error will be generated since the maximum of LEN function is only 8000

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

LEN with NTEXT

What will be the output of the following scripts if you run them in SQL Server Management Studio?
DECLARE @STR NTEXT
SET @STR = REPLICATE('1',8001)
SELECT LEN(@STR)

A. 8001
B. 8000
C. An error will be generated since the ntext data types are invalid for local variables.
D. An error will be generated since the maximum of LEN function is only 8000

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

COALESCE function

Do you remember COALESCE function?
What would be the output of the following scripts?
CREATE TABLE #Test(a INT NULL, b INT NULL, c INT NULL)
INSERT INTO #Test(a,b,c) VALUES(1,1,NULL)
INSERT INTO #Test(a,b,c) VALUES(NULL,2,NULL)
INSERT INTO #Test(a,b,c) VALUES(1,NULL,3)
SELECT COALESCE(a,b,c) FROM #Test

A. 1,2,3
B. 1,NULL,1
C. 1,2,1
D. NULL,NULL,NULL
E. NULL,2,3
F. 1,NULL,3

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

Find output

What will be the output of the following statements?
DECLARE @iRet INT
SELECT @iRet = ISNULL(@iRet,'1') + MyValue
FROM (SELECT '1' MyValue UNION SELECT '1' UNION SELECT '2') t1
SELECT @iRet

A. An error will be generated
B. '112'
C. 4
D. '1112'

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

I'm an SQL Guy

 ____
/  __)
\(~oo
_\ o/_
/  \/  \
/ / SQL\ \
\ | GUY/_/
\|___(_/
| |\  \
| |/  /
|_/__/
(__[__)

TRUNCATE TABLE

Which of the following is NOT true about the TRUNCATE TABLE command?

A. Removes all records from the table.
B. Resets the identity column to the seed of the column.
C. Delete triggers are executed.
D. It cannot be used on tables referenced by foreign tables.

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

Extended stored procedure or not

What will be the output of the following statements in SQL Server 2005?
SELECT OBJECTPROPERTY(OBJECT_ID('sp_executesql'),'IsExtendedProc')
SELECT OBJECTPROPERTY(OBJECT_ID('xp_grantlogin'),'IsExtendedProc')

A. 1, 1
B. 1, 0
C. 0, 1
D. 0, 0

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

FOR XML

The following query returns data as XML
SELECT ContactID, FirstName, LastName, Phone
FROM Person.Contact
ORDER BY ContactID
FOR XML AUTO,ELEMENTS
However, there're some NULL values in Phone column and it makes some Phone tag disappeared in returned XML. You need to modify this query so that all tags appear in the XML document. What should you do?

A. Add an XML index to the table that contains the product data.
B. Add the XSINIL argument to the ELEMENTS directive in the query.
C. Add a HAVING clause to the query.
D. Add the replace value of clause to the query.

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

Import data

When deploying the applications, you need to import data into SQL Server 2005. Data is contained in text files such as employees, contacts, customers, etc. Columns in the data are separated by semicolons. What should you do?

A. Use the bcp command, specifying a semicolon as the field terminator
B. Use the bcp command with the default arguments
C. Use BULK INSERT with the default arguments
D. Use OPENQUERY with the default arguments

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

Which network protocols below Microsoft SQL Server 2005 does not support?

A. Named Pipes
B. TCP/IP
C. Multiprotocol
D. Shared Memory
E. Virtual Interface Adapter (VIA)

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

ISNUMERIC function

Do you remember ISNUMERIC() built-in function?
What would be the output of the following script?

DECLARE @Year VARCHAR(10)
SET @Year = '2,010.'
SELECT ISNUMERIC(@Year)

A. 0
B. 1
C. True
D. False
E. An error will be generated.

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

Remove spaces

Your SQL Server database contains a lot of columns with data type nchar. Many of them contain preceding or trailing spaces. You need to implement mechanism to get the data without leading and trailing spaces. Your solution must be available for reuse in T-SQL statements and views. What should you do?

A. Create DML triggers that query the inserted and deleted tables
B. Create a stored procedure that calls LTRIM and RTRIM built-in function
C. Call TRIM built-in function
D. Create a function that calls RTRIM and LTRIM built-in function

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

Port number

What are the default port numbers of SQL Server and MySQL in sequence?

A. 3306 and 5432
B. 1433 and 3306
C. 1433 and 5432
D. 3306 and 1433
E. 5432 and 1433
F. 5432 and 3306

Answer:[B]
Explanation: SQL Server:1433, MySQL:3306, PostgreSQL: 5432
Highlight to find out the answer.

Reference to another server

You have two SQL Server 2005 computers. One server is SQL1 and another is SQL2.
You need to access SQL2 from SQL1. You have enough permissions on the two servers.
What should you do?

A. Join the two servers by using four-part syntax: server.database.schema.table
B. Reference SQL2 by using an alias
C. Add SQL2 as a remote server to SQL1
D. Add SQL2 as a linked server to SQL1

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

INT data type

Do you remember INT data type?
What's the maximum value can an INT data type hold?

A. 2,147,483,647
B. 2,147,483,648
C. 4,294,967,295
D. 4,294,967,296

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

Add a new column to an existing table

Which of the following statements is the correct syntax for adding a new column called Gender into a table called dbo.Contact that already contains data?

A. ALTER TABLE dbo.Contact ADD COLUMN Gender CHAR(1) NOT NULL
B. ALTER TABLE dbo.Contact ADD COLUMN Gender CHAR(1) NULL
C. ALTER TABLE dbo.Contact ADD Gender CHAR(1) NOT NULL
D. ALTER TABLE dbo.Contact ADD Gender CHAR(1) NULL

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

Find output

What is the result of the following batch?

DECLARE @Test TABLE(a INT IDENTITY, b VARCHAR(5))
DECLARE @sRet VARCHAR(255)
SET @sRet = 'Happy'
SELECT @sRet = ISNULL(b, '!') FROM @Test ORDER BY a
INSERT @Test VALUES('Happy')
INSERT @Test VALUES('Lunar')
INSERT @Test VALUES('New')
INSERT @Test VALUES('Year')
SELECT @sRet = @sRet + ' ' + b FROM @Test ORDER BY a
SELECT @sRet

A. NULL
B. ! Happy Lunar New Year
C. An error will be generated
D. Happy Happy Lunar New Year
E. Happy Lunar New Year

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

@@ERROR

What would be the output of this code when you open a new query window and execute it?

SELECT @@ERROR
SELECT @@ERROR

A. 0,0
B. 1,1
C. 0,1
D. 1,0

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

Find weak login passwords in your server

This is the script I posted on sqlservercentral.com last year.
DECLARE @WeakPwdList TABLE(WeakPwd NVARCHAR(255))
--Define weak password list
--Use @@Name if users password contain their name
INSERT INTO @WeakPwdList(WeakPwd)
SELECT ''
UNION SELECT '123'
UNION SELECT '1234'
UNION SELECT '12345'
UNION SELECT 'abc'
UNION SELECT 'default'
UNION SELECT 'guest'
UNION SELECT '123456'
UNION SELECT '@@Name123'
UNION SELECT '@@Name'
UNION SELECT '@@Name@@Name'
UNION SELECT 'admin'
UNION SELECT 'Administrator'
UNION SELECT 'admin123'
-- SELECT * FROM @WeakPwdList
SELECT t1.name [Login Name], REPLACE(t2.WeakPwd,'@@Name',t1.name) As [Password]
FROM sys.sql_logins t1
INNER JOIN @WeakPwdList t2 ON (PWDCOMPARE(t2.WeakPwd, password_hash) = 1
OR PWDCOMPARE(REPLACE(t2.WeakPwd,'@@Name',t1.name),password_hash) = 1)

Reserved Keywords again

Which of the following is a reserved keyword in SQL Server 2005 and therefore cannot be used as an object name or as a column name in a table unless delimited?

A. ADD
B. SUBTRACT
C. MINUS
D. MULTIPLY
E. DIVIDE
F. None of the above is a reserved keyword in SQL Server 2005

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

Data Type Precedence

Do you remember data type precedence?
Which of the following data types has the highest data type precedence?

A. SMALLDATETIME
B. SMALLMONEY
C. SMALLINT
D. CHAR

Answer:[A]
Highlight to find out the answer. Try to answer first before visiting the link above.

DATETIME data type

Do you remember DATETIME data type?
What will be the result of the following statement?

SELECT CAST(-1 AS DATETIME)

A. 1900-01-01 00:00:00.000
B. 1899-01-01 00:00:00.000
C. 1899-12-31 00:00:00.000
D. 1752-01-01 00:00:00.000
E. 1752-12-31 00:00:00.000
F. The statement will generate an error. Only positive integer values can be converted to a DATETIME data type.

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

Reserved Keywords

Which of the following is NOT a reserved keyword in SQL Server 2005 and therefore can be used as an object name or as a column name in a table without using delimiters.

A. FUNCTION
B. PROCEDURE
C. RETURN
E. RETURNS
E. EXECUTE
F. None of the above. All of these are reserved keywords in SQL Server 2005.

Answer:[E]
Explanation: Although RETURNS is used in user defined function it's not a reserved keyword.
Highlight to find out the answer.

Find output

What will be the result of the following SELECT statement?

SELECT 2010/02/16 + 4

A. 2010/02/20
B. 2000/06/16
C. 66
D. An error is generated.

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

public role

Do you remember the public role in SQL Server?
Which of the following is NOT a valid description of the public role?

A. The public role captures all default permissions for users in a database.
B. The public role cannot be dropped.
C. The public role is contained in every database, including msdb, tempdb, model, and all user databases except in the master database for security purposes.
D. The public role cannot have users, groups, or roles assigned to it.

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

Division by zero?

What will be the value of @A in the following statement?

SET ARITHABORT OFF
SET ANSI_WARNINGS OFF
DECLARE @A INT
SET @A = 2010/0

A. 0
B. NULL
C. Infinity
D. An error is generated.

Answer:[B]
Explanation: When both ARITHABORT and ANSI_WARNINGS set to OFF, SQL Server will return a NULL value in a calculation involving a divide-by-zero error.
Highlight to find out the answer.

Using GETDATE in function?

Can you use GETDATE() function in MSSQL2K, MSSQL2K5, and MSSQL2K8?
Which MSSQL version of the following function definition will generate an error?

CREATE FUNCTION [dbo].[fnCheckDate] ()
RETURNS DATETIME
AS
BEGIN
DECLARE @Today DATETIME
SET @Today = GETDATE()
SET @Today = DATEADD(DD, DATEDIFF(DD, 0, @Today), 0)
RETURN DATEADD(DD, 1, @Today)
END

A. SQL Server 2000
B. SQL Server 2005
C. SQL Server 2008
D. No error is generated. The function will be created for all versions.
E. Error will be generated for all versions.

Answer:[A]
Explanation:The GETDATE() function, and any non-deterministic functions, are not allowed in a user-defined function of SQL Server 2000.
Highlight to find out the answer.

Trigger tables

How many system tables can you use in a trigger for INSERT, UPDATE, and DELETE?
To identify records that were updated in a table from a trigger, which of the following tables need to be investigated?

A. Inserted system table
B. Updated system table
C. Deleted system table
D. Inserted and Deleted system tables

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

SMALLDATETIME data type

Which of the following is NOT true about the SMALLDATETIME data type?

A. SQL Server stores SMALLDATETIME values as two 2-byte integers.
B. The first 2 bytes store the number of days after January 1, 1900
C. The second byte store the number of seconds since midnight.
D. Dates range from January 1, 1900 through June 6, 2079.

Answer:[C]
Explanation: SMALLDATETIME data type doesn't contain second part.
Highlight to find out the answer.

CREATE FUNCTION

Which part of the following function definition will generate an error?

CREATE FUNCTION dbo.CreateContact(
@FirstName VARCHAR(50),
@LastName VARCHAR(50))
RETURNS INT
AS
BEGIN
DECLARE @ContactName VARCHAR(20)

SET @ContactName = LEFT(@FirstName, 1) + @LastName

INSERT INTO dbo.Contact(ContactName, FirstName, LastName )
VALUES (@ContactName, @FirstName, @LastName )

RETURN SCOPE_IDENTITY()
END


A. SET @ContactName = LEFT(@FirstName, 1) + @LastName
B. INSERT INTO dbo.Contact
C. RETURN SCOPE_IDENTITY()
D. No error is generated. The function will be created and compiled.

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

CAST with SMALLDATETIME

What will be the result of the following statement?

SELECT CAST(-1 AS SMALLDATETIME)


A. 1900-01-01 00:00:00.000
B. 1899-01-01 00:00:00.000
C. 1899-12-31 00:00:00.000
D. 1752-01-01 00:00:00.000
E. 1752-12-31 00:00:00.000
F. The system will generate an error. Arithmetic overflow error converting expression to data type smalldatetime.

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

Reserved Keywords

Which of the following is NOT a reserved keyword in SQL Server and therefore can be used as an object name or as a column name in a table without using delimiters?

A. CREATE
B. GOTO
C. GO
D. BEGIN
E. END
F. None of the above. All of these are reserved keywords in SQL Server.

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

Fixed database role

Do you recognize fixed database roles and fixed server roles?
Which of the following fixed database roles can add or remove user IDs?

A. db_accessadmin
B. db_securityadmin
C. db_setupadmin
D. db_sysadmin

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

DATEADD

Do you remember DATEADD and DATEDIFF functions?
What does the following statement do?

SELECT DATEADD(DD, 0, DATEDIFF(DD, 0, GETDATE()))

A. Return the current system date unchanged.
B. Return just the date part of the current system date removing the time.
C. Return the integer value of the current system date.
D. The statement will generate an error. The second parameter of the DATEDIFF function should be a DATETIME data type.

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

Find output

What will be the output of the following statement?

SELECT LEN(CAST(LEFT('026-100', 3) AS INT))

A. 2
B. 3
C. 7
D. Statement will generate an error.

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

securityadmin fixed server role

Which of the following is NOT a valid description of the securityadmin fixed server role?

A. Manages server logins.
B. Adds, removes and configures linked servers.
C. Has permission to execute the sp_password stored procedure for all users other than members of the sysadmin role.
D. Can read the error log.

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

Numeric data type

Which of the following data types has the least data type precedence?

A. BIGINT
B. FLOAT
C. DECIMAL
D. MONEY
E. REAL
F. NUMERIC

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

DATETIME Conversion

What will be the value of @Today in the following script?

DECLARE @Today VARCHAR(10)
SET @Today = '02/02/2010'
SET @Today = @Today + 8

A. 02/10/2010
B. 10/02/2010
C. 02/02/2018
D. An error is generated.

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

BIT data type

Do you remember BIT data type?
What will be the value of the @Bit variables in the following?

DECLARE @Bit BIT
SET @Bit = -1

A. 0
B. -1
C. 1
D. An error will be encountered. Only 1, 0 and NULL values are allowed for a bit data type.

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

SMALLDATETIME data type

Do you remember SMALLDATETIME data type?
What is the maximum date value that can be stored in a SMALLDATETIME data type?

A. June 6, 2079
B. July 6, 2079
C. December 31, 2079
D. December 31, 9999

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

Format date with CONVERT()

Do you remember the CONVERT function for date/time value?
Which of the following scripts return the date value as YYYY/MM/DD?

A. SELECT CONVERT(VARCHAR(10), GETDATE(), 101)
B. SELECT CONVERT(VARCHAR(10), GETDATE(), 102)
C. SELECT CONVERT(VARCHAR(10), GETDATE(), 111)
D. SELECT CONVERT(VARCHAR(10), GETDATE(), 112)

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

Fixed server role?

Do you remember fixed server roles?
Which of the following is NOT a fixed server role?

A. setupadmin
B. diskadmin
C. securityadmin
D. accessadmin

Answer:[D]
Explanation: setupadmin, diskadmin and securityadmin are all valid fixed server roles. On the other hand, accessadmin is not a valid fixed server role.
Highlight to find out the answer.

Find output

Do you remember the STR function?
What will be the result of the following statement?

SELECT STR(-2010.87, 6, 1)

A. -2011
B. -2010.9
C. -2010.8
D. -2010

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

Find output

What will be the output of the following statement?
SELECT CHARINDEX('in', 'finished testing', 0)
A. 0
B. 2
C. NULL
D. Statement will generate an error. The CHARDINDEX function only accepts 2 parameters.

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

True or False

Have you ever used varchar(max) or varbinary(max) in your database?
The maximum sizes of varchar(max) and varbinary(max) is the same in SQL Server 2005?

A. True
B. False

Answer:[A]
Explanation: 2^31-1

Highlight to find out the answer.

MySQL Quiz

What is the maximum length of a table name, a database name, index name, constraint name, stored procedure name, or a field name in MySQL?

A. 256
B. 50
C. 64
D. 128

Answer: C
Highlight to find out the answer.

SQL Server Quiz

Which of the following is NOT true about the PRIMARY KEY constraint?

A. Can be of more than 1 column.
B. Always created as CLUSTERED.
C. Enforces data uniqueness by creating a unique index for the primary key columns.
D. Column that participates in the PRIMARY KEY constraint cannot accept NULL values.

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

Display fragmentation info on data and indice of a table

Use DBCC SHOWCONTIG statement on heavily modified tables or tables that contain imported data or tables that make the query performance very poor.

DECLARE @TableId INT
SET @TableId = OBJECT_ID('Your table name')
DBCC SHOWCONTIG(@TableId);

Use a DEFAULT for several columns

Sometimes you need to set a default value for multiple columns. Why don't you create a DEFAULT and bind it to them instead of setting default value one by one.
CREATE TABLE tblTest1(Status varchar(10));
GO
CREATE TABLE tblTest2(Status varchar(10));
GO
CREATE DEFAULT dfStatus AS 'Active';
GO
-- Bind default
EXEC sp_bindefault N'dfStatus', N'tblTest1.Status';
EXEC sp_bindefault N'dfStatus', N'tblTest2.Status';
-- Unind default
EXEC sp_unbindefault N'tblTest1.Status';
EXEC sp_unbindefault N'tblTest2.Status';

Get Oracle system events

SELECT EVENT,
 TOTAL_WAITS,
 TOTAL_TIMEOUTS,
 TIME_WAITED,
 ROUND(AVERAGE_WAIT,2) AVAREAGE_WAIT
FROM v$system_event
ORDER BY TOTAL_WAITS

Get all running queries in MySQL

Sometimes you need to determine which queries are running too long in MySQL db, this script may help you:
SHOW PROCESSLIST; (get first 100 characters of the query)
OR
SHOW FULL PROCESSLIST; for more detail in the query

Get spaced used of all tables

DECLARE @TableName sysname
DECLARE @SpaceUsed TABLE(TableName sysname,
   Rows INT,
   Reserved VARCHAR(50),
   Data VARCHAR(50),
   IndexSize VARCHAR(50),
   Unused VARCHAR(50))
DECLARE Cur CURSOR FOR
 SELECT name
 FROM sysobjects
 WHERE type = 'U'
OPEN Cur
FETCH FROM Cur
INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
 INSERT INTO @SpaceUsed
 EXEC sp_spaceused @TableName
 FETCH NEXT FROM Cur
 INTO @TableName
END
CLOSE Cur
DEALLOCATE Cur
SELECT * FROM @SpaceUsed

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")')