Contents tagged with SQL
-
Adding simple trigger-based auditing to your SQL Server database
How do you track changes to data in your database? There are a variety of supported auditing methods for SQL Server, including comprehensive C2 security auditing, but what do you do if you're solving a business rather than a security problem, and you're interested in tracking the following kinds of information:
-
The real reason SELECT * queries are bad: index coverage
Are SELECT * queries bad? Sure, everyone know that. But, why?
It's returning too much data, right?
That's the common answer, but I don't think it's the right one. If you're working with a reasonably normalized database, the actual network traffic difference is pretty small.
Let's take a look at a sample. The following two queries select 326 rows from the TransactionHistoryArchive table in the AdventureWorks database (which has a total of 89K rows). The first uses a SELECT * query, the second selects a specific column:
-
[T-SQL] Getting distinct, current entries from tables with timestamp / datetime fields
It's relatively easy to store data with a time dimension, but querying it is another matter. If you select from a temporal tables (one which includes historical information indicated by a timestamp or datetime column) based on your ID, you'll get a lot of duplicate records; only one or a few of those records will be applicable to a given time or timespan.
-
[SQL] Force the protocol (TCP, Named Pipes, etc.) in your connection string
Barry Dorrans recently mentioned that you can force the database connection protocol by specifying np: or tcp: before the server name in your connection string. I've jumped through some hoops before using localhost to target tcp and (local) to target named pipes, but it looks like there's a much better way to do this (since MDAC 2.6).
-
Passing lists to SQL Server 2005 with XML Parameters
-
sysobjects, sys.objects... what about INFORMATION_SCHEMA?
SQL Server Management Objects (SMO) is one of the coolest parts of SQL Server 2005, in my opinion. I've written about SMO before, and used it extensively in writing Data Dictionary Creator. In a nutshell, SMO is a collection of objects that are designed for programming all aspects of managing Microsoft SQL Server.
-
[tip] Use the Database Publishing Wizard to script your table data
Jeff Atwood recently asked "Is your database under version control?" Well, is it? It's not as easy as it sounds. Until now, there wasn't a good, scriptable solution to the problem. I'll run through a few of the options I've looked at in the past, but let's jump to to the punchline:
-
SQL Server recovery model selection (or, what's this 3GB LDF file?!)
-
Notes for 11/28 Talk - SMO and INFORMATION_SCHEMA
I spoke at the San Diego .NET User Group meetin on 11/28 on SQL Server Metadata (SMO and INFORMATION_SCHEMA). The complete notes should be available on the user group downloads page, but here are the rough notes:
-
Speaking: 11/28 - San Diego .NET User Group
I'll be speaking at the San Diego .NET User Group on 11/28: