Interview Q & A

Q. Execute an Operating System Command From Within SQL Server
A. The xp_cmdshell extended store procedure makes it possible to execute operating system
commands from within SQL Server.
Example:
EXEC Master..xp_cmdshell 'Dir c:\'

Q. How can I create a plain-text flat file from SQL Server as input to another
application?
A. One of the purposes of Extensible Markup Language (XML) is to solve challenges like this,
but until all
applications become XML-enabled, consider using our faithful standby, the bulk copy program
(bcp) utility.
This utility can do more than just dump a table; bcp also can take its input from a view
instead of from a table. After you specify a view as the input source, you can limit the output
to a subset of columns or to a subset of rows by selecting appropriate filtering (WHERE and
HAVING) clauses.
More important, by using a view, you can export data from multiple joined tables. The only
thing you cannot do is specify the sequence in which the rows are written to the flat file,
because a view does not let you include an ORDER BY clause in it unless you also use the TOP
keyword.
If you want to generate the data in a particular sequence or if you cannot predict the
content of the data you want to export, be aware that in addition to a view, bcp also supports
using an actual query. The only "gotcha" about using a query instead of a table or view is that
you must specify queryout in place of out in the bcp command line.
For example, you can use bcp to generate from the pubs database a list of authors who reside
in California by writing the following code:
bcp "SELECT * FROM pubs..authors WHERE state = 'CA'" queryout c:\CAauthors.txt -c -T -S

Q. How can I programmatically detect whether a given connection is blocked?
A. A connection is blocked when it requires an object that another connection has a lock on.
You can use the system stored procedure sp_lock to retrieve information about the current
locks in SQL Server, and you can use the server process ID (SPID) to filter the information
that sp_lock returns. To determine whether a given process is waiting for the release of a
locked resource, you can execute the sp_GetBlockInfo procedure that follows.
Note: You must execute the procedure before the timeout.
USE master
GO
CREATE PROCEDURE sp_GetBlockInfo
@BlockedSPID as int
AS
IF EXISTS (select *
FROM master.dbo.syslockinfo
WHERE req_spid = @BlockedSPID
AND req_status = 3)
SELECT sli1.req_spid AS SPID,
SUBSTRING (u.name, 1, 8) As Mode,
DB_NAME(sli1.rsc_dbid) AS [Database],
OBJECT_NAME(sli1.rsc_objid) AS [Table],
sli1.rsc_Text AS [Resource]
FROM master.dbo.syslockinfo sli1
JOIN master.dbo.spt_values u
ON sli1.req_mode + 1 = u.number
AND u.type = 'L'
JOIN
master.dbo.syslockinfo sli2
ON sli1.rsc_dbid = sli2.rsc_dbid
AND sli1.rsc_objid = sli2.rsc_objid
AND sli1.rsc_text = sli2.rsc_text
WHERE sli2.req_spid = @BlockedSPID
AND sli1.req_status = 1
AND sli1.req_spid <> @BlockedSPID
AND sli2.req_status = 3
ELSE
SELECT CAST(1 as int) AS SPID,
SUBSTRING ('', 1, 8) AS Mode,
DB_NAME(NULL) AS [Database],
OBJECT_NAME(NULL) AS [Table],
CAST(NULL AS nchar(32)) AS [Resource]
WHERE 1=2
GO
The sp_GetBlockInfo procedure tells you the lock mode, the database and object names of the
locked resource, and in the case of a blocking chain, which SPID is the root blocker. If the
process is not blocked,
sp_GetBlockInfo returns an empty recordset.
You can also detect blocks by checking for error 1222, "Lock request time out period
exceeded." The LOCK_TIMEOUT setting controls how long a process will wait for locks to be
released before timing out. When the lock timeout occurs, SQL Server sends error 1222 to the
application. In SQL Server 7.0, this error aborts the statement but does not cause the batch to
roll back, so you can look for the Transact-SQL system variable @@ERROR and determine
where locks exist

Q. Can you create UNIQUE and PRIMARY KEY constraints on computed columns in SQL Server
2000?
A. In SQL Server, the physical mechanism that UNIQUE and PRIMARY KEY constraints use to
enforce uniqueness is a unique index. Because SQL Server 2000 supports indexes on
computed columns,
you can create UNIQUE and PRIMARY KEY constraints on computed columns.
Defining a UNIQUE constraint on a computed column is a straightforward process, as the
following example shows:
CREATE TABLE T1 (
col1 int NOT NULL,
col2 AS col1 + 1 UNIQUE
)
However, if you define a PRIMARY KEY on a computed column, such as:
CREATE TABLE T2 (
col1 int NOT NULL,
col2 AS col1 + 1 PRIMARY KEY
)

you receive the following error:
Server: Msg 8111, Level 16, State 2, Line 1
Cannot define PRIMARY KEY constraint on nullable column in table 'T2'.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.

Because of the primary key constraint, SQL Server requires you to guarantee that your
computation's
result will not be NULL. The computation in the computed column can overflow
(for example, when you add 1 to the largest integer) or underflow (when you subtract 1 from
the smallest integer), and other computations can result in a divide-by-zero error. However, if
the ARITHABORT
(which determines whether a query has ended when an overflow or a divide-by-zero error
occurs)
and ANSI_WARNINGS (which specifies ANSI SQL-92 standard behavior for several error
conditions)
session settings are off, instead of ending the query, the computation can have a NULL result.

In practice, when either ARITHABORT or ANSI_WARNINGS settings is off, you cannot
create an index on a computed column or insert values into a table that has an index on a
computed column because SQL Server detects such an attempt and returns an error. But SQL
Server still requires you to guarantee that the computation will not result in NULL values. The
trick is to wrap the computed column's computation with the ISNULL() function and supply an
alternative value if the computation results in NULL:
CREATE TABLE T2 (
col1 int NOT NULL,
col2 AS ISNULL(col1 + 1, 0) PRIMARY KEY
)

Q. Why does my inline or embedded SQL run faster than my stored procedures?
A. Recompilations might be the source of the slower stored procedure speed. To find out for
sure, you need to do some performance investigation, such as looking at Showplans for each
type of query versus calling the stored procedures and comparing query plan cache hits to
cache misses. You can also try coding the object owner for referenced tables, views, and
procedures inside your stored procedures, as the following example shows:
SELECT * FROM dbo.mytable
This technique helps you reuse plans and prevent cache misses.

Q. Why doesn't SQL Server permit an ORDER BY clause in the definition of a view?
Q. Execute an Operating System Command From Within SQL Server
A. SQL Server excludes an ORDER BY clause from a view to comply with the ANSI SQL-92
standard. Because analyzing the rationale for this standard requires a discussion of the
underlying structure of the structured query language (SQL) and the mathematics upon which
it is based, we can't fully explain the restriction here. However, if you need to be able to
specify an ORDER BY clause in a view, consider using the following workaround:
USE pubs
GO
CREATE VIEW AuthorsByName
AS
SELECT TOP 100 PERCENT *
FROM authors
ORDER BY au_lname, au_fname
GO
The TOP construct, which Microsoft introduced in SQL Server 7.0, is most useful when you
combine it with the ORDER BY clause. The only time that SQL Server supports an ORDER BY
clause in a view is when it is used in conjunction with the TOP keyword.
Note that the TOP keyword is a SQL Server extension to the ANSI SQL-92 standard.

Q. Is using the TOP N clause faster than using SET ROWCOUNT N to return a specific number
of rows from a query?
A. With proper indexes, the TOP N clause and SET ROWCOUNT N statement are equally fast,
but with unsorted input from a heap, TOP N is faster. With unsorted input, the TOP N operator
uses a small internal sorted temporary table in which it replaces only the last row. If the input
is nearly sorted, the TOP N engine must delete or insert the last row only a few times. Nearly
sorted means you're dealing with a heap with ordered inserts for the initial population and
without many updates, deletes, forwarding pointers, and so on afterward. A nearly sorted
heap is more efficient to sort than sorting a huge table. In a test that used TOP N to sort a
table with the same number of rows but with unordered inserts, TOP N was not as efficient
anymore. Usually, the I/O time is the same both with an index and without; however, without
an index SQL Server must do a complete table scan. Processor time and elapsed time show
the efficiency of the nearly sorted heap. The I/O time is the same because SQL Server must
read all the rows either way.

