Category Archives: TSQL

Issue Creating SQL Login for AppPoolIdentity on Windows Server 2008

IIS7 introduced the option to run your application pool as AppPoolIdentity. With the release of IIS7.5, AppPoolIdentity was promoted to the default option.  You see this change if you’re running Windows 7 or Windows Server 2008 R2.  image

On my Windows 7 machine, I’m able to define my Application Pool Identity and then create an associated database login via the SQL Server Management Studio interface.  No problem.  However, I ran into some troubles when recently installing my web application onto a Windows Server 2008 R2 64-bit machine.  Strange, but the same approach failed as SSMS couldn’t find the AppPoolIdentity user.  Instead of using the tools, I created and executed the login via script and it worked fine. 

Here’s the script, based off of the DefaultAppPool identity, if the same happens to you:

CREATE LOGIN [IIS APPPOOL\DefaultAppPool]
FROM WINDOWS WITH DEFAULT_DATABASE=[master]
USE [Chinook]
CREATE USER [IIS APPPOOL\DefaultAppPool] FOR LOGIN [IIS APPPOOL\DefaultAppPool]

TSQL – Self Update

This morning I needed to compose a very simple SQL routine and it took me around five compiles until I got the syntax right.  All I needed to do was transfer an active status from one entity (in my case a computer) to another.  I decided to implement this by updating the same table in which I was selecting.  I think the syntax associated with “self update” type queries is tricky so this post is so I personally don’t lose this code snippet and future cycles.  I hope it might help you as well.

This example transfers the status of the source computer to the destination computer.  Nothing fancy…

DECLARE @SourceID INT; SET @SourceID = 2
DECLARE @DestinationID INT; SET @DestinationID = 1

DECLARE @Computer TABLE (ComputerID INT, StatusID INT)

INSERT INTO @Computer(ComputerID, StatusID)
SELECT @DestinationID, 1 UNION SELECT @SourceID, 2

SELECT * FROM @Computer

UPDATE A
SET A.StatusID = B.StatusID
FROM @Computer A, @Computer B
WHERE B.ComputerID = @SourceID
AND A.ComputerID = @DestinationID

SELECT * FROM @Computer

(2 row(s) affected)
ComputerID  StatusID
----------- -----------
1           1
2           2

(2 row(s) affected)

(1 row(s) affected)

ComputerID  StatusID
----------- -----------
1           2
2           2

(2 row(s) affected)

 

DBTalk – Stuff vs Replace

Why would you use “Stuff” instead of “Replace?”

Good question.  Both perform a similar function — replace a string with another string (or no string at all), but Replace has a simpler syntax.

The Replace function looks like this:

select replace('abcdef','bc','xx')

result: axxdef

No locations to find, just replace the string.  However, what if we had a string with two occurrences of the string, like:

select replace('abcabcabc','abc','123')

result: 123123123

If we only wanted to replace the first one, Replace wouldn’t work, since it always replaces ALL occurrences of the string. But Stuff would, since it only replaces the string it finds at the starting location we tell it for the number of chars we want it to replace. 

select stuff('abcabcabc',4, 3,'123')

result: abc123abc

Since I tend to prefer explicit control of strings (to reduce the possibility of unintended substitutions), I will usually grab Stuff rather than Replace.  The overhead is probably slightly less for Stuff since the function already knows where to look for the value, but I don’t consider that a deal breaker.

Capture Results of EXEC SP

Here is a simple example of a routine to capture the results of an executed stored procedure so they can be acted upon.  To be honest, I am posting this completely for selfish reasons as I tend to forget this syntax more often than I wish to admit.

CREATE TABLE #Results
(
    Column1 varchar(64),
    Column2 varchar(64),
)

INSERT INTO #Results
EXEC SampleStoredProcedure @param1, @param2, @param3

SELECT Column1, Column2
FROM #Results

If, per chance, there is a way to include stored procedure EXEC as a derived table within a sql select statement, I would love to hear about it.  Conceptually, I am thinking something like the following:

SELECT * FROM (EXEC SampleStoredProcedure @param1, @param2, @param3) A

DBTalk – Derived Tables

For the uninitiated, a derived table is where you create a query that contains another query but that second query is wrapped in parenthesis and you will normally join to it for further results or set operations.

The pseudo-code is like:

Select a.Blah, b.BlahBlah
From  FirstTable a
Join (select BlahBlah from SecondTable ) b On b.ID = a.ID

So the second query becomes the “derived table”, all in memory, and is joinable just like a regular table, and all of its indexes are play as well.

BUT – do derived tables have performance better or worse than temp tables and in memory temp table variables?

DBTalk – Select Into and Temp Tables

We can create a new copy of a table from an existing one, using the “select into” function.

However, while “select into” is a powerful function it can also be a server killer due to the i/o overhead copying a bazillion rows in a single batch (if the source was a large table), and more importantly, during the query, if you are creating a temp table (like #tablename) rather than a permanent table, it locks tempdb  (yes, the entire db) for the duration of the query, which means that no other temp tables can be created until the lock is removed (this is not a good thing). 

So if we use “select into” to create a temp or perm table, we want to do it really quickly.  Here’s the workaround for achieving the same goal but without breaking a sweat, OR, use it to simply create an empty copy of an existing table:

select   a.* into tmpAccount
from     dbo.Account a with (nolock)
where    1 = 2

That where clause is always false, which in turn says it can’t return any rows which eliminates all table i/o so the query runs licketysplit.  But does it build the table copy we were hoping for?  You bet. 

The above statement builds an exact replica of the source table but without any rows or i/o or locks or trauma, and releases the lock on tempdb almost instantly if you were building a temp table.

Then, we can leisurely fill the table in a manner that suits the row count.  If under 5000 rows, just do the insert.

If over, break it into 5000 row chunks using a “while” loop.  That prevents the transaction log from bursting when you try to cram 1.5 billion records into the new table…

DBTalk – Group By vs Select Distinct

Here’s something to contemplate:

What’s the difference between “group by” and “select distinct” in a simple query like:

select distinct a.LastName
from   dbo.Account a with (nolock)

could be rewritten as:

select   a.LastName
from     dbo.Account a with (nolock)
group by a.LastName

Who can tell us what the difference in execution would be?  When would you pick one over the other?

DBTalk – @@error and @@rowcount

We use the @@Error and @@rowcount system variables all the time in queries and sprocs.  However, one might be lead astray thinking they behaved like “regular” variables in programming, i.e., they are persistent, but they are not, they only hold the value from the most recent statement.

Here’s an example where I declare a couple of variables to hold the error and rowcount values and purposely create an illegal query (division by zero), then I grab the rowcount in one statement and display that plus the “error” in the next.  Note that the no error value is zero and a non-zero value indicates error:

declare @Error int, @rowcount int
select @Error = 0, @rowcount = 0 

select 'Query 1/0' = 1/0
select @rowcount = @@rowcount
select '@@Error' = @@Error, '@rowcount' = @rowcount

The results are this:

Query 1/0
———–
Msg 8134, Level 16, State 1, Line 5
Divide by zero error encountered.

@@Error     @rowcount
———– ———–
0           1

Huh? How come the @@Error value is still zero?  The real error value was overwritten by the “error” value from the statement “select @rowcount = @@rowcount”, which of course will always be successful.  Here’s how to properly capture the error and rowcount:

declare @Error int, @rowcount int
select @Error = 0, @rowcount = 0 

select 'Query 1/0' = 1/0
select @Error = @@Error, @rowcount = @@rowcount
select '@Error' = @Error, '@rowcount' = @rowcount

and here’s the correct results:

Query 1/0
———–
Msg 8134, Level 16, State 1, Line 4
Divide by zero error encountered.

@Error      @rowcount
———– ———–
8134        1

So the last piece is, why is rowcount still 1 when no data rows were returned?  Every select statement that doesn’t hit an actual table returns at least one row.  If we rem out all of the select and set statements then we get a zero for rowcount.:

declare @Error int, @rowcount int
--select @Error = 0, @rowcount = 0 

--select 'Query 1/0' = 1/0
select @Error = @@Error, @rowcount = @@rowcount
select '@Error' = @Error, '@rowcount' = @rowcount

results:

@Error      @rowcount
———– ———–
0           0

So when we do hit a real table, we’ll get an accurate capture of row count activity:

declare @Error int, @rowcount int
select @Error = 0, @rowcount = 0 

select id, status from master..sysobjects where name = 'grumpy'
select @Error = @@Error, @rowcount = @@rowcount
select '@Error' = @Error, '@rowcount' = @rowcount

results (obviously, no table in master with a name of grumpy):

id          status
———– ———–

@Error      @rowcount
———– ———–
0           0

And one that returns data:

declare @Error int, @rowcount int
select
@Error = 0, @rowcount = 0

select id, status from master..sysobjects where name = sysindexes
select @Error = @@Error, @rowcount = @@rowcount
select ‘@Error’ = @Error, ‘@rowcount’ = @rowcount

results (one data row, no errors):

id          status
———– ———–
-134        0

@Error      @rowcount
———– ———–
0           1

These behaviors are predictable and repeatable and form the basis of structured error handling in sprocs in SQL 2000.  For SQL2005, we have the beautiful new Try/Catch model for error management that makes life a breeze.