Tonight, I will be talking about default database paths that a DBA can change either manually or programatically. The automated process is to use undocumented extended system stored procedures to read from and write to the registry. The manual process is to use the Server Properties dialog box in SQL Server Management Studio (SSMS). I am going to start with the database settings page that contains the default data file and log file directories. This setting determines where the data files (*.mdf, *.ndf) and log files (*.ldf) are stored. Just…
Server Settings – Part 2
Some of the settings that are listed in Server Properties dialog box in SQL Server Management Studio (SSMS) are stored in the local registry. These registry settings can be read from and written to by TSQL code. Thus, scripts can be created to standardize settings for all servers that you manage. The purpose of today’s talk is talk about security page of the Server Properties dialog box and how a DBA can change the settings. All of the settings can be changed by selecting the correct radial button, check box…
Server Settings – Part 1
The simplicity of installing SQL Server out of the box is very appealing to the accidental database administrator (DBA). Run the install program, select default options, and you have a running database. The ease of installation is a double edge sword. You now have a database management server (DBMS) that might be using settings that are not optimal for your company. The purpose of today’s talk is to introduce settings that a DBA can change to make the DBMS perform better. I will be showing where information can be found…
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…
Import & Export Data Part 2
The BULK INSERT statement imports formatted data directly into a table or view of your choosing. The main advantage of this statement is that is minimally logged if the correct recovery model is choosen. Peforming a transaction log backup after each bulk insert reclaims the log space that was used. This statment has many parameters that can alter how the statement executes. Today, I am going to demonstrate the parameters that I think are most useful. We will be working again with the Boy Scouts of America (BSA) hypothetical database.…
Import & Export Data – Part 1
There are not many real life databases that do not import data from external sources or do not export data for delivery to external targets. I am going to devote a series of talks on different ways to perform these functions. The INSERT statement is the easiest way to load small sets of static information into a database. We will be working again with the Boy Scouts of America (BSA) hypothetical database. The first example loads the rank table with a data. We can see the auto-increment surrogote key is…
Temporary Tables – Part 5
Today, I am going to talking about common table expressions. A common table expression (CTE) is a temporary result set that is defined within the execution scope of a single DML statement. A CTE is simular to a derived table in which the results are not stored as an object; However, CTE’s can be recursive or built upon other CTE’s. The WITH key word is used to indicate the start of a CTE and needs to be preceeded with a semicolon. The rest of the syntax is like a stored…
Temporary Tables – Part 4
Today, I am going to talk about regular tables in tempdb. Please check out the Microsoft white paper on working with tempdb for a detailed discussion. The advantage of creating a table in tempdb is that it is persistent until the server is rebooted. Upon startup of SQL Server, tempdb is created again from scratch. One use of this technique is to have startup procedures create tables in tempdb that are used by an application program. One can leverage the fact that operations within tempdb are minimally logged. The major…
Temporary Tables – Part 3
Today, I am going to talk about table variables. In general, TSQL variables are defined by using the DECLARE key word and preceeding the variable name with a @ sign. Table variables are created by using the TABLE key word just after the variable name. The declaration following this key word includes column definitions — names, data types, and constraints. The only constraint types allowed in the declaration are PRIMARY KEY, UNIQUE, NULL, and CHECK. Indexes are a bi-product of a PRIMARY and UNIQUE constraints. The scope of a table…
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…