I am continuing my series of talks on fundamental topics like data types. I am proud to be a United States Army Reservist (USAR) Veteran. Just like boot camp I went to so long ago, I am going nick name the series BASIC TRAINING. The most basic unit of any database is a TABLE which consists of COLUMNS. A vital decision during the initial database design is to choose the data types that will capture the information you want in the least amount of space. Today, I am exploring character…
Category: Database Developer
Articles related to developing TSQL code for SQL Server 2008 R2.
Basic Training – Data Types – Part 2
I am continuing my series of talks on fundamental topics like data types. I am a proud United States Army Reservist (USAR) Veteran. Just like boot camp I went to so long ago, I am going nick name the series BASIC TRAINING. The most basic unit of any database is a TABLE which consists of COLUMNS. The most important decision during the initial database design is to choose the data types that will capture the information you want in the least amount of space. Today, we are reviewing Approximate Numerical…
Basic Training – Data Types – Part 1
Just the other day, I was tasked with redesigning a data warehouse’s star schema that grew to over 4 terabytes in size. After completing the project, I realized that if the original designers knew more about storage (data types, data pages, index pages), the explosive growth would have not been so bad. I ended up putting the database on a diet of daily table partitions and page compression. Today, the database is 20% of it’s orginal size. In short, I am going to start off a series of talks covering…
Various Triggers – Part 6
Triggers can be used to track Data Manipulation Language (DML) changes that occur in tables. Today, I am going to expand the AUTOS database script to add table auditing. Auditing is a very good practice when multiple people have rights to make changes to important semi-static data. This is not a good solution if the amount of data is large or number of table changes are huge. First, I am going to leverage the newly built schema called Audit Database Tracking (ADT) to seperate data tables from audit tables. A…
Various Triggers – Part 5
Another usage of Database Triggers is track Data Definition Language (DDL) changes that occur in a database. Today, I am going to enhance the AUTOS database script to add such auditing. This auditing is a very good practice when multiple people have syadmin rights to make such changes. First, I am going to create a schema called Audit Database Tracking (ADT) to seperate the data tables from audit tracking tables.
|
1 2 3 4 5 6 7 8 9 |
<span style="color: #008000; font-size:small;">-- Delete existing schema. IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'ADT') DROP SCHEMA [ADT]; GO -- Create a ADT schema CREATE SCHEMA [ADT] AUTHORIZATION dbo; GO </span> |
Second, I am going to create a table to hold the keep track of the DDL changes that are…
Various Triggers – Part 4
Another usage of Data Modification Language (DML) triggers is the replicating data for historical tracking or data aggregation. Today, I am going to expand on the AUTOS database that I created in prior articles to demonstrate such usage. The business unit has asked us to track each macro task in the making of an automobile. To accomplish this goal, I am going to add the following objects to the AUTOS database. STATUS_CODE – a coded version of the status of a given task. MAKE_STATUS – for a given vehicle…
Various Triggers – Part 3
A typical use of Data Modification Language (DML) triggers is to prevent unwanted data modification. AFTER triggers are great at detecting the INSERT, UPDATE or DELETE action and rolling back the changes. A good security model can prevent these types of changes and should be the first line of defense. However, there are cases in which keys to the castle have to be given out for political reasons inside the organization. In that case, this type of preventive programming can be handy. Today, I am going to revisit the AUTOS…
Various Triggers – Part 2
I am going to wrap up my discussion of LOGON TRIGGERS. We now have a MS ACCESS database that is using a LINKED TABLE pointing to the [WILDLIFE] SQL Server database. Most professional applications set the application name string before connecting via ODBC API to a data source. MS ACCESS does just that at a higher level, the product suite name. The trigger named ‘DISALLOW_MS_OFFICE’ uses the APP_NAME() system function to determine if the application is MS OFFICE product. We are going to revoke all MS OFFICE products from connecting…
Various Triggers – Part 1
I am going to wrap up my discussion of LOGON TRIGGERS. The number and types of database triggers available to database administrators has increased over the years as the Microsoft SQL Server database engine has matured. There are now triggers for execution after both DDL and DML modifications. These triggers can be classified as AFTER – TSQL code to be executed after an action or INSTEAD OF – TSQL code to be executed in lue of a action. Triggers are commonly used in following tasks: Auditing data changes by place…
Import & Export Data – Part 4
The BCP command line utility is the Cadillac of ETL programs for text based data files. It is REALLY FAST. It can perform data imports, data exports and generate format files. What it lacks in the transformation step can be crafted with custom TSQL. Today, I am going to concentrate on how BCP can be used to export data to text files. The format of the output text files can be either character, native, or unicode. A quick demonstration of how to create character and xml format files will also…