|
|
SQL Server 2008 introduced four new data types related to date and time: date, time, datetime2, and datetimeoffset. In this first installation, we cover time and datetime2. In the follow-up, we will cover date and datetimeoffset. We will use code to see how they work with some of our favorite date/time-related functions and a couple of new functions. Most of the results are as-expected; a few are surprising.
Read More
You’ve probably found that the prefix “lead” is a fairly common occurrence in the technology world. We have “lead developers”, “lead DBAs”, “lead architects”, and “lead consultants”. Yet, we don’t have “lead managers”, “lead directors”, or “lead VPs”. Why is that?
Read More
Thomas has been running SQL Server 2008 live in production for over 1 year as part of the Microsoft Early Adopter Program. Below is the full interview I conducted with him about his experience with SQL Server 2008.
Read More
RAID: An Ounce of Prevention
By Michael Coles
There are hundreds of ways to lose your data, from extremely dramatic natural disasters to far more ordinary basic equipment failures. The many moving mechanical parts contained in modern hard drives, including rotating platters on spindles and movable read-write heads (see Figure 1), make hard drive ... Read More
Understanding Query Execution Plans to Optimize Performance
By Scott Klein
One of the most difficult tasks that faces a T-SQL developer is that of tuning their queries for optimal performance. A poorly performing query can not only seriously degrade the operations on your SQL Server, the users that are waiting on the data being returned by the qu... Read More
XML SchemaBinding
By Wayne Fillis
Introduction SQL Server 2005 introduced exciting enhancements to SQL Server’s XML functionality, one of the most important being the addition of an XML data type. XML can now be stored in its native format as a variable or a table column. Additionally, SQL2005 provided support for XML Schemas, XSLT stylesheets, a... Read More
Using T-SQL CodeGen for Audit Trails
By Paul Nielsen
From time to time over the years I’ve obsessed on writing better audit trails. Maybe it’s my history as a Data Systems Tech in the Navy, maybe I just like having proof that the database worked as advertised, but seeing the full history for any row makes good sense to me. There are several types... Read More
SQL Server Security for the Paranoid DBA
By James Luetkehoelter
Do you lie awake at night wondering how secure your SQL Server is? I do. And if you don’t, perhaps you should. Yes, Windows Server 2003 and SQL Server 2005 make great advances in security, but a truly secure SQL Server installation goes well beyond ensuring that Windows 2003 and SQL ... Read More
Row-Level Security Using Windows Groups
By K. Brian Kelley
When it comes to securing data, sometimes the best option is to use row-level security. This is where we make a determination row-by-row on whether or not a given user can see the data in the table. For instance, if we have one large Customer table and we have numerous sales reps, we may ... Read More
Performance Tuning Using SQL Profiler
By K. Brian Kelley
Performance Monitor, a free tool with the Microsoft Windows operating systems, is an invaluable tool when locating performance bottlenecks. While this may be the primary tool of the system administrator and a valuable resource for the SQL Server DBA, there are other tools a SQL Server DBA s... Read More
Maximum Functionality with SQL 2005 Large-Value Data Type and Large Row Support
By Kathi Kellenberger
Introduction
SQL 2005 delivers many new features for DBAs and developers alike. Database Administrators have many new options for security, availability, recoverability and much more. Probably the biggest news on the developer side is the CLR in... Read More
Introduction to Performance Tuning on SQL Server
By Wayne Fillis
Introduction
Performance Tuning in SQL Server is an art, and so involved that some DBAs specialize in this field. Every system developed has scope for improvement, and it is usually the DBA’s job to fix the inevitable performance problems that develop over time. I can’t say that I ... Read More
How to Make the Impossible Possible
By Peter Ward
It is almost 5 o’clock on a Friday afternoon and your mind has already started to think how SQL Server will be the utmost thing from your mind for the next two days. Suddenly your bosses’ head appears over the top of your cubicle and interrupts this daydream with “Can you tell me all the databases... Read More
Common Table Expressions and Recursion
By K. Brian Kelley
There are quite a few T-SQL enhancements in SQL Server 2005, but the one which caught my eye immediately was the Common Table Expression (CTE). CTEs are similar to derived tables in that you get a result set you can use within a larger query. However, CTEs are very different from derived t... Read More
SQL Server 2005 CLR Integration Security
By Scott Klein
When Microsoft SQL Server 2005 debuted just over a year ago, a lot of the hype surrounding this new release was the integration of the common language runtime (CLR) components of the .NET Framework. The integration of the CLR, which is hosted in the SQL Server Database Engine, provides the s... Read More
Fuzzy Grouping
By Brian Nordberg
Finding similar, but not exact, duplicates using SQL Server Integration Services
SQL Server 2005 Integration Services (SSIS) introduces two new tools designed for Data Warehousing, but their uses are far more than just warehousing. Fuzzy Lookups and Fuzzy Grouping both can improve quality of data. Fuzzy lookups i... Read More
Built-In Security Principals and Unusual Logins
By K. Brian Kelley
Inevitably, when managing SQL Server security the question comes up, “Who can do what?” Usually this is prompted by internal audit or a SOX compliance officer or an outside audit firm confirming controls. In most cases we can show said requester the list of Windows groups and user... Read More
Better Living Through Endpoints
By Michael Coles
SQL Server 2005 makes creating efficient and secure T-SQLbased Web Services easier than ever. SQL Server’s newest T-SQL extensions allow you to expose plain old stored procedures and user-defined functions as HTTP/SOAP Web Service methods directly from your SQL Server. In this article I will discus... Read More
A Small Discussion on Covering Indexes
By Randy Dyess
While discussing indexes during my classes or during presentations, I am often asked what a covering index is and why should you use one. This article will attempt to answer those questions by explaining the benefits of covering indexes in both SQL Server 2000 and SQL Server 2005. Note: The in... Read More