Date and Time dimension creation and population T-SQL. Dim_Date and Dim_Time, Date and Time dimensions.
Creation, population, indexing and use script. Just copy the code into a query window and run the script. There are no additional functions or procedures required. This code will create the tables, populate the date and time tables, add holidays, add indexes and there are a few code snippets at the bottom for using them. There are two variables in the code right after the table creation "@StartDate" and "@EndDate".
The "ID" field on the dim_Date table is the primary key and the date as format YYYYMMDD. GO # and WHILE Loops. Simple Query tuning with STATISTICS IO and Execution plans. A great deal can be gleaned from the use of the STATISTICS IO and the execution plan, when you are checking that a query is performing properly.
Josef Richberg, the current holder of the 'Exceptional DBA' award, explains how an apparently draconian IT policy turns out to be a useful ways of ensuring that Stored Procedures are carefully checked for performance before they are released I once worked at a large financial institution where we had a policy in place that went … “All procedures that are to be promoted must have their execution plan and STATISTICS IO data attached with the promote form, for review by DBA group”. SQL Server SSIS package: SSIS : Alternatives of Slowly Changing Dimension - SCD. SSIS – SCD (Slowly Changing Dimension ) - OleDb Command Using: SQL Server 2008, BIDS 2008 (Business Intelligence Development studio) I was working on a scenario, which was as below: There was a dimension table say ‘dimDest’ with around 600 columns, which gets populated every hour from the source through a scheduled job.The job gets all updated data from the source, and stores the data in the stage table say ‘tmpdimDest’ Job loads ‘dimDest’ table from ‘tmpdimDest’ table through SCD (Slowly changing dimension) type 1.
Quick Data Synchronization Using Table Partitioning and SSIS. I recently helped a client migrate an application’s back-end databases from Sybase to SQL Server.
One of the databases used by this application, which I’ll call MyApp, was a shared database. The other applications that used this database weren’t being migrated from Sybase to SQL Server, so the client needed a data synchronization mechanism that would transfer over changes made in the Sybase tables to the SQL Server tables. The SQL Server users of MyApp only read data from 19 tables of 100+ tables in the shared database. The data in those tables was modified by several other applications that were still utilizing Sybase. Because the users of the Sybase applications were spread across multiple time zones around the globe, data changes occurred practically every minute, 24/7.
How To: Optimize SQL Indexes. Improving .NET Application Performance and Scalability J.D.
Meier, Srinath Vasireddy, Ashish Babbar, and Alex Mackman Microsoft Corporation May 2004 Related Links Home Page for Improving .NET Application Performance and Scalability Chapter 12, Improving ADO.NET Performance Chapter 14, Improving SQL Server Performance Checklist: ADO.NET Performance Checklist: SQL Server Performance Send feedback to Scale@microsoft.com patterns & practices Library. SQL Server Index Tutorial. I had to do some developer training last week and I wrote up a paper on the basics of primary keys and indexes.
Sure, there’s tons of similar stuff around the net, but this is MINE, baby. Our Table: Phone Numbers For our evil training purposes, let’s say we work for the phone company, and we need a database table with phone numbers. We need to track: Phone number (required)Billing contact last name (required)Billing contact first name (required)Business name (optional)Business category (restaurant, dog groomer, auto dealer, etc)Address 1Address 2CityStateZipService start date (Sometimes a person or a business will have multiple phone numbers, but for the sake of this training, let’s keep it a simple flat table.)
We will never have two records in here with the same phone number. Introduction to Change Data Capture (CDC) in SQL Server 2008. Change Data Capture records INSERTs, UPDATEs, and DELETEs applied to SQL Server tables, and makes a record available of what changed, where, and when, in simple relational 'change tables' rather than in an esoteric chopped salad of XML.
These change tables contain columns that reflect the column structure of the source table you have chosen to track, along with the metadata needed to understand the changes that have been made. Pinal Dave explains all, with plenty of examples in a simple introduction. Introduction Often, you’ll be told that the specification of an application requires that the value of data in the database of an application must be recorded before it is changed. In other words, we are required to save all the history of the changes to the data. SQL Server 2005 introduced the new features of ‘after update’, ‘after insert’ and ‘after delete’ triggers that almost solved the problem of tracking changes in data. Automating RANGE RIGHT Sliding Window Maintenance.
How To Decide if You Should Use Table Partitioning. Great volumes have been written about table partitioning.
It’s a complex feature and you can read for days to just understand how you might apply it. But will it improve performance for you? Table partitioning produces great benefits for some applications, but causes giant headaches for others. SQL Server: Natural Key Verses Surrogate Key. When designing a database to support applications you need to consider how you are going to handle primary keys.
This article explores natural and surrogate keys, and discusses the pros and cons of each, allowing you to determine what makes the best sense in your environment when you are designing your databases. When designing a database to support applications you need to consider how you are going to handle primary keys. There are two schools of thought, or maybe three. SQL Server: Natural Key Verses Surrogate Key. How do I... Reseed a SQL Server identity column?