SQL Server 2008 Microsoft Certified Master (MCM) Readiness Videos

Thanks to Brad McGehee. (Linkedin)
He is the one who first mentioned about this link. (or that is where I saw it)
When I went to the site I was able to discover some more content deep and simple.
Do you want to know about the internals, and like to analyze various ways of handling things, this is something great to visit.

http://www.microsoft.com/feeds/TechNet/en-us/How-to-videos/SQL_Server_2008_Microsoft_Certified_Master_%28MCM%29_Readiness_Videos.xml

I am speaking today at SSSLUG on Excution Plans

Today the sessions include an introduction to Denali and the second part of Execution Plans Explained. Today's session focuses on specific items in the execution plan:  Seeks, Scans and Physical Join operators.

Bug? - Multiple drop and add columns can cause error

This issue was first reported by my friend and colleague at eCollege Tim Sesst. We did a lot of research on this. Dinesh Asanka, Susantha and I really got into this.  Initially we thought this as a feature, but it took some time for us to understand the issue. Now we feel that it is a bug.

Before going any further, check this code:

IF OBJECT_ID('TestAlterTable') IS NOT NULL

    DROP TABLE TestAlterTable

create table dbo.TestAlterTable

(

     Id int identity not null CONSTRAINT PK_TestAlterTable PRIMARY KEY CLUSTERED

     ,col1 char(8000) not null

     ,col2 char(49) not null

)

go

Declare @i int =1

While @i <100

begin

print @i

IF OBJECT_ID('DF_Col2') IS NOT NULL

    ALTER TABLE dbo.TestAlterTable DROP CONSTRAINT DF_COl2

Alter table dbo.TestAlterTable Drop Column Col2

Alter table dbo.TestAlterTable add Col2 char(49) NOT NULL CONSTRAINT DF_Col2 DEFAULT('a')

set @i+=1

end

Based on the number of columns and the data types you have, at one point,  you may receive an error similar too this:

.Net SqlClient Data Provider: Msg 1701, Level 16, State 1, Line 9
Creating or altering table 'TestAlterTable' failed because the minimum row size would be 8061, including 8 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.
 
 The number of times you need to drop and add columns depend totally on number of columns and data types used. 
Initially we thought that this was due to cleaning of data of the dropped columns from data pages.  Like the ghost entries, (where the row is deleted logically, but the data is still there in the data page) I was even prepared to name this action as ghost columns but, sorry, I was wrong. This happens at the meta data level.

Still we haven;t figured out the reason behind this.  But based on our research, it has all the behaviors of a bug. It does not happen at the first time. 

Interestingly, if you rebuild the primary key, you can continue on the same path for another fixed number of times.

If anyone knows a good reason to say, why this is not a bug, please let me know.
Thanks to all the guys at eCollege who worked on this. 

Index Remained Fragmented EVEN AFTER Index Rebuild Job

I had an interesting thing to find out this week. An Organization had an issue: Their queries are running extremely slow. They did some research on this. They found that even simple queries which uses index seek too are extremely slow. They copied the data into another database and created the indexes and executed again. They were quite fast. There were no other processes running when this test was carried out.

Through another friend of mine they got my contacts and they called me. I got the execution plans from both databases and they were identical. Then with the help from them, I ran a query using sys.dm_db_index_physical_stats against this index and found that it is fragmented. They rebuilt the index and the query started working well again.

Using the same DMV against other tables, I found out that the indexes are fragmented. They sent me the figures they received using dm_db_index_physical_stats Fragmented means some are 80% - 95% fragmented. To be honest with you, I even saw a few indexes showing 100% fragmentation which I have never seen before. I asked them to rebuild the indexes. Later I explained them how these house keeping jobs could be automated.

After a couple of weeks, they called me again. The indexes are fragmented again. I asked them whether they have added the index maintenance job. They answered back saying they have an index maintenance task as part of the database maintenance job and even made it as the first task of the job, [which runs on Sunday nights] but that has not resolved the issue.

Red gate labs

Red gate has opened its lab for public viewing!

It is web site, where the tools developed are available for download. They include
Log Shipping Monitor, SQL Backup to MTF Converter (which converts a red-gate backup to native SQL Server backup and Red Gate Snapper (that allows you to create SQL Compare snapshot files for SQL Server databases).

The lab does include tools for .Net developers. Red Gate Memory Tracker (analyzing application memory usage, including loaded libraries, system heap memory and .NET memory) and Diagnostic Tool (used to collect useful system information) will help serious developers.

These are tiny tools, which i believe Red-gate may include or have included with their products. Now these tools come as free downloads.
I haven't tested all the applications yet, (in fact I haven't tested any of them) but I feel that most of them are really useful.

Try this link
http://labs.red-gate.com/

NO Recompilations when (parameter) values are different

Please read another post on this subject by Dinesh Priyankara.
http://dinesql.blogspot.com/2010/10/do-we-need-to-recompile-stored.html

This is another topic came in the last user group meeting: 
Does SQL Server generate a new plan (in other word recompile) the query if the parameter value is changed?
The actual answer is it depends:
First of all, SQL Server does not go through the recompilation process simply because the parameter is changed.
As I mentioned in the previous blog, there are only certain conditions where an existing execution plan is thrown away and a new plan is used.

Even though in my example, a new plan is created between these two statements, the value is not the cause.

DBCC FREEPROCCACHE

GO

SELECT * FROM Sales.SalesOrderDetail

WHERE SalesOrderDetailID <= 1

GO

SELECT * FROM Sales.SalesOrderDetail

WHERE SalesOrderDetailID <= 1000

GO

When I ran the queries and captures the SQL:Recompile event, nothing was captured. Interestingly, ShowPlan XML for Query Compilation too, didn't give any number as the reason for recompilation. For me it looked like SQL Server didn't consider them as same query.
I removed the GO statement in between and tried too. The result was same. (except the column line number showed 1 and 2 instead of 1 always.
The image is attached.

Fortunately, SQL Server provides a few DMVs (dynamic management views) which allows us to check the execution plans

Recompilations after index creation

Yesterday I got an interesting question: Does adding an index to a table force the execution plans to be recreated?
Books online says this:

 

Please note the first, second and fourth bulleted points.

It says either a structure should be changed or index used by the query should be changed. In other words not any change in the index will not force recompile.

Today I will be speaking at the user group meeting

As some of you know, SS SLUG (SQL Server Sri Lanka User Group) meets today. Generally we have two topics to discuss. 
According to the plan we had earlier, I will be in the panel of the second topic (database design)
But due to certain unforeseen issues, at the last minute, I accepted the challenge to speak at today's user group meeting. That means I will speak on execution plans first and then will be in the panel for the second topic too.

If everything goes well with the plan, In today's presentation, I will talk about reading execution plan, various table and index operations, and the logic behind it. At the end of the session, I expect the users to guess the execution plan for simple queries


See you there!

Trigger Happy at SSSLUG

Hope you were there at the last user group meeting!

I was talking about triggers.  This was a beginner level session.
The code samples and the slide deck are uploaded at SSLUG.  you ncan download them from here:
http://sqlserveruniverse.com/v2/SSSLUG/Downloads.aspx

Understanding REVOKE

This is one of the topics I was planning to write for a long time, but failed due to various reasons (and without valid reasons) So, this may sound too basic for some people; if so, please forgive me. I have seen much confusion over REVOKE statement. Some believe that revoke is a different word for DENY. As explaining the security model is worth a series of articles, in this post, I am going to explain only about REVOKE statement. For this, I am using AdventureWorks database. I created two logins which I am using throughout the post

CREATE LOGIN [Sam] WITH PASSWORD=N'MyPassw0rd!_', DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

CREATE LOGIN [Joe] WITH PASSWORD=N'MyPassw0rd!_', DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

Additionally I am adding these users to AdventureWorks. Two database roles also created for this purpose.

USE [AdventureWorks]

CREATE USER [Joe] FOR LOGIN [Joe] WITH DEFAULT_SCHEMA=[dbo]

CREATE USER [Sam] FOR LOGIN [Sam] WITH DEFAULT_SCHEMA=[dbo]

CREATE ROLE [Sales] AUTHORIZATION [dbo]

EXEC sp_addrolemember N'Sales', N'Joe'

EXEC sp_addrolemember N'Sales', N'Sam'

CREATE ROLE [Marketing] AUTHORIZATION [dbo]

EXEC sp_addrolemember N'Marketing', N'Sam'

Susantha's Presentation on Powershell

As you may know, Susantha Bathige, a fellow DBA at eCollege, gave a great presentation on Windows Powershell 2.0 at the last SQL Server User Group meeting. He had a few hick-ups when he was starting as the laptop couldn't communicate well with the projector. Gogula shared his laptop, but it refused to listen to his commands properly.

On content, presentation style and confidence level Susantha made an impression. I recommend it to all DBAs, developers and IT pro members.

For those who couldn't participate can view the sample code and presentation at here.  The pictures taken were available at here (If you are wondering what I was doing when he was presenting, I was working as his assistant during his presentation.  I hope it would have helped Susantha to concentrate on the presentation while I was controlling Gogula's laptop.)


