The transaction log file records all transactions and the database modifications made by each transaction. Heavy application processing against the database can make the transaction log grow quickly. Therefore, the transaction log must be truncated on a ADHOC basis to keep it from filling up. Each physical log file is divided into smaller logical units called virtual log files (VLFs). Truncation provides free space by deleting inactive virtual log files. To avoid running out of log file space or physical disk space if growth is set to unlimited, one should…
Checksum vs Hashbytes
I recently worked on a project I which I redesigned sales data warehouse as a STAR schema, using daily file partitions, with a automatic sliding window, and applying data compression at the page level. I ended up reducing a 5 terabyte database to less than 750 GB. I will be writing several articles on the lessons that I learned during the process. Today, I want to talk about how to generate a hash key by using two built in SQL Server functions. A hash function is any algorithm that maps…
Surrogate Keys
I recently worked on a project I which I redesigned sales data warehouse as a STAR schema, using daily file partitions, with a automatic sliding window, and applying data compression at the page level. I ended up reducing a 5 terabyte database to less than 750 GB. I will be writing several articles on the lessons that I learned during the process. Today, I want to talk about how surrogate keys can be used to tie the dimension tables to the fact tables. I need to start the talk off…
Seattle – Pass Summit 2012
I just returned to New England after 4 days in Seattle at Pass Summit 2012. I analogize the conference to going out to eat every hour, some presentations I truly enjoyed; Others did not suit my pallet. Overall, I would rate the event as a total success. Here are some presentations I viewed in chronological order that were definite highlights during my trip.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
Matthew Roche & Peter Myers - 10 Extraordinary Things to Achieve with Integration Services 2012. Devin Knight - Adapting Your ETL Solutions to Use SSIS 2012. Kalen Delaney - Storing ColumnStore Indexes. Brent Ozar - Real-Life SQL 2012 Availability Group Lessons Learned. Kendra Little - Index Psychiatry: Diagnose and Treat the Top 5 Disorders. Paul White - Query Optimizer Deep Dive. Tim Ford - You Can Do That with T-SQL? |
In summary, if you can get the chance to go to the Summit 2013 Conference in October at Charlotte NC, please do. You will not…
Database Files & Objects – Part 4
Today, I will be continuing my talk on database objects that can be viewed through the system catalog views or system stored procedures. A Microsoft SQL Server database is composed of many different objects. We will be investigating how to retrieve information on these objects in the [MATH] sample database. Please see my first article that creates this database. I will be focusing on functions, stored procedures, triggers and views in this article. Let’s use the USE statement to select the sample database.
|
1 2 3 4 |
<span style="color: #008000; font-size: small;">-- Which database to use. USE [MATH] go </span> |
VIEWS – MSDN QUOTE: “A view…
Nashua – SQL Saturday #146
I am totally pumped up about having the opportunity to present at SQL Saturday #146 in Nashua. I will be posting an updated version of the presentation with TSQL code before the end of the weekend. I hope you have time to attend cool event on October 20th, 2012! Topic: Effective use of temporary tables Abstract: Every developer eventually comes against business logic that can’t be handled with a single simple or complex query. TSQL provides the developer with several constructs that can store temporary result sets that are passed…
Database Files & Objects – Part 3
Today, I will be continuing my talk on database objects that can be viewed through the system catalog views or system stored procedures. A Microsoft SQL Server database is composed of many different objects. We will be reviewing how to retrieve information on these objects in the [MATH] sample database. Please see my first article that creates this database. I will be focusing on tables, columns, indexes, and constraints in this article. Let’s use the USE statement to select the sample database.
|
1 2 3 4 |
<span style="color: #008000; font-size: small;">-- Which database to use. USE [MATH] go </span> |
The most basic component of a database…
Database Files & Objects – Part 2
Today, I will be continuing my talk on database files and database objects that can be viewed through the system catalog views or system stored procedures. A Microsoft SQL Server database is composed of at least two operating system files: a primary data file and a transaction log file. A secondary data files can be used to implement table partitioning or storage of data on different disks by filegroup. If we want to get a simple list of all the databases on the server, one can execute the sp_helpdb system…
Database Files & Objects – Part 1
I am currently reading Kalen Delany and others book on SQL Server 2008 System internals. If you have not read this book from cover to cover, you should. It has a wealth of knowledge about database engine details. To demonstrate key ideas from the book, I am going to expand on the [MATH] database that contains PRIME numbers. The trial division algorithm inside this database uses a brute force method for calculating prime numbers. This article will focus on files and objects that can be viewed through the system views,…
Basic Training – Data Types – Part 6
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 binary…