Feb 16, 2010
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
Feb 15, 2010
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.
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.
Feb 11, 2010
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:
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)
- 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
- 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.
Feb 10, 2010
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:
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. :)
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
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.
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.
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. :)
Feb 9, 2010
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
Employee Table
Now you want the results like this:
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 |
Feb 6, 2010
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.
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?
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?
Feb 5, 2010
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.
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.
Subscribe to:
Posts (Atom)