Yesterday I wrote the SQL Server Quiz, Can You Answer All These? post and asked 10 question. Today I will give you the answes

1) Name three differences between primary keys and unique constraints
A primary key cannot have any null values and a unique constraint can have one null value
A primary key is by default clustered and a unique constraint is not
A table can only have one primary key but can have more than one unique constraint

2) If your database is in simple recovery model and you run code that looks like this

T-SQL
1
2
3
4
5
6
7
BULK INSERT Northwind.dbo.[Order Details]
   FROM 'f:orderslineitem.tbl'
   WITH 
      (
         FIELDTERMINATOR = '|',
         ROWTERMINATOR = '|n'
      )
BULK INSERT Northwind.dbo.[Order Details]
   FROM 'f:orderslineitem.tbl'
   WITH 
      (
         FIELDTERMINATOR = '|',
         ROWTERMINATOR = '|n'
      )

Will this be minimally logged?

There are some additional things you need to do before a bulk insert is minimally logged, one of them would be that you would need to lock the table.

Here is an example

T-SQL
1
2
3
4
5
6
7
8
BULK INSERT Northwind.dbo.[Order Details]
   FROM 'f:orderslineitem.tbl'
   WITH 
      (
         FIELDTERMINATOR = '|',
         ROWTERMINATOR = '|n',
     TABLOCK
      )
BULK INSERT Northwind.dbo.[Order Details]
   FROM 'f:orderslineitem.tbl'
   WITH 
      (
         FIELDTERMINATOR = '|',
         ROWTERMINATOR = '|n',
	 TABLOCK
      )

There is actually more that is required, here is what books on line specifies about it

A minimally logged bulk copy can be performed if all of these conditions are met:

  • The recovery model is simple or bulk-logged.
  • The target table is not being replicated.
  • The target table does not have any triggers.
  • The target table has either 0 rows or no indexes.
  • The TABLOCK hint is specified.

3) How many flaws/worst practices are in this piece of code

T-SQL
1
2
3
4
5
6
select * 
from SomeTable
Where left(SomeColumn,1) ='A'
 
print 'query executed'
select @@rowcount as 'Rows returned'
select * 
from SomeTable
Where left(SomeColumn,1) ='A'

print 'query executed'
select @@rowcount as 'Rows returned'

There are a couple of things that stand out
You should never use * but only list the columns you really want, see also here: Don’t use * but list the columns
There is a missing qualifier in the table name
This where clause is not sargable instead of left(SomeColumn,1) =’A’ do this SomeColumn like ‘A%’. see also here: Functions on left side of the operator
@@ROWCOUNT should be selected right after the query because the print will make it have a value of 0; any statement will affect @@ROWCOUNT so you should always dump it into a variable right after you do a DML statement. The same that applies to @@ROWCOUNT also applies to @@ERROR. If you do need to grab both @@ERROR and @@rowcount then do it on the same line. SELECT @MyErr =@@ERROR, @MyCount = @@ROWCOUNT

4) When we use Try and Catch will the following tran be commited?

T-SQL
1
2
3
4
5
6
7
8
9
BEGIN TRANSACTION TranA
    BEGIN TRY
     DECLARE  @cond INT;
     SET @cond =  'A';
    END TRY
    BEGIN CATCH
     PRINT 'Inside catch'
    END CATCH;
    COMMIT TRAN TranA
BEGIN TRANSACTION TranA
    BEGIN TRY
     DECLARE  @cond INT;
     SET @cond =  'A';
    END TRY
    BEGIN CATCH
     PRINT 'Inside catch'
    END CATCH;
    COMMIT TRAN TranA

No this will result in a doomed transaction and you will see the following message: Msg 3930, Level 16, State 1, Line 15 The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction. Server: Msg 3998, Level 16, State 1, Line 1 Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.

Before I show you what you can do first run this

T-SQL
1
2
3
4
5
6
7
8
9
BEGIN TRANSACTION TranA
    BEGIN TRY
     DECLARE  @cond INT;
     SET @cond =  1/0;
    END TRY
    BEGIN CATCH
     PRINT 'Inside catch'
    END CATCH;
    COMMIT TRAN TranA
