Friday, June 27, 2008
As database administrators and developers, it's our job to know the in's and out's of the data in our charge. Whether we're designing entity relationships to support underlying business requirements, optimizing indexes to achieve the best response times, or writing a query to retrieve the right data in the most efficient manner, having an intimate knowledge of the database is key.
However, since few of us are responsible for just one database, it's sometimes difficult to gain, much less maintain, that level of familiarity with our data.
Fortunately for us, however, we have the metadata available to us. We can use SQL Server's system tables and views to seek out the information we need to help us do our jobs.
For example, I was recently asked by a client to begin considering what it would take to retrofit replication into an existing application. Now obviously it's best to know before the design phase ever begins that replication may be an option, but atlas hindsight is always 20-20 and circumstances change.
One of my first thoughts was that of identity columns. If identity columns are widespread throughout a database design, that must be addressed as replication is considered.
So, how many tables in my client's employ identity columns? Good question. I don't know off-hand. But fortunately the information is at my fingertips.
The following query can be used to list all tables in a database where an identity column is defined.
SELECT
t.TABLE_NAME
,c.COLUMN_NAME
,c.TABLE_CATALOG
,c.TABLE_SCHEMA
FROM
INFORMATION_SCHEMA.COLUMNS AS c JOIN
INFORMATION_SCHEMA.TABLES AS t
ON t.TABLE_NAME = c.TABLE_NAME
WHERE
COLUMNPROPERTY(OBJECT_ID(c.TABLE_NAME)
,c.COLUMN_NAME,'IsIdentity') = 1 AND
t.TABLE_TYPE = 'Base Table' AND
t.TABLE_NAME NOT LIKE 'dt%' AND
t.TABLE_NAME NOT LIKE 'MS%' AND
t.TABLE_NAME NOT LIKE 'syncobj_%'
Running the query in the AdventureWorksDW database produces the following results.
Cheers!
Joe
Thursday, June 19, 2008
I was searching for something online the other day and stumbled upon this TechNet article -
Working with tempdb in SQL Server 2005. It's a good little article and I wanted to share it with you.
Enjoy!
Joe
Friday, May 30, 2008
I was recently perusing the the MSDN SQL Server forums and noticed a question about localization of Microsoft SQL Server messages. I replied to the post with a mention of the sys.messages table - the table where SQL Server messages are stored. I blogged about this system table a few months ago.
Afterward I started playing around some more with the sys.messages table. I started with the following basic query.
The first thing to notice is that each message_id has one or more languages associated with it. Message_id 21, for example, has a row for language_id 1033, 1031, 1036, etc. It's fairly obvious to me that language_id 1033 is my native tongue, English. And I can make some educated guesses on some of the other languages. But where are they defined?
Well, the syslanguages system table contains a list of each language that SQL Server recognizes. For example, run the following query in a Management Studio Query Window.
1033 is indeed US English. And 1031 is German, 1036 is French, and so on. There are 33 languages in all - US English and British English are considered two different languages in here, though they share the same messages.
But what's interesting is that not all languages in the syslanguages system table are represented in the sys.messages table. For example, consider the following query.
SELECT
m.language_id
,l.alias
,COUNT(*)
FROM
sys.messages AS m JOIN
syslanguages AS l
ON l.msglangid = m.language_id
GROUP BY
l.alias
,m.language_id
The results indicate that only 11 languages (10 if you consider US English and British English to be one and the same) are represented in the sys.messages table.
So, that must mean that the other languages don't have a need for messages. :) Right, if that's that case, remove US English from the list!
Actually if a language is not present in the sys.messages table, a default language will be used instead. For more information, check out the sp_addmessage procedure and the sys.messasges system table in Books Online.
Cheers!
Joe
Monday, May 19, 2008
Over the years, I've had the opportunity to attend, and even speak at, many technical conferences both here in the United States and abroad. Some of the conferences had only a couple of hundred attendees while others boasted of attendance figures in 5 digit range.
For the two of the six years I spent on the
PASS (Professional Association for SQL Server) Board of Directors, I served as Director of Logistics and Conference Operations. I arranged meeting space and selected food, contracted for wireless and bought conference bags. My final years of the Board was spent serving as the Executive Vice President of Finance where I became even more intimately aware of the high costs of putting on a quality conference.
I mention all this, not to heap self-serving platitudes upon myself, but rather to let you know that I have somewhat of an insider's perspective on the challenges of creating a good conference experience. There are many moving parts. So many variables that it's almost like trying to herd cats.
Last week I was in Toronto for the
DevTeach/
SQLTeach conference. This was my third year to be there. The conference, which attracts 200 to 300 people, has a very warm and intimate feel about it. There is a real sense of community exuded by the attendees, the speakers, and the volunteers that make it all happen.
But the small size does not mean a lower quality conference. The food was good; the exhibit area was intermingled in the common area so it was easy to spend time with the exhibitors; and the wireless, conference bags, and program was on par with the conference's much larger counterparts.
The primary reason most any of us attend a conference is to improve our knowledge. And the caliber of speakers and quality of presentations at DevTeach/SQLTeach was outstanding.
Although the conference offers eight simultaneous tracks, ranging from Sharepoint to .NET, from Agile to Architecture, I spent most of my time in the SQL Server tracks. And I was not disappointed. I watched as Rick Heiges artfully explained SQL Server on the 64-bit environment, Peter DeBetta showcased the new features of SQL Server 2008 for developers, and Brad McGehee examine the SQL Server 2008 Performance Data Collector. And this was but a
few of the eye catching sessions there were available.
Our friends in the financial services industry like to remind us "Past performance is no guarantee of future results", but given my experiences at DevTeach/SQLTeach, I definitely plan to go back!
If you've attended on the DevTeach/SQLTeach conferences, I'd love to hear your experiences. Just post a comment.
Cheers!
Joe
Thursday, May 15, 2008
Here's a presentation that I gave yesterday afternoon at
DevTeach/SQLTeach. It's called Tips & Tricks for Writing Better Queries. It's very similar to the presentation I gave a month or so ago at the
Nashville SQL Server User Group meeting.
To everyone who attended the session - thanks for spending a part of your day with me. I hope it was worth your while. Please feel free to email me if you have any additional questions or comments. And please remember to login to your SQLTeach account and submit your session evals.
Cheers!
Joe
Wednesday, May 14, 2008
Thanks to everyone who attended my "Identifying Performance Bottlenecks" session this morning at DevTeach/SQLTeach. As promised, here is the PowerPoint slide deck I used during the presentation.
Regarding the question of on the performance of LINQ, Bob Beauchemin recently posted a series of blogs entries on the topic. Here's a link.
And here's a link to series of posts on the Dynamic Management Views (DMVs) that Louis Davidson authored.
Cheers!
Joe
Wednesday, May 07, 2008
The nomenclature used for referencing software releases and updates can be quite confusing. Let's consider an example to illustrate what the various terms mean.
As a new version of a product is being developed, it may be made available to select customers and community members for early testing. This is sometimes called alpha builds of the product.
As development progresses and the product becomes more and more polished, it's provided to a wider audience. This used to be called beta releases; for example beta 1, beta 2, etc. However a few years ago Microsoft changed the terminology for SQL Server pre-releases. They are now referred to as CTPs (Community Technology Previews). You can download the November CTP, for example.
As the product enters it's final stages before release, the feature set is complete and the product is undergoing final testing, it's called an RC (Release Candidate).
After a product has undergone significant testing and it's determined that no more changes will be made to the product before release, it's sometimes said that the product has gone golden. It's also called a GA (General Availability) release.
Once the bits been turned over to a company to mass produce the media (CDs, DVDs, etc), it's RTM'd (Released To Manufacturing).
Usually sometime around the RTM, the product version is "launched". The timing of the launch may or may not have any correlation with the time the product is actually available for purchase. The launch has more to do with marketing and product feature education than availability.
Finally the product is released! It's available for purchase from the normal distribution channels.
Over time, Hot Fixes are created by the dev team to address specific product issues affecting certain customers. Sometimes the issue is so wide spread, a GDR (General Distribution Release) is issued so that all customers will receive the updates.
Since hot fixes and GDRs are designed to quickly address specific problems encountered by specific customers, they can be issued rather often. The rapidity of the hot fixes and GDR's makes it impractical for many IT shops to keep up with the pace of the releases. So, a CU (Cumulative Update) is created that contains all of the applicable hot fixes. This makes it easier for customers who haven't been directly affected by the issues that sparked the hot fixes to remain current.
Once a large enough collection of changes have been gathered, an SP (Service Pack) will be issued. Historically, SPs have also been the release vehicle used to deliver new features that were not ready at the time of GA. For example, Database Mirroring was made available in SP1. SP2 brought us the custom reports as in the Performance Dashboard. Microsoft has since indicated that SPs will not be used as a release vehicle for new features.
And then the whole cycle starts overs. Feel like you're on a treadmill yet?
For more information, check out the following links:
Cheers!
Joe
Tuesday, May 06, 2008
As most you already know, Notification Services is not part of Microsoft SQL Server 2008. I think this is a shame since SSNS is really a great product. It may be rather complex at first glance, but it's a great product nonetheless. I'll probably devote a blog to it's abrupt deprecation at some point in the future.
In the meantime, if you're preparing to upgrade to SQL Server 2008, you should verify that you don't have any rogue SSNS instances running on any of your servers. I hope your environment is more controlled than that, but I've seen more than one shop where change control was, well in a word, uncontrolled.
So, how can you tell if a server has a SSNS installed on it? Run the following query in the msdb database. It'll list all SSNS instances installed on that SQL Server instance.
SELECT
InstanceName
,DatabaseName
FROM msdb.NS90.NSInstances
Cheers!
Joe
Sunday, April 27, 2008
Ocassionally, I see a question in the MSDN Forums where someone wants to know how to retrieve a list of column information for a table using only T-SQL.
Although there are several ways in which to approach this, I like the following method.
SELECT
ORDINAL_POSITION
,COLUMN_NAME
,DATA_TYPE
,CHARACTER_MAXIMUM_LENGTH
,IS_NULLABLE
,COLUMN_DEFAULT
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = 'Product'
ORDER BY
ORDINAL_POSITION ASC;
Do you have a different way of doing this that you'd like to share? If so, feel free to leave a comment, sharing your way and why you like it.
Cheers!
Joe
Wednesday, April 23, 2008
I hope that everyone that works with Microsoft SQL Server is aware of the
MSDN SQL Server Forums. If not, you should really go check them out. They're a great resource when you're staring at a tough SQL Server-related problem.
Now, some of the Moderators, Answerers, and MVPs have gotten together to create a new resource to supplement the Forums; it's called
SQLExamples. We just started it a few weeks ago so it's still very much in its infancy and somewhat sparsely populated. But it's growing quickly. I think it'll be a boon for SQL Server professionals the world over before long.
Check it out and let me know what you think.
Cheers!
Joe
