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