SQL Server Books Online (BOL) suggests that in order to clean up old partitions from a partition table, you should either issue a DELETE or SWITCH command. BOL also suggests that while SWITCHING partitions, you should set up a NON PARTITIONED TABLE in the
In the Granting permission with the EXECUTE AS command in SQL Server 2005 tip, you have seen how you can grant granular permissions using the EXECUTE AS clause. Context switching within a programmable object such as a stored procedure or function is impor
In this article we look at the DBCC PAGE command to look at what data is stored in a specific page in a SQL Server database along with examples.
Microsoft products such as Windows Server Update Services (WSUS) 3.0 and Windows Sharepoint Services (WSS) 3.0 ship with SQL Server 2005 Embedded Edition. Now called the Windows Internal Database, more and more system administrators charged with managing
On my new job I faced a situation where archived tables were created in the production database and now there was a requirement to move them as the database was growing. These archival tables were being used by several jobs and also in the application cod
As a DBA I like to take advantage of command line tools when I am working on performance tuning and optimizing my SQL Server databases. One of the things I typically need to do is to collect performance data on the server which includes CPU, memory and d
I have a stored procedure I want to run when SQL Server starts. Is there a way to execute this procedure automatically each time the SQL Server service is started?
SQL Server 2005 supports Database Instant File Initialization which skips zeroing out data pages that can reduce the time when performing operations like creating databases, adding files to an existing database, increasing the size of an existing database
In the Auditing DDL (Create, Alter, Drop) Commands in SQL Server 2005 tip, you have seen how you can track DDL activities in your server instance. But how do we store the events captured by these DDL triggers and store it in a table for reporting purposes
I am trying to pass a comma delimited list of values into a stored procedure to limit the result set. Whenever I use the variable in the IN clause I get an error message. Is there a way to do this without using Dynamic SQL?
There is something to be said for taking a vacation. The rest and relaxation, seeing new places, being exposed to new and exciting things. The other side of the coin is being on-call, answering pages at 3:00 AM then driving around a strange location hun
I've been working remotely (also known as Telecommuting) for over 8 years now. While it sounds great: Hey I get to work from home! telecommuting effectively takes a great deal of work. You can't just phone it in (sorry I could not resist the pun). B
In a previous tip you discussed overriding the MaxTokenSize to accommodate larger Kerberos tokens due to membership of a large number of groups in Active Directory. Right around the same we noticed that the MaxTokenSize needed to be adjusted, issues with
SQL Server Security, probably one of the most controversial and debated topics among SQL Server DBAs and Developers. One person's security is another person's nightmare and vice versa. With security being so important for so many different reasons let's
I am frequently asked to translate the databases that reside on a physical server / SQL instance into their corresponding application names. Typically this need arises when preparing planned downtime notifications for servers, but it also is valuable whe
Database Mirroring was released with SQL Server 2005 and is becoming a popular choice for a failover solution. One of the big issues with Database Mirroring is that the failover is tied to one database, so if there is a database failure the mirrored serv
We have chosen MOLAP storage in order to maximize the query performance of our cubes. Since we have a number of cubes we are now focused on coming up with a strategy for keeping the cubes up to date as the data in our warehouse changes frequently. Can y
In this article, we example whether it is better to use a UNIQUE Constraint or a Unique Index for SQL Server design and performance.
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 2005 instances. Just because you have disk space, plenty of memory, and
We are looking to automate the processing of our SQL Server Analysis Services dimensions and cubes. We'd like to add this processing to our existing SQL Server Integration Services (SSIS) packages which periodically update our data warehouse from our OLT
Lately it seems like SQL Injection attacks have been increasing. Recently our team has worked through resolving a few different SQL Injection attacks across a variety of web sites. Each of these attacks had a number of similarities which proved to point
DBA's and web developers at our company are experiencing issues with connecting to SQL instances using SQL Server Management Studio and other SQL tools using Windows Integrated Authentication. Our company is large, with well over 70,000 users and groups i
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 used in a SQL Server Integration Services (SSIS) package?
I have heard of table variables, but not sure how to use them in a stored procedure. What purpose do they serve and why not just use temporary tables instead?