BEGIN TRANSACTION TranA
    BEGIN TRY
     DECLARE  @cond INT;
     SET @cond =  1/0;
    END TRY
    BEGIN CATCH
     PRINT 'Inside catch'
    END CATCH;
    COMMIT TRAN TranA

See that was no problem at all, the first code blew up because it is a non trapable error. You can use XACT_STATE() to see what state the transaction is in

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
BEGIN TRANSACTION TranA
    BEGIN TRY
     DECLARE  @cond INT;
     SET @cond = 'A';
    END TRY
    BEGIN CATCH
     PRINT ERROR_MESSAGE();
    END CATCH;
    IF XACT_STATE() =0
    BEGIN
     COMMIT TRAN TranA
    END
    ELSE
    BEGIN
     ROLLBACK TRAN TranA
    END
BEGIN TRANSACTION TranA
    BEGIN TRY
     DECLARE  @cond INT;
     SET @cond = 'A';
    END TRY
    BEGIN CATCH
     PRINT ERROR_MESSAGE();
    END CATCH;
    IF XACT_STATE() =0
    BEGIN
     COMMIT TRAN TranA
    END
    ELSE
    BEGIN
     ROLLBACK TRAN TranA
    END

To learn more about errors and transactions I highly recommend these two links by Erland Sommarskog: Implementing Error Handling with Stored Procedures and Error Handling in SQL Server – a Background.

5)Take a look at the code below, what will the last select return?

T-SQL
1
2
3
4
5
6
declare @SQL varchar(100)
declare @Val varchar(10)
 
select @SQL ='The value this item is..'
 
select @SQL + isnull(@Val,' currently not available')
declare @SQL varchar(100)
declare @Val varchar(10)

select @SQL ='The value this item is..'

select @SQL + isnull(@Val,' currently not available')

Running that code will return the following: The value this item is.. currently. This is because isnull looks at @Val which is varchar(10) and chops off everything after 10 characters. If you use coalesce then you don’t have this problem, run the following

T-SQL
1
2
3
4
5
6
declare @SQL varchar(100)
declare @Val varchar(10)
 
select @SQL ='The value this item is..'
 
select @SQL + coalesce(@Val,' currently not available')
declare @SQL varchar(100)
declare @Val varchar(10)

select @SQL ='The value this item is..'

select @SQL + coalesce(@Val,' currently not available')

And now this is returned The value this item is.. currently not available

6)What will the returned when you run the following query?

T-SQL
1
select 3/2
select 3/2

So running that returns 1, surprised? Don’t be the result of division with two integers is an integer, this is also known as integer math

Here is how you can fix it by doing explicit and implicit conversions

T-SQL
1
2
3
4
    --Implicit
    SELECT 3/(2*1.0)
    --Explicit
    SELECT CONVERT(DECIMAL(18,4),3)/2
    --Implicit
    SELECT 3/(2*1.0)
    --Explicit
    SELECT CONVERT(DECIMAL(18,4),3)/2

7)How many rows will the select query return from the table with 3 rows

T-SQL
1
2
3
4
5
6
7
CREATE TABLE #testnulls (ID INT)
INSERT INTO #testnulls VALUES (1)
INSERT INTO #testnulls VALUES (2)
INSERT INTO #testnulls VALUES (null)
 
select * from #testnulls
where id <> 1
CREATE TABLE #testnulls (ID INT)
INSERT INTO #testnulls VALUES (1)
INSERT INTO #testnulls VALUES (2)
INSERT INTO #testnulls VALUES (null)

select * from #testnulls
where id <> 1

The answer is one row, the reason for that is that a null is not equal to anything not even another null

Run this to see what I mean

T-SQL
1
2
3
4
5
6
7
8
9
if null = null
print 'yes null = null'
else
print 'no null = null'
 
if null is null
print 'yes null is null'
else
print 'no null is null'
if null = null
print 'yes null = null'
else
print 'no null = null'

if null is null
print 'yes null is null'
else
print 'no null is null'