Congratulations Susantha.  It is very difficult to say that it was your first presentation.  Keep it up.

"She" is coming. .. I am excited. Are you?

For a change I am writing some thing different this time. This is not a technical article. As you can below, I have no tags to it.

What is her name? I don't know yet. But I know she is coming and I am excited about it.

If you are wondering who she is, I can tell you this: Even though I haven't seen her, I am sure she will be in her best, sexiest appearance when she appears. You are going to wait anxiously to see her coming and working in your office. You may want to work closely with her, see her internal organs and have complete control over her. Initially your office management will be scared / hesitated to take her in, but there is a high possibility she will make her way in. When she comes she will come with much “hosanna” from some, while some others will grumble about her. Those who grumble will find fault with the time she has taken to respond to the calls they have made, the way she is working and the time she takes to deliver results, and even how her internal organs are made. Interestingly only a few will grumble about her sexy appearance as everyone knows that there is no point in talking about it.

Even if she failed to join your firm, you will try to have some quality time with her. You may use your office and after office hours to gather information about her, listen from people who know her already and even make appointments to work with her. Sometimes you will work with her but mostly you will sit and watch when she is doing the hard work for you. Even though she may work very closely with you, she will remain a mystery for some time. Gradually you will start learning some secrets about her. You may be looking for a day to say at least this: “I know many things including some secrets about her”.

I am not sure about you, but I bet many like to have illegal relationship with her, as starting a legal relationship costs a lot these days, you know. Even though her parents will try to avoid these illegal relationships and she too will resist illegal relationships some may know how to get hold of her.

Initially no one will know her actual name, and the actual date she is going to join your firm. People will keep on guessing. Many will refer her with her pet name until they are used to the actual name.

Excited? Now I share the first secret I know about her: Her pet name is Denali

I need to caution you on this: As it happen often in other cases, when time goes, you may lose the excitement and even start looking for a new one.

Now it is time to share the next secret: Denali is the code name for the next version of SQL Server, Microsoft’s flagship database management system. Most probably you will see her coming to your place by next year. So better be ready soon.

Constraints on Temp tables

Yesterday, I had an interesting conversation on temp tables:
All started with this question:  What is the best method to create constraints on temp tables? I never expected it to give me a chance to learn something new.
As you know Temp tables are created in tempdb. Local temp tables are visible only to the user, and it allows multiple users to have temp tables on their own. That means while user A has a temp table #MyFirstTable another user can have a table (at the same time) at the same name.
Generally all objects created in a database will have an entry in sys.objects. For the table, the entry will not be on the same name. SQL Server will add additional characters to create a unique name. The objects are dropped when the connection is dropped or reset.
But there is a difference when it comes to constraints. When a constraint is created, it created with the exact name specified and it will have an entry in sys.objects table. For example the following code is going to fail if multiple users are going to execute during the same period.

CREATE TABLE #MyFirstTable

(

MyFirstTable_PK int,

CONSTRAINT PK_#MyFirstTable PRIMARY KEY CLUSTERED (MyFirstTable_PK)

)

The reason for failure is nothing to do with syntax. When the first user executes it two entries into sys.objects: a row with system defined name for table and another row for name we specified for primary key. When the second user executes the code, it can add the row for table but it can’t add an entry for primary key. So it will fail for second user.

Cluster Installation

I am writing this after a long 2 months delay. While I do not try to justify the reasons, Some high priority things at office and issues in my computer kept me out of writing.

Last couple of months we were trying to create a clustered environment.  Generally, when it comes to clustering, we get into additional cost.

This was my idea.  we need two servers clustered (windows) in active-active topology.  Then we need install one instance in as active in Server A and passive in Server B. Then similarly, we can install another instance active in server B and passive in Server A.  By using this, we create a fail-over cluster while utilizing the passive server for another SQL Server installation.

