This topic is nothing new, but well worth revisiting for those who have not used CROSS JOINS. You may have faced a problem where you need to compare historical records for a particular set of records to ensure that records have been saved in the correct o
Our clustered Microsoft SQL Server environment hosts over 200 databases currently across all nodes. Whereas clustering technology has been around for quite some time now, many applications still are not cluster-aware. The problem we encountered was with
Learn how to write to operating system files from SQL Server using a CLR.
The cache management mechanism plays an important role in performance of any system. Like any reliable DBMS, SQL Server enjoys a sophisticated cache management system for optimized performance without the need for any user intervention. There are ways to
T-SQL scripts and stored procedures don’t produce their PRINT statements and RAISERROR output in a way that keeps you informed of the code’s progress. You usually have to wait until the procedure is complete before seeing messages. How can I get insight
In a previous tip on Switching Execution Context using the REVERT clause, you have seen how to switch execution context to a more privileged user and revert back to the original context of the caller within a programmable object. The REVERT clause accepts
I have inherited a Microsoft SQL clustered server and I don't know much about it's logical structure. It is a two node, active-active cluster and I am trying to determine which of the logical volumes on the shared disk are associated with each SQL Server
In this tip we look at how to query Active Directory Users and Groups with SQL Server Integration Services.
In a previous tip on Auditing SELECT statements in SQL Server 2008, you have seen how you can use SQL Server Management Studio to audit SELECT statements in SQL Server 2008. While it is easy to use the graphical user interface to create audit objects, it
In this article, we look at how to use T-SQL code to find a string value in a SQL Server table.
In SQL Server 2000 and below, I relied on sp_helprotect, sp_helprolemember, and sp_helpsrvrolemember to be able to report on all the permissions a given login or user had. However, I'm now supporting SQL Server 2005/2008 and I have read how sp_helprotect
Discover dba best practices that can ease your database responsibilities and improve your SQL Server knowledge and skills.
We have a two node SQL Server cluster in our organization. We are running an instance of Microsoft SQL Server on each node in the cluster. I am coming up to speed on the Cluster Administrator tool and I am looking for a process by which I can check the
I am designing a table and I am trying to enforce domain integrity on some of my columns using CHECK constraints. Can you give me some examples of using CHECK constraints to constrain a single column and to constrain a column based on the value in another
Management has asked that I audit when data is read from a table in the database. I can't use triggers, because they don't fire on SELECT statements. Is there a method other than running a SQL Server Profiler or server side trace to audit when SELECTs are
I've installed a SQL Server 2005 cluster and now I have to administer the darn thing. I am comfortable within SQL Server and the management tools that wrap around it, but I've been told by the Server Team in our organization that I need to be able to use
Your company is considering a disaster recovery solution for SQL Server 2005 and 2008 instances. You have been asked to analyze the various options and report in detail which method is the best for your company. Over the course of the analysis you are al
Learn different ways to execute SQL Server SELECT statements without locking and causing blocking issues.
In a previous tip on SQL Server 2005 Disaster Recovery Procedures, we have seen how we can build a disaster recovery procedures. There are other ways to increase availability of your critical databases in SQL Server with Page Level Restore.
We are in the process of trying to hire a Junior SQL Server Developer to work on a few of our projects. What expectations should we have on their skill set? What are some fair questions to ask them during the interview process? With a junior level deve
OPENROWSET is a T-SQL function that allows for reading data from many sources including using the SQL Server’s BULK import capability.
When creating tables it is difficult to determine exactly how the data will be accessed. Therefore when clustered indexes are chosen they are often just the ID column that makes the row unique. This may be a good choice, but once the application has bee
We use the FTP task in SSIS to process a number of files from an FTP server. We would like to implement a step in our SSIS packages that would retrieve the list of files that are available on the FTP server before we try to process them. The FTP task do
In this article we look at SQL Server sparse columns and how to identify columns that are good candidates to be sparse columns.