The following will be printed
no null = null
yes null is null

To check for null you would use IS NULL and IS NOT NULL or NOT IS NULL, you would not use = NULL or <> NULL

8)If you run the code below what will the len function return, can you also answer why?

T-SQL
1
2
3
4
declare @v varchar(max)
select @v =replicate('a',20000)
 
select len(@v)
declare @v varchar(max)
select @v =replicate('a',20000)

select len(@v)

8000 will be returned because ‘a’ is a varchar which goes up to 8000 max. Here is one way to get around it

T-SQL
1
2
3
4
declare @v varchar(max)
select @v =replicate(convert(varchar(max),'a'),20000)
 
select len(@v)
declare @v varchar(max)
select @v =replicate(convert(varchar(max),'a'),20000)

select len(@v)

9) If you have the following table

T-SQL
1
2
3
4
CREATE TABLE #testnulls2 (ID INT)
INSERT INTO #testnulls2 VALUES (1)
INSERT INTO #testnulls2 VALUES (2)
INSERT INTO #testnulls2 VALUES (null)
CREATE TABLE #testnulls2 (ID INT)
INSERT INTO #testnulls2 VALUES (1)
INSERT INTO #testnulls2 VALUES (2)
INSERT INTO #testnulls2 VALUES (null)

what will the query below return?

T-SQL
1
2
select count(*), count(id)
from #testnulls2
select count(*), count(id)
from #testnulls2

This will return 3 and 2. this is because count(*) counts all the columns and count(id) will only count the non null values in a column

10)If you have the following two tables

T-SQL
1
2
3
4
5
6
7
8
CREATE TABLE TestOne (id INT IDENTITY,SomeDate DATETIME)
CREATE TABLE TestTwo (id INT IDENTITY,TestOneID INT,SomeDate DATETIME)
 
    --Let's insert 4 rows into the table
    INSERT TestOne VALUES(GETDATE())
    INSERT TestOne VALUES(GETDATE())
    INSERT TestOne VALUES(GETDATE())
    INSERT TestOne VALUES(GETDATE())
CREATE TABLE TestOne (id INT IDENTITY,SomeDate DATETIME)
CREATE TABLE TestTwo (id INT IDENTITY,TestOneID INT,SomeDate DATETIME)
 
    --Let's insert 4 rows into the table
    INSERT TestOne VALUES(GETDATE())
    INSERT TestOne VALUES(GETDATE())
    INSERT TestOne VALUES(GETDATE())
    INSERT TestOne VALUES(GETDATE())

If table TestOne now has the following trigger added to it

T-SQL
1
2
3
4
5
6
7
8
9
CREATE TRIGGER trTestOne ON [dbo].[TestOne]
    FOR INSERT
    AS
    DECLARE @CreditUserID INT
 
    SELECT @CreditUserID = (SELECT ID FROM Inserted)
 
    INSERT TestTwo VALUES(@CreditUserID,GETDATE())
    GO
CREATE TRIGGER trTestOne ON [dbo].[TestOne]
    FOR INSERT
    AS
    DECLARE @CreditUserID INT
 
    SELECT @CreditUserID = (SELECT ID FROM Inserted)
 
    INSERT TestTwo VALUES(@CreditUserID,GETDATE())
    GO

What will be the value that the @@identity function returns after a new insert into the TestOne table?

T-SQL
1
2
INSERT TestOne VALUES(GETDATE())
select @@identity
INSERT TestOne VALUES(GETDATE())
select @@identity

You will get back the value 1 and not 5. This is because @@IDENTITY doesn’t care about scope and returns the last identity value from all the statements, which in this case is from the code within the trigger trTestOne. So the bottom line is this: Always use SCOPE_IDENTITY() unless you DO need the last identity value regradless of scope (for example you need to know the identity from the table insert inside the trigger)
Run this now and you will see both values and you can see that they are indeed different

T-SQL
1
2
INSERT TestOne VALUES(GETDATE())
select scope_identity(), @@identity
INSERT TestOne VALUES(GETDATE())
select scope_identity(), @@identity

So that is it for all 10 questions

*** If you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum