Contents tagged with SQL Server
-
TechEd 2008 Keynote Summary
Here are the highlights from the TechEd 2008 Keynote (as seen from afar by watching the TechEd 2008 Keynote and reading posts and press):
-
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 Server Analysis Services] - "Errors in the metadata manager" when restoring a backup
I had trouble restoring a SQL Server 2005 Analysis Services backup today due to "Errors in the metadata manager" messages:
The ddl2:MemberKeysUnique element at line 243, column 28420 (namespace http://schemas.microsoft.com/analysisservices/2003/engine/2) cannot appear under Load/ObjectDefinition/Dimension/Hierarchies/Hierarchy.
Errors in the metadata manager. An error occurred when instantiating a metadata object from the file, '\\?\C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\... -
SQL Puzzle #2 - Paging through a table with GO 10
I just wrote about using GO 10 to execute a T-SQL batch 10 times in SQL Server Management Studio. Could you use it to page through data?
-
[SQL] Using 'GO 100' to execute a batch 100 times
The GO statement is used by SQL Server as a batch terminator. It's recognized by by tools which run scripts like SSMS, SMO, and SQLCMD, but it's not technically T-SQL. SQL Server 2005 added a new little trick to the GO command which lets you specify a number of times to execute the batch: GO 10
-
[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.