Initially, our proposal was not well taken. I was told that that is simply not possible in Windows 2008 cluster. I have seen similar configuration in Windows 2003.  So I was pushing hard.  Finally the hardware vendors tried on their own and created a test server.  Yes it worked.   Now we are setting up clustered environment, while 100% utilizing the processing power of the server.
The key is to create the shared disks properly.  If that is done, you are almost done.

Interesting?  If you want to know more,  drop me a mail.  I'll send the details.

The Other side of Filtered Index

 
I see many like the idea of filtered index. This cooooooooooool new feature allows an index to be created on a subset of the data. I have seen many presentations praising this feature.

Most of you who attended the session on “The other side of indexes”  at the last Tech Ed would have enjoyed the session well. Pinal Dave was giving a very good explanation on when to use indexes and when not to use them. He mentioned on Filtered index and explained how it will benefit certain type of queries.


If you have a large table (by rows) your index will be larger.  It adds complexity over the maintenance plans you need to build.  Additionally, statistics around larger indexes can get skewed easily. This may lead to sub-optimal execution plans chosen by SQL Server Query engine.

On of the key features on filtered index is the WHERE clause. The condition may not reference the key columns of the index For example, CREATE INDEX IndexName ON TableName (Column1) WHERE Column2 >100 is a valid statement. This allows a high level of logical partitioning of data. You can create an index for column1 where Column2 is mentioned in the definition but not in the storage.

I am inspired by the feature. Wow filtered index can make wonders.  It will ease some pain points from DBAs. Suddenly a question came into my mind:  Can I create multiple filtered indexes on the entire table by

New Service packs for SQL Server

The Group Program Manager of SQL Server team finally announced the news last Friday (Saturday Morning SL time):  There will be new service packs for SQL Server 2005 and SQL Server 2008 before end of the year.

SQL Server 2005 sp4 is expected to be released by the last quarter of this year, but the mainline support will end by April 12, 2011.
SQL Server 2008 Sp2 is expected to be released by the third quarter:
Here is the link http://blogs.msdn.com/sqlreleaseservices/archive/2010/02/12/sql-server-servicing-plans.aspx


As you all know, service packs are simply the collection of Microsoft's cumulative upgrades (CU) released after the last service pack. However, service packs go through much deeper quality assurance tests than cumulative upgrades.  As a reason, many companies do hesitate to install cumulative upgrades to their production and critical installations. However, with this announcement, many many like to install the service pack.

Born to Learn ?

Frequently I hear people asking "What are the good resources to learn SQL Server stuff?" Honestly speaking I still do not know how to answer. Some sites I feel as great may not be that great for some one else.  (I can even start with the famous phrase "It Depends...." lol.)  Some may want to learn things from webcasts, some may like simple articles while some others like lab sessions,where some do have the patience to read huge books both printed and digital.

As I am still learning, I was thinking what is my favorite? The fact is I do not have a favorite.  I read almost everything, both good and bad. (I remember, when I was visiting a friend of mine,  I even started reading a query he wrote on his computer.  Those days, we didn't enjoy internet, He has just bought a computer.  He used books online and wrote a multi-statement query. That is the first time I saw a multi statement query. All those days, I was breaking my brain to some how write a complex process in single statement. )

Okay,  Now for those who love to learn, Some of the sites I like to recommend:

• Books online.  Whether you have SQL Server or not (you can have SQL Server Express Edition for free) you can have books online for free.  For me this was the only source of information when SQL Server 6.0 was there.  (was it the same name at that time?)
• Learning resources from Microsoft.  Whenever a new version is released, Microsoft also releases a set of web casts and lab sessions.  Please refer them at  http://www.microsoft.com/sqlserver/2008/en/us/learning.aspx
• Dedicated SLQ Server sites. Some of them are: 
• http://www.SQlServerCentral.com.  I believe this is most popular site.  It has a wide range of topics, for both novice and advanced users.  
• http://www.sql-server-performance.com.  Another wonderful site, dedicated to performance tuning and optimization.  If you are a new user,  and not much involved in administration, this may not be the best site for you.  I see less articles now in this site, but this site contains a lot of tips and forums on performance.
• http://www.databasejournal.com/features/mssql/ This article periodically writes articles on various topics.
• http://www.simple-talk.com/sql/ I don't think I can say thing less than "Great" about this site.  It is for those who already use SQL Server.  real meat for SQL Server lovers.
• http://www.mssqltips.com/ A great site for small tips on SQL Server. More beneficial for database developers.
• http://sqlserverpedia.com Another growing site sponsored by quest software.
• Books: http://www.red-gate.com/about/book_store/index.htm is my first choice. If you have membership on safari online book store, you may be able to read a lot of them. I do not know how much it will cost.  eCollege provides me (and all developers and DBAs) a free membership. (Another reason to join eCollege!)  There are other sources for free e-books but I can;t list them here.  There are many.
• Blogs:  I believe now they are the best option for these days.  They are small, and to the point (If you think this blog is an exception, that is not the point and you re going out of the point!) You have the freedom to read as many as possible every day. If you want to know about the blogs available on SQL Server,  PASS has a directory of blogs at http://www.sqlpass.org/Community/BlogDirectory/tabid/66/Default.aspx.  (Those who have not registered here yet, can register. The page contains the information).  This site has many recognized blogs.  (Yes. my blog is listed... ha ha ha)

Enough said. Now you have two important notices:

1. I found a site periodically offering level 400 web casts on performance optimization.  you have web casts ready for downloads http://webcasts.sqlworkshops.com/webcasts.asp
2. Quest software is offering a full day free virtual conference on troubleshooting and performance tuning. Quest announced that this event will happen on March 03rd.  Check this link if you are interested:  Quest Software SQL Server vConference; Troubleshooting and Performance Tuning.  Remember that they have planned to have a session for each for beginners moderately experienced and advanced users.

Tech Ed 2010 - Feedback

I need to share a couple of things which made me happy.
First,  the sessions: 
There are a couple of sessions purely for DBAs, well at least one session for people like me, who work with massive OLTP systems.
We really enjoyed a couple of sessions:

• New Features in SQL Server 2008 R2
• The history of the Log: Change Data Capture (CDC)
• Customizing Analysis Services Dimensions
• The Other Side of SQL Server Index : Advance Solutions to Ancient Problem  

On top of these events, we had a special SQL Server User Group meeting too.  It was well attended. At the meeting, I was honored to get selected as one of the top posters at SQL Server Universe.com. (Sorry guys,  the site is still down) I was awarded with gifts, including a book of my choice on SQL Server.
 
Second, the speakers:
I met some good SQL Server guys and some guys who work with Windows.  It is a pleasure to have them. I met two guys from Solid Quality Mentors. One guy is  "P" NULL ... err.  I mean Pinal Dave. He is not only talented but also has a great sense of humor.  He is the one who writes at SQLAuthority.com - a blog which is read by thousands every day. I had quite a long chat with him,  where he even gave me feedback on my blog.  (Hurray...  He too reads my blog)

Even though the other guy looked familiar,  I couldn't recognize him immediately.  He is  Rushabh Mehta.  He is a mentor and managing director of Solid Quality Mentors (India) and president of PASS (Professional Association of SQL Server) international.  By the way do you know the SQL Server Sri  Lanka user Group (SSSLUG) is the Sri Lankan Chapter of PASS?  (I am sorry,  I couldn't take a photo with him.  I suddenly got a call from office and I was forced to leave the room.)  But If I recall right Gogula took a few.  I hope he will upload them soon.

I also met a great guy from Microsoft HQ:  Brian Puhl. He is a technology architect at Microsoft (Redmond).

Third, the other participants

Summits like this always give an additional opportunity to meet the people you don't know, also gives a chance to meet some of your old friends. I met a whole heap of them.  I met some after a long period, it took some time to recognize some of them.  I need to admit, that on some people, I couldn't recall where I met before too.  I was an embarrassing situation..  It is also a sign that I am getting old :).  As usual, I told some developers about the openings available at eCollege. 

Forth, the crossword puzzle
 Don't tell anyone,  I was working with Gogula in putting up some crossword puzzles.  These puzzles were on display at MVP corner.  Out of the correct answers, they were selecting 3 lucky winners and present them a portable external hard drive (and many other gifts)
For me it a joy to see the participants working on puzzles. They were in groups trying to solve it.  Some were calling their friends and asking for possible answers.  Some were searching on the web and I saw a guy reading a book to solve a clue.    

Fifth, The winners 
I was thrilled to see ever smiling Iranja (from eCollege of course) showing me what he got as a winner. He solved the crossword and won the price.  I am not sure who created his crossword puzzle.  But if anyone thinks that you can get some clue about the answers, remember that the puzzle you get (or going to get) was not prepared by me.

If you have missed Tech Ed,  you still have a day. :)

String "Aggregates" Solution

