RSS Feed
Archive for the ‘T-SQL Programming’ Category
Look-up Tables in SQL
February 16th, 2011 
Mushrraf
History Tables, in a properly designed schema, model either an entity or a relationship, but not both. Slightly outside of the tables in the data model, we have other kinds of tables. Staging tables bring in "dirty data" so we can scrub it and then insert it into base tables. Auxiliary tables hold static data [...]
Posted in 
Database Refactoring
February 16th, 2011 Mushrraf
Much of the material that I’ll discuss in this article comes from the excellent work done by Scott W Ambler and Pramodkumar J. Sadalage who wrote the book ‘Refactoring Databases: Evolutionary Database Design’ and maintain the site http://www.agiledata.org/. Just as Martin Fowler’s original book ‘Refactoring: Improving the Design of Existing Code’ introduced the world to [...]
Data Conversion in SQL Server
February 16th, 2011 Mushrraf
When working with data in a SQL Server database, you might find that some of that data needs to be converted from one data type to another. For example, suppose you need to compare two numbers, one configured with a numeric data type and one configured with a string data type. To perform the comparison, [...]
Collecting Performance Data into a SQL Server Table
February 14th, 2011 wiseinn
Occasionally, when tracking down a performance problem, you have to have information over time on the values of particular sets of performance counters. To track down the more insidious or sporadic problem, it is best to have the data in a SQL Server table, so you can query it. Feodor Georgiev explains the command-line way [...]
Get Started with Chart Development in Reporting Services 2008
February 14th, 2011 wiseinn
With Reporting Services 2008, it is now much easier to produce charts from data in SQL Server, and the variety of charts seems almost limitless. All you need to get started is a quick step-by-step guide that tells you the basics and gets you past the stage of creating the first chart. Well, here it [...]
RegEx-Based Finding and Replacing of Text in SSMS
February 14th, 2011 wiseinn
So often, one sees developers doing repetitive coding in SQL Server Management Studio or Visual Studio that could be made much quicker and easier by using the Regular-Expression-based Find/Replace functionality. It is understandable, since the syntax is odd and some features are missing, but it is still worth knowing about. The Find/Replace feature of SQL [...]
Showplan Operator of the Week – Merge Interval
February 14th, 2011 wiseinn
When Fabiano agreed to undertake the epic task of describing each showplan operator, none of us quite predicted the interesting ways that the series helps to understand how the query optimizer works. With the Merge Interval, Fabiano comes up with some insights about the way that the Query optimizer handles overlapping ranges efficiently. Merge Interval [...]
BIT of a Problem
February 14th, 2011 wiseinn
Once upon a time, long, long time ago, programming languages were tied to the hardware. Obviously, assembly and machine languages were designed for one particular family of computers. But even the higher level languages still clung to the hardware. FORTRAN and COBOL standards were defined on the assumption that files were sequential, main storage was [...]
SQL SERVER 2008 Data Dictionary
February 13th, 2011 wiseinn
This is a comprehensive script for generating data dictionary for your database, shows the tables, columns, descriptions for each table and column as well as the data types of the columns and if columns are PK/FK, Null Allowable, Identity generated as well as the name of foriegn table if column is FK. SELECT [...]
Targeted Index Performance Improvements
February 13th, 2011 wiseinn
Introduction When T-SQL code is run, information about which indexes it uses and how it uses them (i.e. updates, seeks, scans, lookups) is stored. Additionally information about the number of rows affected by the running code is also recorded. Using this information, we can target our performance improvements to those specific indexes, leading to better [...]
