Finding out information about database settings is not that hard when you use Enterprise Manager or Management Studio, T-SQL commands such as sp_helpdb or query the system tables directly. But the way the information is returned may not always be the mos
In this article, we look at how to perform a SQL case sensitive search when the SQL Server database has been setup as case insensitive.
From SQL Server 2000 to 2005, many of the core SQL Server system metrics have migrated from static commands to dynamic management views and functions that can be queried to gather statistics in real time. The new dynamic management views and functions of
The principal of least privileges is a cornerstone to most security implementations. The premise behind the principal is to only grant users, developers, DBAs, network administrators, etc. the needed rights and nothing more. With SQL Server 2000 and 2005
There are several useful commands and functions that are available in SQL Server, but not knowing what they are or where to find more information about them is sometimes a problem. Having these commands at your fingertips is very helpful when trying to s
In SQL Server 2000 when creating an index, the max degree of parallelism or MAXDOP configuration was based on the server configuration. With SQL Server 2005, you are no longer constrained by the default settings. The MAXDOP value can be configured each ti
Learn how to use SQL try catch for effective error handling in SQL Server stored procedures and improve your code quality.
Sometimes there is a need to manipulate string values using T-SQL code. With other languages such as Visual Basic, C++, C#, VBScript, etc... there are a lot of commands at your finger tips. With SQL Server 2000 you don't have all the same options, but t
In this tip we look at how to construct a SQL Server PIVOT query with an example and explanation.
Getting insight into a SQL Server issue can be difficult without a comprehensive set of information. One option to capture a comprehensive set of SQL Server system information for troubleshooting is via 1 or more of the Microsoft Product Support (MPS) Rep
Determining if two rows or expressions are equal can be a difficult and resource intensive. To address this need the CHECKSUM, CHECKSUM_AGG and BINARY_CHECKSUM functions are available in SQL Server 2005 to natively create a unique expression, row or table
Many of the objects in SQL 2000 DTS Packages require authentication to SQL Server when extracting, transforming and loading data. It is quick and easy to just add your SQL Server user name and password, but this can become a management issue. One alterna
Database fragmentation is a hindrance to SQL Server performance, because with excessive fragmentation more data pages need to be traversed to fulfill a query request. Luckily, fragmentation is a manageable problem that is resolved by re-building the inde
System and application log data is essential when troubleshooting a SQL Server issue. In SQL Server 2000 this was a bit cumbersome, because critical data had to be reviewed from multiple interfaces. With SQL Server 2005 the Management Studio ships with th
Learn how to enable xp_cmdshell for SQL Server and some of the errors you may encounter when using xp_cmdshell as well as how to disable xp_cmdshell.
Index rebuilds are a core component for database maintenance which ultimately improve performance and the user experience. DBAs and Developers alike have become accustom to much of the syntax in SQL Server 2000 and know the purpose for each set of code.
We have been running SQL Server 2000 for a few years and the transaction log file has become very large for some of our databases. In some circumstances, the transaction log is a more than 5 times larger than our database. How can I reduce the size of th
With any database systems there are a couple of key items that help with performance. These items include: good database design, properly written SQL code, correct indexes, current statistics and defragmented indexes and data. The last two items can be h
Using Query Analyzer is a great tool for testing queries, running ad hoc queries, troubleshooting queries etc... There is a lot of functionality as well as a lot of shortcuts built into the tool, but finding the shortcuts or remembering them is sometimes
In this article we look at how to create a SQL Server database snapshot and how snapshots can be used for reporting, auditing and more.
In this tip we look at how to randomly retrieve a few records from a SQL Server table.
In this tip we show where you set SQL Server startup parameters from SQL Server 2000 to SQL Server 2017.
In this tip we look at how you can use fn_get_sql to return the entire contents of a SQL Server query for a process that is running or just finished.
Have you ever wanted to or needed to run a DTS package from outside of SQL Server? This could be from an application you develop or even just from a command line. Well SQL Server gives you the ability to run a DTS package by using the DTSRUN command in