When you register, you can choose to be a registered guest and have access to these articles,
or you can become a dues-paying member, with a great-many benefits, and get full access to everything on SSWUG and
many other benefits.
How To Collect Performance Data With TYPEPERF.EXE - (Ray Barley) As a DBA I like to take advantage of command line tools when I'm working on performance tuning and optimizing my SQL Server databases. One of the things I typically need to do is to... [more]
Database Audit Specifications in SQL Server 2008 - (Ashish Kumar Mehta) SQL Server 2008 introduces a new feature called SQL Server Audit. Auditing a SQL Server database involves tracking and logging events that occur on the database. This feature... [more]
SQL Server 2008 Management Tools - (Buck Woody) Microsoft has made several changes in the tools area for each release of SQL Server for the past few years. One of the largest jumps was from SQL Server 2000 to 2005. They changed th... [more]
Server Audit Specifications in SQL Server 2008 - (Ashish Kumar Mehta) SQL Server 2008 introduces a new feature called SQL Server Audit. Auditing an instance of SQL Server involves tracking and logging events that occur on the system. This featu... [more]
Should Microsoft provide a transaction log reader tool? - (Paul S. Randal) Now this one's sure to spark some controversy...
I was checking my RSS feeds of the SQL blogs that I follow and noticed an interesting post on Kalen's blog (the post is here).... [more]
Security in SQL Server 2005 Part II - (Vinod Kumar) In Part I on Security with SQL Server we did try to get into some aspects of security which as an administrator or developer one needs to know. We started with instance security, d... [more]
SQL Server Clustering and Consolidation - Balancing Resources - (Tim Ford) In my SQL Server environment we initially clustered to support a consolidation effort in which we were focusing on elimination of approximately thirteen Microsoft SQL Server 2000 and 2... [more]
SQL Server 2008 New Features - Row Constructors - (Nidal Arabi) Microsoft SQL Server 2008 with its release included some new features that some of us (as developers) will find interesting and useful. In this article, which will be followed by ot... [more]
Backup compression in SQL Server 2008 - (Tim Chapman) New to SQL Server 2008 is the native ability to compress database backups. This great new feature results in a smaller (sometimes much smaller) backup file, which saves disk space, ... [more]
SQL Server Hidden Stored Procedures - (Michael Ceranski) sp_msforeachdb and sp_msforeachtable are very powerful stored procedures. They allow you to loop through the databases and tables in your instance and run commands against them... [more]
How To Use the Analysis Services Execute DDL Task in SSIS 2005 - (Ray Barley) We are looking to automate some tasks to be performed on our SQL Server Analysis Services Servers. Can you give us the details on how the Analysis Services Execute DDL Task can be u... [more]
SQL Server Clustering Active vs Passive - (Tim Ford) Recently, I found myself rebuilding our production SQL Server 2005 cluster. Trust me when I say that a DBA does not take on a task like this because they want to. No, we encountered ... [more]
Should you upgrade to SQL Server 2005 or SQL Server 2008? - (Roman Rehak) Even though SQL Server 2005 was released almost three years ago, many companies, surprisingly, haven't made the upgrade from SQL Server 2000 just yet. Now those companies face anoth... [more]
How to tell if you have instant initialization enabled? - (Paul S. Randal) As you may already know, instant initialization is a way to prevent data (not log) file create and grow operations having to zero-initialize the new space before allowing it to b... [more]
Resource Governor in SQL Server 2008 - (Ashish Kumar Mehta) SQL Server 2008 introduces a new feature called Resource Governor which enables Database Administrators to manage SQL Server workload and critical system resource consumption... [more]
SQL Server 2008: The Debugger is back - If you have worked in SQL Server 2000 and were used to using the T-SQL debugger, you might have lamented its absence in SSMS in SQL Server 2005. In SQL Server 2005, one can debug the stored proce... [more]
Check your SQL Server using Windows PowerShell Part 4 - (Muthusamy Anantha Kumar) Part I of this series illustrated the first and foremost check on SQL Serverhow to ping the host. Part 2 examined how to check all of the windows services related to SQL... [more]
Deeper insight into unused indexes for SQL Server - One of the balancing acts of SQL Server is the use of indexes. Too few indexes can cause scans to occur which hurts performance and too many indexes causes overhead for index maintenance during ... [more]
Performance of inserts and IGNORE_DUP_KEY - (Alexander Kuznetsov) If your data has a small percentage of duplicates, then IGNORE_DUP_KEY may speed up your inserts. For larger amounts of duplicates, IGNORE_DUP_KEY may slow them down signifi... [more]
Partition Wizard Makes it Simple - (Allen White) The partitioned table was one of the great new features of SQL Server 2005. We could now manage data based on some range data, usually a date value, and keep the different ranges in... [more]
How do I Reseed a SQL Server identity column? - (Susan Harkins) Microsoft SQL Servers identity column generates sequential values for new records using a seed value. The term seed refers to the internal value SQL Server uses to generate the ne... [more]
Restoring Master - beyond the technical - (James Luetkehoelter) Thank Greg Low for reviewing my book and pointing out one of the copy-edit issues in it (I wasn't involved in that process by choice and schedule, a mistake I regret). As Gr... [more]
Avoid Untrusted Constraints in SQL Server - (Armando Prato) Some time ago, I loaded a large set of data into one my tables. To speed up the load, I disabled the FOREIGN KEY and CHECK constraints on the table and then re-enabled them after ... [more]
SQL Server 2008: Table Valued Parameters - (Kathi Kellenberger) In SQL Server 2005 and earlier, it is not possible to pass a table variable as a parameter to a stored procedure. When multiple rows of data to SQL Server need to send multip... [more]
Triggers...Evil? - (Louis Davidson) Say it isn't so. "It isn't so." Glenn Berry thinks so in his post here. When I read his post I thought I was going to really get into it with Conor's post here, called the Troub... [more]
Loading XML data into SQL Server 2008 - (Ashish Kumar Mehta) SQL Server DBAs will often come across a scenario where they need to load data from an XML document into SQL Server 2005 or 2008. As XML is being used widely by many organiza... [more]
New SQL Server 2008 Install Contains Some Useful Features - (Eric Johnson) The new SQL Server installation is so different, I thought I would take a second to write about it. For starters, the whole thing is now called the SQL Server Installation Center.
Create custom looping procedures in SQL Server 2005 - (Tim Chapman) In my previous SQL Server article, I discussed how you can use two undocumented system stored procedures to your advantage when it comes to executing ad-hoc statements against a lar... [more]
More on SQLDiag - We had briefly touched upon the usage of SQLDiag in some of our previous posts including the one here. While working on a client issue this weekend, it was surprising to see that none of the clie... [more]
Creating SQL Server Tables Part 1 - The fifth part of the SQL Server Programming Fundamentals tutorial looks at the creation of tables within a SQL Server database. Tables provide structured storage locations for all of the inform... [more]
Build a Generic Histogram Generator for SQL Server - (Michael Sorens) The histogram is a mathematical tool that is invaluable when analyzing large quantities of data. A histogram may be represented in a table of numbers or in a chart. Because datab... [more]
Interview With SSIS Guru Jamie Thomson - (Denis Gobo) I asked for some names of people who you would like to see interviewed here at Sqlblog and Jamie Thomson's name popped up a couple of times. I contacted Jamie and he was kind enough ... [more]
The SQL Server Sample Databases - pubs - (Buck Woody) Were in a series that explains the main sample databases that you can use with SQL Server, and in this tutorial Ill show you how to find, install and work with the first (and smalles... [more]
Database Normalization Basics - (Mike Chapple) If you've been working with databases for a while, chances are you've heard the term normalization. Perhaps someone's asked you "Is that database normalized?" or "Is that in BCNF?"... [more]
SQL Server Profiler Part 2 - (Don Schlichting) Profiler is tool that monitors the events and activity running on a SQL Server. Using profiler, this monitoring can be viewed, saved, and replayed. The results can be saved to a... [more]
sys.dm_exec_requests - (Aaron Bertrand) One of the dynamic management views (DMVs) that is very useful in troubleshooting query performance is sys.dm_exec_requests. The documentation around this DMV, however, is quite... [more]
SQL Server and .Net Data Type mapping - (Francois Malgreve) Today I would like to post a table listing the corresponding .Net Type to use with each SQL Data Type. This comes in handy when writing ADO.NET code.
System Data Collection Reports - (Greg Larsen) In my last article (http://sql-server-performance.com/articles/per/Management_Data_Warehouse_p1.aspx) I introduced one of the new SQL Server 2008 features known as the Management Da... [more]
Avoid Using Cursors - (Molly Pell) Transact-SQL cursors are used mainly in stored procedures, triggers, and Transact-SQL scripts in which they make the contents of a result set available to other Transact-SQL statemen... [more]
Rebuilding the SQL Server master database - Part 1 - (Tim Ford) I recently found myself in a situation where I had to rebuild the master database on one of the nodes of our development SQL 2005 cluster. I had to do so because there was a server co... [more]
SQL Server 2008: Spatial Data, Part 8 - (Jason Follas) In this, the eighth part in a series on the new Spatial Data types in SQL Server 2008, I'll step away from the database and do a little spatial coding using .NET.
How to setup SQL Server alerts and email operator notifications - (David Bird) Setting up an alert to e-mail an operator with a message is a multiple step process. You can setup database mail, define an operator and an alert, but still no e-mail is being sent. ... [more]
Analytic Functions and sorting on a constant value - We have covered analytic functions (ROW_NUMBER(), RANK(), DENSE_RANK() etc.) in our posts and whitepapers before. A requirement for these window functions also is that you need to have an order b... [more]
A first look at SQL Server 2008 (part 1 of 3) - (Ken Casada) During the last days I found the time to have a first look at SQL Server 2008. In this 3 parts blog (Ill post the second one the coming week) I will summarize some of the most intere... [more]
Data Mining with SQL Server 2005 - (The JZ) In my research, we do a lot of data mining and machine learning. While we do not employ SQL Server 2005 to perform the operations, the underlying concepts are the same. Data mining can r... [more]
White Paper: Security Overview for Database Administrators - SQL Server 2008 is secure by design, default, and deployment. Microsoft is committed to communicating information about threats, countermeasures, and security enhancements as necessary to keep yo... [more]
Introduction to SQL 2005 Profiler Part 1 - (Don Schlichting) Profiler is a tool that monitors the events and activity running on a SQL Server. Using profiler, this monitoring can be viewed, saved, and replayed. The results can be saved to... [more]
Problem using DISTINCT in case insensitive SQL Server databases - SQL Server gives you the ability to store mixed case data in your databases, but depending on how you create your databases SQL Server will ignore the case when you issue T-SQL commands. One of ... [more]
Automate Audit Requests - (TJay Belt) Periodically, an internal auditor would stop by my desk and ask me to show them all the users and permissions on a particular database. This is easily gathered, and most of us know h... [more]
Execute SQL statements in Integration Services - (Tim Chapman) Executing SQL statements is one of the easiest and most common tasks that occur in SQL Server 2005 Integration Services (SSIS). This tutorial walks you through the process of adding... [more]
Reuse Your Code with CROSS APPLY - (Alexander Kuznetsov) Suppose you are using SQL Server 2005, and you want to truncate your DATETIME value, leaving only date part of it, as follows:
DATEADD(day, DATEDIFF(day, '19010101', Last... [more]
Loading Excel values to database - (onetidbit) If you want to load the excel values to database (eg:SQL server database) follow the steps:
Using xp_ReadErrorLog in SQL Server 2005 - (Dan McClain) I would like to share some interesting parameters I found for the undocumented extended stored procedure xp_ReadErrorLog. In doing some testing with this extended stored procedure I... [more]
Sarbanes-Oxley compliance checklist: IT security and SQL audits - (Michelle Gutzait) My company needs to meet Sarbanes-Oxley compliance for all its Information Technology (IT) department layers and products, including SQL Server. We're running SQL Server 2000 a... [more]
SQL Server Management Studio Reports and Dashboard - (Don Schlichting) SQL Server 2005 and SQL Server 2008 provide graphical reports for monitoring system health and performance. While there have always been various queries, stored procedures, and ... [more]
Convert numbers to fractions using SQL Server - (Tim Chapman) I recently worked on an interesting problem that involved converting a decimal number to a fraction value in SQL Server. At first glance, it seemed reasonably simple, but after digg... [more]
Installing Sql Server 2005 AFTER Visual Studio 2005 / 2008 - (James Curtis) If you have had the wonderful opportunity of trying to install Sql Server 2005 on a development machine after you have already had Visual Studio 2005 installed, you know that you c... [more]
High Availability with SQL Server and Double-Take Part III - (Steven Warren) In Part II of High Availability with SQL Server and Double-Take, we discussed how to configure and test a typical failover scenario using Double-Take. At this point, you should no... [more]
Generate synchronization scripts in SQL Server with TableDiff - (Tim Chapman) DBAs often have to find the differences between lookup tables in different database environments (i.e., development, quality-assurance, staging, and production). The data in these l... [more]
Dynamic SQL, Dynamic Ordering and Paging - In some of our previous posts, we have covered both Paging of recordsets as well as conditional ordering using the CASE statement in the Order By clause. In today’s post, we will address one of t... [more]
SQL Server 2008: Sparse Columns - (Paul S. Randal) It's been quite a while since I wrote a blog post on a flight but I happened to be playing with a CTP-6 VPC on the way down to Orlando and thought I'd do the first in a few posts... [more]
UPSERT Functionality in SQL Server 2008 - (Muthusamy Anantha Kumar) The UPSERT command inserts rows that don’t exist and updates the rows that do exist. The Word UPSERT is a fusion of the words UPDATE and INSERT. UPSERT was officially in... [more]
Translate Microsoft SQL (MSSQL) To MySQL - (Ben Nadel) This is going to be a post that I update from time to time and I just wanted to kick if off right now. For years, I have been working on Microsoft SQL Server and I have reached what I... [more]
Simplify SQL Server 2005 queries with a Dates table - (Tim Chapman) I find that it can be advantageous to use a Dates table, especially when doing a lot of date calculations. Learn what a Dates table is and how to create one, and then try it out for... [more]
Issues with running backup log with no_log or truncate_only - On my database server I have my databases set to the full recovery model, but the transaction logs get quite big, so I am issuing a BACKUP LOG with NO_LOG. I am not exactly sure if this causes a... [more]
Interesting observation with table > 1TB - (Kevin Cox) We have a project using SQL 2005 with a table that is several terabytes. This table has a varbinary(max) column with an average length of 150k bytes.
Enabling Mixed Mode Authentication in SQL Server - (Brendan) I recently needed to enable Mixed Mode Authentication on a SQL Server instance, so I will demonstrate how to go about setting this up. It is a pretty easy process, but I figure I'll doc... [more]
SQL Server Database Requirements - In our organization, I have noticed that database requirements are never included as a portion of the system requirements. The requirements always focus on the interface and we derive the databa... [more]
Can your sum be a subtraction? Or can you avoid it altogether? - (Chris Webb) Quite often you'll find yourself writing calculations that need to sum up large sets; in fact, they might be summing up all of the members on a level apart from one or two. In that s... [more]
CHECKSUM and Tempdb - (Sunil Agarwal) You may recall that starting with SQL Server 2005, you have an option available to enable CHECKSUM on the user databases. For details, please refer to http://blogs.msdn.com/sqlse... [more]
SQL Server Index Checklist - Indexing a SQL Server database in some respects is considered both an art and a science. Since this is the case, what are some considerations when designing indexes for a new database or an exis... [more]
= Members-Only 
= Registered Guests