Thursday, July 29, 2010

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.

Tuesday, July 27, 2010

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

Saturday, July 24, 2010

Defragment indexes

You are writing a new stored procedure to perform maintenance on your SQL Server 2005 databases that defragment the indexes in an online manner. What command should you use?

A. DBCC DBREINDEX
B. DBCC INDEXDEFRAG
C. ALTER INDEX with the REORGANIZE option
D. ALTER INDEX with the LOB_COMPACTION option set to OFF

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

Thursday, July 22, 2010

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.

Page size again

Do you remember pages in SQL Server?
Your SQL Server database log file is 5MB. How many pages are allocated to it?

A. 0
B. 640
C. 320
D. 80
E. It depends

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

Page size

Do you remember pages in SQL Server?
In SQL Server, the page size is _____

A. 1KB
B. 8KB
C. 16KB
D. 64KB
E. 125KB
F. 256KB

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

Tuesday, July 20, 2010

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.

Access To MySQL

Access To MySQL: "Access to MySQL is a small program that will convert Microsoft Access Databases to MySQL.

* Wizard interface.
* Transfer data directly from one server to another.
* Create a dump file.
* Select tables to transfer.
* Select fields to transfer.
* Transfer password protected databases.
* Supports both shared security and user-level security.
* Optional transfer of indexes.
* Optional transfer of records.
* Optional transfer of default values in field definitions.
* Identifies and transfers auto number field types.
* Command line interface.
* Easy install, uninstall and upgrade.

- Sent using Google Toolbar"

Monday, July 19, 2010

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.

Sunday, July 18, 2010

Find output

What will be the output of the following scripts?

CREATE TABLE #tblTest(TestValue VARCHAR(2) COLLATE French_CI_AI) 
DECLARE @Var1 CHAR(1), @Var2 CHAR(1)
SET @Var1='A'

WHILE @Var1 <= 'Z' 
BEGIN
 SET @Var2='A'
 WHILE @Var2 <= 'Z'
 BEGIN
  INSERT INTO #tblTest SELECT @Var1 + @Var2
  SET @Var2 = CHAR(ASCII(@Var2) + 1)
 END
 SET @Var1 = CHAR(ASCII(@Var1) + 1)
END

SELECT DISTINCT TestValue FROM #tblTest
WHERE TestValue LIKE '_'

DROP TABLE #tblTest
A. 0
B. 1
C. 26
D. 676 = 26*26
E. It depends on tempdb collation
F. Error is generated

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

Get all valid collations

You can execute the system function fn_helpcollations to retrieve a list of all the valid collation names for Windows collations and SQL Server collations.
SELECT *
FROM fn_helpcollations()

Monday, July 12, 2010

Get all database sizes on server

DECLARE @DBSize TABLE([DatabaseName] [nvarchar](75) NOT NULL,
      [Size] [decimal] NOT NULL,
      [Name] [nvarchar](75) NOT NULL,
      [Filename] [nvarchar](90) NOT NULL);
DECLARE @FixedDrive TABLE ([Drive] [char](1) NOT NULL,
      [MBFree] [decimal] NOT NULL);

INSERT INTO @DBSize
EXEC sp_MSforeachdb 'SELECT ''?'' as DatabaseName, 
      CASE WHEN [?]..sysfiles.size * 8 / 1024 = 0 THEN 1 
       ELSE [?]..sysfiles.size * 8 / 1024 END AS size, 
      [?]..sysfiles.name, 
      [?]..sysfiles.filename 
     FROM [?]..sysfiles';

INSERT INTO @FixedDrive
EXEC xp_fixeddrives;

SELECT RTRIM(CAST(DatabaseName AS VARCHAR(75))) DatabaseName,
        Drive,Filename, Cast(Size AS INT) Size,
        Cast(MBFree AS VARCHAR(10)) MB_Free
FROM @DBSize t1
 INNER JOIN @FixedDrive t2 ON LEFT(t1.Filename, 1) = t2.Drive
GROUP BY DatabaseName, Drive, MBFree, Filename, Cast(Size AS INT)
ORDER BY Drive, Size DESC;

SELECT Drive [Total Space Used],
        Cast(Sum(Size) AS VARCHAR(10)) [Total Size],
        Cast(MBFree AS VARCHAR(10)) [MB Free]
FROM @DBSize t1
        INNER JOIN @FixedDrive t2 ON LEFT(t1.Filename, 1) = t2.Drive
GROUP BY Drive, MBFree;

Create database from mdf file

USE [master]

GO
CREATE DATABASE [TestDB] ON
( FILENAME = N'D:\temp\testdb.mdf' )
FOR ATTACH

SQL Injection Prevention Cheat Sheet - OWASP