Even though I promised to update a solution to a problem,  I failed to do so for a long time.

Here is the problem

If you need to get the sum of salary by department we may be able to use Sum and group by clauses in a single select statement to get it done.  But if you have a situation where you need to give a comma separated string of employees for each department, how will you handle it?
Consider these tables:

Department Table

DepartmentID	DepartmentName
1	Sales
2	IT
3	Finance

Employee Table

EmployeeID	DepartmentID	EmployeeName
1	1	Sales Person 1
2	1	Sales Person 2
3	2	Developer 1
4	2	Developer 2
5	2	DBA 1

Now you want the results like this:

DepartmentName	EmployeeList
Sales	Sales Person 1, Sales Person 2
IT	Developer 1, Developer 2, DBA
Finance	NULL

Tech Ed 2010.

Are you guys attending Tech Ed 2010?  Well I am attending and Excited about it too.

Generally,  In Sri Lanka, There is a lot of emphasis on Development side when it comes to Tech Ed.  This is nothing different.  But, some interesting things happening on SQL Server side too. We have four sessions on the first two days on SQL Server.  In addition to that there is one session on Excel, which is useful for those who work with analysis services.  So, you will have totally five sessions connected SQL Server all during the first two days.  Two guys from Solid Quality Learning (Not Kimberly Tripp or Paul Randal, but from the same organization) are coming here.  They are From India.

Added on February 09, 2010
Correction:  A Couple of errors in this statement: First of all, Rushabh_Mehta and Pinal Dave are from Solid Quality Mentors.  Additionally, I was informed that neither Kimberly L. Tripp nor Paul Randal are from Solid Quality Mentors. Unfortunately I got a presentation from web by Kimberly Tripp,  where on one of the slides she was connected to Solid Quality Learning. I got the words mentors and learning mixed up. As I knew she and Paul work together I wrongly assumed that they were working there.  Sorry guys,  My sincere apologies. 

Want to get confused more?
Interestingly, when I searched for her connection with Solid Quality Learning, I found a another link saying Kalen Delaney is from Solid Quality Learning. But When I searched for www.SolidQualityLearning.com, my browser was redirected to Solid Quality Mentors.

You can view the agenda here.

I’ll say, in addition to the scheduled topics, we have some additions.  SQL Server Sri Lanka User group meeting is scheduled on the first day, after the sessions. (Even if you can’t attend the summit, you can attend this session.)   You can raise any questions you want to raise.

I also heard that the MVPs will have a stall and having some competitions there.  There is a good chance of winning some great prices.  (Shhh.  Don’t tell anyone. MVPs are keeping it as a secret until the first day, but some of the things they are going to give are close to one hundred dollars.). Things include (oops…. Sorry I am not supposed to tell that)… 

Interested in attending the summit, but struggling with the workload? You can attend only for a day.  Microsoft is issuing day passes, so you can attend only for a day.

I will be there.  Will you?

SQL Server Universe

Some of you have faced issues with SQL Server Universe. Com – the official site of SQL Server Sri Lanka User Group.   This is the site where all our user group activities are updated. There were a couple of limitations which prevented from uploading presentations,  new articles, pictures and many other things.  Now I have good news and bad news.  We were having these issues for couple of months.

Bad news first: The site is unavailable right now.  Earlier, at least you can view existing things and participate in forums.  Now nothing you can do other than reading a standard error page.

Now the good News: The site is going through an upgrade process. That is the reason it is offline right now.  I heard that the team had redesigned it using new version of community server, Due to this the site is currently unavailable.  Gogula and Wella are working on resolving it. Please keep on checking.  I heard that some of the features are not even available on our sister sites.

Best Method to archive data

Hi all,
Recently I got into a particular situation:
We have a process (process 1) which goes through all the data inserted today, from one table and do a complex calculation using multiple tables. If a particular condition is not met, I copy the data into a table (ProcessData).  This process will go though > 100K of rows but will insert <1000 rows into the table.
Then I have another process (Process B),  Which takes the rows from ProcessData table one by one and executes a complex processing.
Both Process A and  Process B are windows services working at off peak time.  They access some large and heavily used tables.  (Some tables are having more than 30 million rows and get around 160, 000 kits per day)
Now what is the best way of archiving the data from ProcessData table.
Suggestion 1:  When Process B completes a row delete the row and move the data into archive table
Suggestion 2:  Wait until Process B completes all rows and move all rows to Archive table and truncate the table
Suggestion 3:  When Process B completes a row mark the row (using a flag). Create another scheduled job to move the data using the flag periodically.
What is the best method?

