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…
Tag: CREATE TABLE
Searching Stored SQL – Part 4
There are four main database objects that contain stored SQL: VIEWS, TRIGGERS, FUNCTIONS and STORED PROCEDURES. Earlier today, I created a pattern searching tool for FUNCTIONS. I am going to clone and modify the code so that it will work with VIEWS. Again, I want to examine the dynamic SELECT statement that retrieves the user defined stored procedure names from a given database. This is the key element of the whole program. I took the liberty to modify the original code so that it will work standalone. The current code…
Searching Stored SQL – Part 3
There are four main database objects that contain stored SQL: VIEWS, TRIGGERS, FUNCTIONS and STORED PROCEDURES. Yesterday, I created a pattern searching tool for TRIGGERS. Today, I am going to clone and modify the code so that it will work with FUNCTIONS. Again, I want to examine the dynamic SELECT statement that retrieves the user defined stored procedure names from a given database. This is the key element of the whole program. I took the liberty to modify the original code so that it will work standalone. The current code…
Searching Stored SQL – Part 2
There are four main database objects that contain stored (compiled) SQL: VIEWS, TRIGGERS, FUNCTIONS and STORED PROCEDURES. Yesterday, I created a pattern searching tool for SQL code in STORED PROCEDURES. Today, I am going to clone and modify the code so that it will allow pattern searching on TRIGGERS. First, I want to examine the dynamic SELECT statement that retrieves the user defined stored procedure names from a given database. This is the key element of the whole program. I took the liberty to modify the original code so that…
Searching Stored SQL – Part 1
Today, I am going to start building on some the articles I did in the past. We are going to create tools to pattern search stored SQL statements. Four main objects that contain stored SQL are VIEWS, TRIGGERS, FUNCTIONS and STORED PROCEDURES. Recently at work, I was asked to find all SQL code that had a certain business rule. The task is to return all objects that reference to a certain temperature process named T1. One solution to this problem is to buy a tool like SQL Search from Red…
Temporary Tables – Part 2
Today, I am going to talk about local and global temporary tables. A local temporary table is defined by using the CREATE TABLE syntax with the name of the table prefixed by one number sign #. The scope of the local temporary table is the current session or SPID. A global temporary table is defined by using the CREATE TABLE syntax with the name of the table prefixed by two number signs ##. The table is visible to all sessions. The scope of the global temporary table is the current…
Crafting Databases – Part 3
The CREATE TABLE statement is part of the Data Definition Language (DDL). It is used to define a structure to hold rows of data or tuples if we use Relational Algebra concepts. Each table is defined by a set of fields of a certain data type and precision. Data Types can be categorized as the following. Exact numerics Approximate numerics Character strings Unicode character strings Binary strings Date and time Other data types The power of a database comes from creating user defined datatypes (UDT). Some of the benefits of…