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.