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…
Tag: database developer
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…
Basic Training – Data Types – Part 5
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 date…
Basic Training – Data Types – Part 4
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 unicode…
Basic Training – Data Types – Part 3
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…
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…
Command Shell
It is sometimes necessary to execute external commands in windows command shell from TSQL. The xp_cmdshell extended stored procedure allows you to do just that. Because this command shell can be a potential target for hackers, it is turned off by default. Today, I am going to use this command to solve a real world ETL problem. The following SQL snippet uses the sp_configure command to enable this option.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
<span style="color: #008000;">-- To allow advanced options to be changed. EXEC sp_configure 'show advanced options', 1 GO -- To update the currently configured value for advanced options. RECONFIGURE GO -- To enable the feature. EXEC sp_configure 'xp_cmdshell', 1 GO -- To update the currently configured value for this feature. RECONFIGURE GO </span> |
The xp_cmdshell command takes a valid DOS string as input and executes asynchronously in a windows command shell until the…
Selecting Data – Part 4
Today, I am continuing our exploration of the SELECT reserved word by talking about sub-queries. A sub-query is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another sub-query. Sub-queries can be used anywhere an expression is allowed or can be nested inside another sub-query. I will be using the AdventureWorks Sample database supplied by Microsoft during this talk.
|
1 2 3 4 |
<span style="color: #008000; font-family: Lucida Console; font-size: small;">-- Use the correct database USE AdventureWorks2008R2 GO </span> |
One usage a sub-query is with comparison operators in the where clause. The example below returns all the sales people by name and id…