SQL - Jon Galloway

Browse by Tags

All Tags » SQL (RSS)

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...
Posted by Jon Galloway | 124 comment(s)
Filed under: ,

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...
Posted by Jon Galloway | 9 comment(s)
Filed under: ,

[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...
Posted by Jon Galloway | with no comments
Filed under: ,

[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...

Passing lists to SQL Server 2005 with XML Parameters

Overview SQL Server 2005's XML capabilities make it a easier to pass lists to SQL Server procedures. Background I recently needed to write a stored procedure which took a list of ID's as a parameter. That's one of those things that seems like...
Posted by Jon Galloway | 62 comment(s)
Filed under: ,

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...
Posted by Jon Galloway | 2 comment(s)
Filed under: ,

[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...

SQL Server recovery model selection (or, what's this 3GB LDF file?!)

SQL Server recovery models can be a bit tricky. By default, SQL Server databases are set to Full Recovery mode, which means they maintain sufficient logs to restore to any specific point in time. These logs can grow to several GB for a small, lightly...
Posted by Jon Galloway | 1 comment(s)
Filed under: ,

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: Extract of the PowerPoint outline: What...
Posted by Jon Galloway | 1 comment(s)
Filed under: ,

Speaking: 11/28 - San Diego .NET User Group

I'll be speaking at the San Diego .NET User Group on 11/28 : Jon has been putting some new and lesser used features in SQL Server to work to solve some tough business problems lately. He will review SQL Server Management Objects (SMO), INFORMATION_SCHEMA...
Posted by Jon Galloway | with no comments
Filed under: , ,
More Posts Next page »