How to find the last identity value inserted in the SQL Server

When inserting a row in the database with an identity column as a primary key, most of the time we need to capture the new identity value generated. In SQL Server there cane as many as three approaches for the same.

@@IDENTITY

SCOPE_IDENTITY()

IDENT_CURRENT(‘tablename’)

All of them can be used to find the last identity value inserted in the database, but they differ in the functionality depending on the scope or source of the insert as well as the connection that insert the row.

The server variable @@IDENTITY will return the last generated identity value accross all scope but for the same connection. The value returned will be for the last table inserted with identity column in the same connection. This means that if we insert some record in table (TableA) which has a trigger on the insert and the trigger inserts a record in some other table (TableB) with identity column then the @@IDENTITY will return the identity value inserted in TableB.

Function SCOPE_IDENTITY() is identical to @@IDENTITY with one exception. The value returned is limited to the current scope (i.e. the executed stored procedure). So in our previous example the value returned will the identity value inserted in TableA.

Finally, function IDENT_CURRENT spans all scope and all connections to retrieve the last generated table identity value. But the function is table specific and returns the value for the given table only.

How to change the maximum number of character displayed in SQL server Query analyzer and Management studio

One of the very simple and common that one of the commenter asked me a few days ago was why does the SQL query analyzer show only results unto 255 character only and not full column text. This is common to any data type in SQL server Query Analyzer.

The maximum number of character displayed using the tools->Option menu. Go to the result Tab and change the ‘Maximum characters per column’. BY default the value is set to 255. The maximum value allowed here is 8192 character.

In SQL Management studio (for SQL server 2005) go to Tools->Options, expand Query Results / SQL Server / Results to Text, and change the setting for “Maximum number of characters displayed in each column.. Note that in Results to Grid, you can send 65,535characters; however, results to text is still limited to 8,192 character only

Temporary Tables vs. Table Variables and Their Effect on SQL Server Performance

There are three major theoretical differences between temporary tables:

create table #T (…)

And table variables:

declare @T table (…)

The first difference is that transaction logs are not recorded for the table variables. Hence, they are out of scope of the transaction mechanism, as is clearly visible from this example:

create table #T (s varchar(128))
declare @T table (s varchar(128))
insert into #T select ‘old value #’
insert into @T select ‘old value @’
begin transaction
     update #T set s=’new value #’
     update @T set s=’new value @’
rollback transaction
select * from #T
select * from @T

s
—————
old value #

s
—————
new value @

After declaring our temporary table #T and our table-variable @T, we assign each one with the same “old value” string. Then, we begin a transaction that updates their contents. At this point, both will now contain the same “new value” string. But when we rollback the transaction, as you can see, the table-variable @T retained its value instead of reverting back to the “old value” string. This happened because, even though the table-variable was updated within the transaction, it is not a part of the transaction itself.

The second major difference is that any procedure with a temporary table cannot be pre-compiled, while an execution plan of procedures with table variables can be statically compiled in advance. Pre-compiling a script gives a major advantage to its speed of execution. This advantage can be dramatic for long procedures, where recompilation can be too pricy.

Finally, table variables exist only in the same scope as variables. Contrary to the temporary tables, they are not visible in inner stored procedures and in exec(string) statements. Also, they cannot be used in an insert/exec statement.

But let’s compare both in terms of performance.

At first, we prepare a test table with 1 million records:

create table NUM (n int primary key, s varchar(128))
GO
set nocount on
declare @n int
set @n=1000000
while @n>0 begin
     insert into NUM
          select @n,’Value: ‘+convert(varchar,@n)
     set @n=@n-1
     end
GO

Now we prepare our test procedure T1:

create procedure T1
     @total int
as
     create table #T (n int, s varchar(128))
     insert into #T select n,s from NUM
          where n%100>0 and n<=@total
     declare @res varchar(128)
     select @res=max(s) from NUM
          where n<=@total and
               not exists(select * from #T
               where #T.n=NUM.n)
GO

Called with a parameter, which we will vary from 10, 100, 1,000, 10,000, 100,000 up to 1,000,000, it copies the given number of records into a temporary table (with some exceptions, as it skips records where n is divisible by 100), and then finds a max(s) of such missing records. Of course, the more records we give, the longer the execution is.

To measure the execution time precisely, I use the code:

declare @t1 datetime, @n int

set @t1=getdate()
set @n=100 – (**)
while @n>0 begin
     exec T1 1000 – (*)
     set @n=@n-1 end
select datediff(ms,@t1,getdate())
GO

(*) The parameter to our procedure is varied from 10 to 1,000,000.

(**) If an execution time is too short, I repeat the same loop 10 or 100 times.

I run the code several times to get a result of a “warm” execution.

The results can be found in Table 1 below.

Now let’s try to improve our stored procedure by adding a primary key to the temporary table:

create procedure T2

     @total int
as
     create table #T (n int primary key, s varchar(128))
     insert into #T select n,s from NUM
          where n%100>0 and n<=@total
     declare @res varchar(128)
     select @res=max(s) from NUM
          where n<=@total and
               not exists(select * from #T
               where #T.n=NUM.n)
GO

Then, let’s create a third one. With a clustered index, it works much better. But let’s create the index AFTER we insert data into the temporary table—usually, it is better:

create procedure T3
     @total int
as
     create table #T (n int, s varchar(128))
     insert into #T select n,s from NUM
          where n%100>0 and n<=@total
     create clustered index Tind on #T (n)
     declare @res varchar(128)
     select @res=max(s) from NUM
          where n<=@total and
               not exists(select * from #T
               where #T.n=NUM.n)
GO

Surprise! Large amounts of data take longer; merely adding 10 records takes an additional 13 milliseconds. The problem is that “create index” statements force SQL server to recompile stored procedures, and slows down the execution significantly.

Now let’s try the same using table variables:

create procedure V1
     @total int
as
     declare @V table (n int, s varchar(128))
     insert into @V select n,s from NUM
          where n%100>0 and n<=@total
     declare @res varchar(128)
     select @res=max(s) from NUM
          where n<=@total and
               not exists(select * from @V V
               where V.n=NUM.n)
GO

To our surprise, this version is not significantly faster than the version with the temporary table. This is a result of a special optimization SQL server has for the create table #T statements in the very beginning of a stored procedure. For the whole range of values, V1 works better or the same as T1.

Now let’s try the same with a primary key:

create procedure V2
     @total int
as
     declare @V table (n int primary key, s varchar(128))
     insert into @V select n,s from NUM
          where n%100>0 and n<=@total
     declare @res varchar(128)
     select @res=max(s) from NUM
          where n<=@total and
               not exists(select * from @V V
               where V.n=NUM.n)
GO

The result is much better, but T2 outruns this version.

Records

T1

T2

T3

V1

V2

Table 1: Using SQL Server 2000, time in ms.

But the real shock is when you try the same on SQL Server 2005:

N

T1

T2

T3

V1

V2

Table 2: Using SQL Server 2005 (time in ms).

In some cases, SQL 2005 was much faster then SQL 2000 (marked with green). But in many cases, especially with huge amounts of data, procedures that used table variables took much longer (highlighted with red). In four cases, I even gave up waiting.

Conclusion

1. There is no universal rule for when and where to use temporary tables or table variables. Try them both and experiment.
2. In your tests, verify both sides of the spectrum—small numbers of records and huge data sets.
3. Be careful with migrating to SQL 2005 when you use complicated logic in your stored procedures. The same code can run 10-100 times slower on SQL server 2005!

SQL Server: Check Whether All Characters In a String Are in Uppercase or Not

When I was developing a small application, I had to write a Microsoft SQL Server script to check whether all the characters in a given string are uppercase alphabets or not.

Here is the Microsoft SQL Server function code which performs the check. This code is compatible with Microsoft SQL Server 2000 and Microsoft SQL Server 2005.

---

/****************************************************************

 * Purpose: Check whether all characters

 *         in a string are capital alphabets or or not

 * Parameter: input string

 * Output: 0 – on success; 1 on failure

 ****************************************************************/

CREATE FUNCTION fnChkAllCaps(@P_String VARCHAR(500))

RETURNS BIT

AS

BEGIN

 

DECLARE @V_RetValue BIT

DECLARE @V_Position INT

 

SET @V_Position = 1

SET @V_RetValue = 0   

 

–Loop through all the characters

WHILE @V_Position <= DATALENGTH(@P_String)

           AND @V_RetValue = 0

BEGIN

 

     –Check if ascii value of the character is between 65 & 90

     –Note: Ascii value of A is 65 and Z is 90

     IF ASCII(SUBSTRING(@P_String, @V_Position, 1))

            BETWEEN 65 AND 90

        SELECT @V_RetValue = 0

    ELSE

       SELECT @V_RetValue = 1      

   –Move to next character       

   SET @V_Position = @V_Position + 1

END

 

–Return the value

RETURN @V_RetValue

 

END

---

Sample code to test the function

---

SELECT dbo.fnChkAllCaps(‘TECHTHOUGHTS’) – Returns 0

GO

SELECT dbo.fnChkAllCaps(‘TechThoughts’) – Returns 1

GO

---

The above function iterates through all the characters of a given input string and checks whether ASCII value of the characters is between 65 and 90 to verify it is an uppercase alphabet or not.

You might be wondering why is ASCII values check is between 65 and 90. It is because the ASCII value of uppercase A is 65 and uppercase Z is 90.

Soon I’ll rewrite the same code using SQL Server 2005 CLR functions and post it. I believe Microsoft SQL Server 2005 CLR functions perform this check very efficiently.

Why is SQL Server So Slow?

I’ve been asked this question a few times when looking into performance problems on a website, and the short answer is, it’s not. SQL Server is a very efficient database.

It’s important to remember that a database is just a runtime for your SQL code. It’s just doing what you ask it to do, nothing more, nothing less.

This applies to any database. MySQL, PostgreSQL, maybe even Oracle are all just agents that do your bidding. But I’m most familiar with SQL Server’s tools for finding and fixing some common problems, so that’s what I’m going to talk about here.Typically what happens is something like this:

• You develop an application.
• You test it. It works okay.
• You add some test data and test it with a “lot��? of rows. It still works okay.
• You deploy it.

Then, maybe a few weeks, maybe a few years later, you, or your successor, notices that the application or website’s response is slow, and it turns out, it’s the database’s fault. The conclusion is that the database is slow, and plans are made to replace it.

The problem with this scenario is that the database probably isn’t the problem. The performance characteristics of any of the major databases are all close enough that switching from one to the other without some optimizing in the process would not help. Do that same optimizing to the existing system and you won’t need to switch.

First off, how was the conclusion reached that the database is slow?

Both Windows and SQL Server give you some excellent tools for diagnosing your problem.

First, look at the CPU usage of the SQL Server process.

Either it will be very low, very high, or somewhere in between.

Ideally it will be somewhere in between, but generally low. If it’s pegged at 100% then you’re probably running a query that’s got all the data it needs in memory, and SQL Server is being forced to do something rather inefficient with it, like stepping through all the rows adding the value of some field for example. This can be a symptom of throwing RAM at the problem without optimizing, and will stop being a solution once the data outgrows memory again.

Often what you see, though, is that SQL Server’s CPU usage is very low, but the system’s paging activity is very high. This signifies that the system is starved for memory, and is swapping. SQL Server shows low CPU usage because it’s busy waiting for the disk to deliver some pages.

This screenshot shows a pretty good situation: Just a bit of paging activity going on. If you’re seeing hundreds of pages per second constantly, you’ve got a problem. Swapping will kill your server’s performance.

If you have a query like “select * from users where lastname = ‘bob’” and you don’t have an index on lastname, then what you’re doing is asking SQL Server to read every single row from table users and build a set of matching rows. This is called a table scan. You want to avoid table scans.

This is the #1 cause of SQL Server slowdown over time: Not having the right indexes.

When executing a table scan, the server needs to read every row of the table. This thrashes SQL Server’s cache, so that now other data cached for other queries has been tossed out and now those other queries have to hit the disk. This means even an occasional table scan is a bad thing.

Here’s how to gain a little visibility into what’s causing your problems. First off, launch the SQL Profiler.

This tool lets you spy on what’s going on inside your server. Create a new trace:

The defaults here are good. Hit Run.

While it’s running, SQL Profiler is gathering data on every query your server executes. Launch your application or visit your website and do some of the things that are causing the performance problems. This gives the profiler, and the next tool, the Index Tuning Wizard, some data to work with. When you’ve got a good data sample, stop the trace.

Before using the Index Tuning Wizard, take a look at the result data. The Reads column and the Duration column are two of the most useful: Reads tells you how many disk pages needed to be read to satisfy this query, and Duration tells you how long it took (in milliseconds).

The highlighted line read 296972 pages, and took 29 seconds. Looks like a problem, doesn’t it?

You can use this data to do some manual tweaking; often the problem is immediately obvious. But your next new friend, the Index Tuning Wizard, will help.

Save your current profile run as a Trace File (*.trc), and then on the Tools menu of the profiler, select the Index Tuning Wizard.

It’s best to select Thorough if you’re working with a fairly small data set.

Unless you’re looking for a very specific problem or have a huge data set, select all the tables. The more the wizard has to look at, the better.

With any luck, the next page will tell you that the Wizard is going to fix all your problems.

No such luck in this case: No recommendations were found. But lets look at another example in a different tool: Say you’ve seen one particular query in the Profiler that you’re suspicious of. Launch the Query Analyzer, enter the query, and bring up the Index Tuning Wizard from there.

When you launch the wizard this way, one of the options is to use the Query Analyzer selection as the query to tune indexes for. I typed in a query that’s going to cause a table scan:

select count(*) from eventlog where logportalname = 'bob'

The logproperties field of the eventlog table doesn’t have an index. Here’s what the tuning wizard gives me for this:

Now there’s a nice projected outcome: A 95% performance improvement! A few clicks and you can have SQL Server apply the changes for you, or create a scheduled job to apply the changes later (during a scheduled maintenance period, for example). You can apply the changes to a live database, but queries on the table being indexed will block until the operation is complete, which could take a while.

But back to the case where the wizard didn’t find anything that could help you. Another thing that often happens is that you’ve got a massive table because you wrote code to put things into it, but never to take anything out of it. You create a log table, or an errors table, or a hits table, write code to put things there, and forget about it.

If you see a query or an insert that’s hitting thousands of rows, count the rows in the table.

select count(*) from eventlog

If you see a few million rows, then that’s your problem.

When you insert into a table, there’s work to do, and the bigger the table, the more work. If your eventlog table has a few indexes on it, those need to be updated when you insert, and the indexes on a table with millions of rows are going to take more IO operations to update than inserting into the same table with only a few thousand rows.

The best way I’ve found to manage this is to use SQL Server’s Jobs to automatically clean things up at regular intervals. Check out the Jobs section of the Enterprise Manager:

A job is work SQL Server will do for you at scheduled times. Create a new job.

This is a sample job. It’s got the 3 steps listed that it’s going to take, one after the other, that will drop old data from a database. A job step is just some SQL to execute:

If you’re going to have multiple steps for a job, don’t forget to go to the Advanced tab and set up the job actions – each job should go to the next job when it’s done, except the last one, which should report success.

Set up the job to run at some regular interval, and that’s it – you’ve guaranteed that your table isn’t going to grow unbounded until it starts causing you problems again.

There you go – Hope all this helps! If you have any other SQL Server optimization tips please post