Q, The Difference between 'Count' and 'Count(*)'
A. 'Count': Counts the number of non-null values.
'Count(*)': Counts the number of rows in the table, including null values and duplicates.

Q. I have two tables t1 and t2 both with the columns a1, a2. I want to find the
difference of (the set of t1) -(the set of t2) without using the keyword EXCEPT
because MSSQL 2000 does not recognize that word. I have tried this query but it
does not give me what I want: SELECT * FROM t1 WHERE NOT EXISTS (SELECT t1.*
FROM t1 INNER JOIN t2 ON t1.a1=t2.a1 AND t1.a2=t2.a2)
A.SELECT * FROM t1 WHERE NOT EXISTS (SELECT * FROM t2 ON t1.a1=t2.a1 AND
t1.a2=t2.a2)
This is the solution. The difference of (the set of t1) - (the set of t2) is
SELECT * FROM t1 LEFT OUTER JOIN t2 ON t1.a1=t2.a1 AND t1.a2=t2.a2 WHERE t2.a1 IS
NULL AND t2.a2 IS NULL
According to SQL Query Analyzer, this is slightly more efficient than the left join (possibly only
because
of the tables I tested it with):
SELECT * FROM t1 WHERE NOT EXISTS (SELECT * FROM t2 WHERE t1.a1=t2.a1 AND
t1.a2=t2.a2)

Q, Method to Perform Case Sensitive Searches in SQL Server
A, By default, SQL Server 7.0 installation sets up SQL Server to run in a case insensitive
mode. For most applications this may work great, but there are certain situations were case
sensitive searches are required. For instance, if a web site needs to have passwords that are
case sensitive a method needs to be devised to perform case-sensitive queries. The following
script performs a case-sensitive search. First let’s create a table:
CREATE TABLE test
(
id INT NOT NULL,
msg VARCHAR(100) NOT NULL
)
Now let’s insert some case sensitive data into it:
INSERT INTO test (id, msg) VALUES (1, 'bUSY');
INSERT INTO test (id, msg) VALUES (2, 'BUSY');
INSERT INTO test (id, msg) VALUES (3, 'busy');
In our test we are searching for a ‘bUSY’ value in the msg column of the test table. So the
syntax of the same query, if the SQL Server was set to be case sensitive, would be:
SELECT * FROM test where msg = ‘bUSY’;
This query will return all rows in the test table. Now, here is the script that will perform the
case sensitive search.
DECLARE @table VARCHAR( 30 )
DECLARE @col VARCHAR( 30 )
DECLARE @searchVal VARCHAR( 195 )
SET @table = 'test'
SET @col = 'msg'
SET @searchVal = 'bUSY'
DECLARE @sql VARCHAR( 255 )
DECLARE @colLength VARCHAR( 3 )
SELECT @colLength = CONVERT( varchar(3), DATALENGTH( @searchVal ) )
SELECT @sql = 'SELECT * ' +
' FROM ' + @table +
' WHERE' +
' CONVERT( varbinary( ' + @colLength + '), ' + @col + ') = ' +
' CONVERT( varbinary( ' + @colLength + '), "' + @searchVal + '")' +
' AND ' + @col + '="' + @searchVal + '"'
EXEC( @sql )

Q, How do I list the available tables in a database I'm querying?
A, You can get a list of tables in a database by calling the INFORMATION_SCHEMA.Tables view
like this:
SELECT * FROM information_schema.tables
This returns the name of the current database, the owner, the table name, and the table type
for each table in the database. It's possible to query the system tables directly, but if this
gives the information you need,
it's better to use the existing views that come with SQL Server.