Identifying Identity related functions

Almost after a week I am writing this post. The heave work load at office and some of the other commitments prevented me from writing during most of the last week.

This time I decided to write about Identity column as I see this issue coming again and again in various forums and blogs.

These are the principles:
When you create a table with the identity property, it does not have any values assigned to it.

USE tempdb;

CREATE TABLE t1 (id int IDENTITY(1,1) )

GO

  

Now we'll check each identity related command and see the results.

SELECT IDENT_CURRENT('t1'), SCOPE_IDENTITY(), @@IDENTITY

IDENT_CURRENT function will return the seed value. This will return null under one condition: The table does not have any column with identity property (or table does not exist).

As there is no statement in this batch has inserted value into this table, both SCOPE_IDENTITY and @@IDENTITY return null.

Now Let us insert a row into t1

INSERT INTO t1 DEFAULT VALUES

SELECT IDENT_CURRENT('t1'), SCOPE_IDENTITY(), @@IDENTITY

Once the first row is inserted, the current identity value will be set to 1.
As the statement in the query inserts a row to t1, scope_identity returns 1.  Also, as that is the last row to be inserted, @@Identity too will return 1


Now we'll do another experiment.
DROP TABLE t1
GO

SELECT IDENT_CURRENT('t1'), SCOPE_IDENTITY(), @@IDENTITY

Are tou receiving values different from what you expected?

1. Ident_Current gives null
2. Both Scope_Identity and @@Identity return a new value:1
   

As the table does not exist, Ident_Current returns null.
Scope_Identity returns a value even though the table does not have any values. Even if the table is not recreated, scope_identity returns the same value it returned when the table was existed.
@@Identity will return the last identity value inserted within the session. This is not tied to a table.  Currently even though we do not have any table with the identity column, @@Identity will return a value it got within the session.

These values will again reset to null when the session is closed.

I'll continue on Identity on future blogs as well.

New book from Red-Gate

Red-gate has released another free e-book recently: Brad's Sure Guide to SQL Server Maintenance Plans. This book is all about maintenance plans and covers both SQL Server 2005 and 2008. The book is designed not to just talk on theories but the practical aspects of maintenance plans. It is written in a way that new and accidental DBAs can understand things clearly. I recommend this book as a must have to all DBAs.

As you all know red-gate is committed to bring better products and bring better DBAs to the society. They have produced some great tools, out of which I love SQL Compare and SQL Data Compare. They also support many SQL Server related sites including www.sqlservercentral.com and www.simple-talk.com. Through their continuous support SQL Server Sri Lanka User group (SSSLUG) is able manage the monthly meetings. In addition to that they also produce great e-books.

I have not finished reading this book yet, but I need to admit that this book is well written.This book talks about many things you can do with maintenance plans.  If you are a DBA or want to be a DBA you need to study this book clearly.
Enough advertising :) You can get the book from this link

24 Hours of PASS: you can view the sessions now!

I am not sure how many of you remember about the 24 hours of online sessions which PASS had a few months before the PASS annual summit. I attended a few sessions including Kalen’s topic: “What’s simple about simple recovery model ?” But I couldn’t attend all the sessions and I had a few technical issues in connecting to the site as well. They were online events and initially they were not available for on-demand streaming. A couple of days later a couple of sessions were made available for on-demand streaming and PASS promised that all sessions will be available for on-demand streaming after the PASS annual summit.

The sessions are really valuable and they cover various tracks such as Development (Dev) Database Administration (DBA) Personal Development (PD) and Business Intelligence (BI)
I forget to check them later, but a few days back when I checked the site, they were made available.

Lies, damn lies and statistics

I was thinking for a long time to write something about statistics. In fact, I even know what to write: How much statistics are reliable and how they affect the query execution plans etc. I even thought of a heading: which you see now. But some or other reason, I didn’t do it.
Finally now I made it.

Statistics is an internal mechanism used by SQL Server to make decisions on query execution plans. For example, assume that we have a query like this (I am using AdventureWorks database which came with SQL Server 2005. It is available for download from Codeplex)

SELECT * FROM HumanResources.Employee WHERE ManagerID = 263

SELECT * FROM HumanResources.Employee WHERE ManagerID = 6

We have an index on ManagerID. Now the issue is you will get two possible (most likely) execution plans generated.