SQL Injection Prevention Cheat Sheet - OWASP: "SQL Injection Prevention Cheat Sheet

- Sent using Google Toolbar"

Get the last day of the current month

This query runs only in SQLite.
SELECT date('now','start of month','+1 month','-1 day');

Sunday, July 11, 2010

Read file content

Load file content
SELECT LOAD_FILE("D:/Temp/test.txt");
Copy a file to another one
SELECT LOAD_FILE("D:/Temp/test.txt") INTO OUTFILE "D:/Temp/test2.txt"

Database Design - Introduction

Database Design - Introduction: "Database design with UML and SQL, 3rd edition

Also available on tomjewett.com: color tutorial, demo application, and video; for Web accessibility resources and consulting, please see The Enabled Web.

- Sent using Google Toolbar"

Ten of the Biggest Mistakes Developers Make With Databases — Developer.com

Ten of the Biggest Mistakes Developers Make With Databases — Developer.com: "Ten of the Biggest Mistakes Developers Make With Databases

- Sent using Google Toolbar"

Saturday, July 10, 2010

OpenXML

What will be the output of the following scripts?

DECLARE @xml XML,@hdoc INT
SELECT @xml = '<myxml>
<value id = "1" name = "SQL Server">
 <category>2</category>
</value>
<value id = "2" name = "Oracle">
 <category>3</category>
</value>
</myxml>'
EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml
SELECT DISTINCT category 
FROM OPENXML (@hdoc, '/myxml/value',1)
WITH (id INT, name VARCHAR(10), category INT)
EXEC sp_xml_removedocument @hdoc

A. NULL
B. 3,2
C. 2,3
D. Error: Incorrect syntax near /value

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

Thursday, July 8, 2010

Get user connections

Sometimes you need to determine who is connecting to the database by using which applications, so I think you can use this script.

SELECT t1.session_id, t1.local_tcp_port
           , t2.login_name, t2.host_name, t2.program_name
FROM sys.dm_exec_connections t1  
     INNER JOIN sys.dm_exec_sessions t2 on t1.session_id = t2.session_id

Tuesday, July 6, 2010

Delete duplicate records

Use this technique with small table only such as this example.^_^

DECLARE @Test TABLE (id int identity(1,1), a int, b int, c int)
INSERT INTO @Test(a,b,c)
SELECT 1,2,3 
UNION ALL SELECT 1,2,3 
UNION ALL SELECT 1,2,3
UNION ALL SELECT 2,3,4
UNION ALL SELECT 2,3,4
UNION ALL SELECT 3,4,5
UNION ALL SELECT 4,5,6
UNION ALL SELECT 4,5,6
UNION ALL SELECT 4,5,6
DELETE t2
FROM @Test t1
   INNER JOIN @Test t2 ON t1.a = t2.a AND t1.b = t2.b AND t1.c = t2.c
      AND t2.id > t1.id;
SELECT * FROM @Test

Monday, July 5, 2010

SQL Server quiz

You are creating a stored procedure that will delete data from the Contact table in a SQL Server 2005 database. The stored procedure includes the following Transact-SQL statement to handle any errors that occur.

BEGIN TRY 
 BEGIN TRAN
 DELETE FROM Person.Contact 
 WHERE ContactID = @ContactID 
 COMMIT TRAN
END TRY
BEGIN CATCH 
 DECLARE @ErrorMessage nvarchar(2000) 
 DECLARE @ErrorSeverity int 
 DECLARE @ErrorState int 
 SELECT @ErrorMessage = ERROR_MESSAGE(), 
  @ErrorSeverity = ERROR_SEVERITY(), 
  @ErrorState = ERROR_STATE() 
  RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState) 
END CATCH;
You test the stored procedure and discover that it leaves open transactions. You need to modify the stored procedure so that it properly handles the open transactions. What should you do?

A. Add a COMMIT TRAN command to the CATCH block.
B. Remove the COMMIT TRAN command from the TRY block.
C. Add a ROLLBACK TRAN command to the CATCH block.
D. Add a ROLLBACK TRAN command to the TRY block.

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

Saturday, July 3, 2010

#Oracle #quiz

How many times does the following loop execute?
FOR iYear IN REVERSE  12 .. 1
LOOP
   CalculateSales(iYear);
END LOOP

A. 12
B. 11
C. 0
D. Error will occur

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

Generate a thousand daily dates starting today

CREATE TABLE test.ints(i tinyint);
INSERT INTO test.ints VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 
SELECT CURDATE() + INTERVAL t1.i*100 + t2.i*10 + t3.i DAY AS Date
FROM test.ints AS t1 
  JOIN test.ints AS t2
  JOIN test.ints AS t3
WHERE ( t1.i*100 + t2.i*10 + t3.i ) < 1000
ORDER BY Date;