SQL DBA TutorialsWelcome to the SQL DBA Tutorials. The intent of these tutorials is to provide in depth understanding of SQL DBA .
In addition to free SQL DBA Tutorials, you can find interview questions, how to tutorials and issues and their resolutions.
Database Administrators (DBAs) are responsible for the design, implementation, support and maintenance of computerized databases in today's organizations. The role also includes architecting, building and scaling databases for future data growth and capacity. They are also responsible for security, performance and availability of data to users and customers. .
The SQL Server Database system from Microsoft Corporation has gained popularity over the last years, and it is believed to be one of the few surviving relational database systems in this marketplace (the others being Oracle and DB2 from IBM).
An important part of many DBAs’ and SQL Server administrators’ jobs is keeping databases current. Doing so involves adding, changing, and deleting data from tables. T-SQL makes these tasks easy. You just need to use INSERT, UPDATE, and DELETE statements. Before I show you how to use these statements, there are a few things you need to do before proceeding.
Assuming that you have the permissions needed to create databases and tables and to issue datamodification statements, you can create MyDB, MyTable, and the dummy tables with the MyDB .sql and MyTable.sql files. You can download these files by going to Download the Code hotlink icon above. After you’ve downloaded and unzipped the 98105.zip file, open a query window in either SQL Server 2005’s SQL Server Management Studio (SSMS) or SQL Server 2000’s Query Analyzer and copy the code in MyDB.sql into the window. In the two FILENAME entries, replace C:\Program Files Microsoft SQL Server\MSSQL.1\MSSQL\DATA MyDB.mdf with a valid path on your server. Run the query to create MyDB. To create MyTable and the dummy tables, copy the code in MyTable.sql into the query window and execute it.
Before working with data in any table, you should familiarize yourself with the table’s layout. So, open MyTable and do so.
Inserting a Single Record
A basic INSERT statement that adds a single record (aka row) to a table consists of three parts and looks like
INSERT INTO Part1
Part 1 is where you specify the target table that will hold the new record. Part2 is where you list the names of the columns in the target table for which you have data. You need to enclose the list in parentheses and use commas to separate column names. Part 3 is where you provide the data to be inserted. When you want to specify the actual data values, you use the VALUES parameter followed by those values. You need to enclose the list of data values in parentheses and separate the values with commas. When a value is character based—such as a string or a datetime value—you also need to enclose it in single quotes (' '). For example, running the following statement inserts the data values of Testable and 2007-09-22 into MyTable ObjectName and Creation- Date columns, respectively:
INSERT INTO MyTable
VALUES ('TestTable', '2007-09-22')
If you familiarized yourself with MyTable earlier, you probably noticed that specified two of the three columns in this INSERT statement. The first column is an identity column, which has a special meaning. The data for that column is automatically added and its values incremented with each record being inserted. This is a powerful feature of SQL Server that you should be aware of.
To view the newly inserted record, execute the code
SELECT * FROM MyTable
Inserting the Results from a SELECT Statement
In “T-SQL 101, Lesson 1” (March 2008, InstantDoc ID 97724), I showed you how to get data out of a database with a SELECT statement. You can use the INSERT statement to store the data retrieved by the SELECT statement in a table. Like the single-record INSERT statement, the INSERT statement that stores the results of a SELECT statement has three parts: the target database (Part1), the column names (Part2), and the data (Part3). However, instead of using the VALUES parameter to specify the actual data values in Part3, you use a SELECT statement that retrieves the data from another source. For example, when run against the MyDB database, the query
INSERT INTO MyTable
SELECT name, crdate
WHERE TYPE = 'U'
ORDER BY name
inserts into our new table MyTable records that contain the name and creation date of all the user-defined tables in MyDB, ordered alphabetically by their names. So, assuming you ran the single-record INSERT statement previously discussed, your results from this query should look like those in Figure 2, with two exceptions. First, the Creation Date values for the MyTable and dummy tables will contain the date and time when you ran MyTable.sql. Second, the ObjectName column will be wider. (I shortened it for space reasons.)
Note that Test Table is still the first record. The ORDER BY name clause only applies to the new records that the SELECT statement is inserting. Test Table was added previously with the single-record INSERT statement.
You can use any SELECT statement you like, as long as the data types of the columns listed in Part2 of the INSERT statement match those columns specified in the SELECT statement. This opens the door for capturing all sorts of data. Use your newfound knowledge with caution, however. You don’t want to be inserting millions of records at a time on a busy server or on a server with limited disk space.
Now that you have a handle on inserting data, let’s look at how the UPDATE statement works. A simple UPDATE statement typically consists of three parts:
Part1 is where you specify the target table. Part2 is where you specify the columns that are to be changed, along with the new data for each column. Part3 is optional but in most cases essential. This is where you specify a filter using the WHERE clause. If you don’t specify a WHERE clause, you’ll update every single record in the table. For example, the query
SET CreationDate = '2007-09-23'
updates the CreationDate value for each record in MyTable, as Figure 3 shows. In Figure 3, note that the CreationDate value is 2007-09-23 00:00:00.000 and not 2007-09-23 as specified in the query. Because the data type of the CreationDate column is defined as datetime and the query doesn’t specify a time portion, SQL Server assumes you mean midnight and adds 00:00:00.000.
Now let’s use a WHERE clause to modify the ObjectName and CreationDate columns for a specific record:
SET ObjectName = 'PartyTime',
CreationDate = '1999-12-31
WHERE TableID = 1
only the first record (specified by WHERE TableID = 1) is updated with the new table name of PartyTime and a new creation date of 1999-12-31 23:00:00.
The command that every DBA fears will fall into the wrong hands is the DELETE statement. Although it can be misused, it serves an important role when using queries to modify data in tables. A basic DELETE statement typically consists of two parts:
Part1 is where you specify the target table. Part2 is where you specify a filter using the WHERE clause. Like the WHERE clause in an UPDATE statement, the WHERE clause in a DELETE statement is optional but usually essential. If you don’t include a WHERE clause and filter, you’ll delete all the records in the specified table.
Let’s say you want to delete the records with a creation date older than September 22, 2007 from MyTables. The query
WHERE CreationDate > '2007-09-22'
will do just that,. You need to be careful when modifying data with either the DELETE or UPDATE statement. Always include a WHERE clause unless you’re positive you want to affect all the records in a table. If you’re unsure of how many records you’ll be affecting, try running a SELECT statement with the same WHERE clause.
New Features in SQL Server 2016
SQL Server 2016 is the latest iteration of Microsoft’s flagship database and analytics platform with lots of new features and improved existing features making it an intelligent database and analytics platform for a mobile first, cloud first world. In fact, my first impression of SQL Server 2016 Community Technology Preview 2 is that it is another exciting launch SQL Server product that offers plenty of new features and improved existing features that enable large organizations to deliver outstanding performance for mission-critical applications and deeper insights on the organization’s data across on-premises and cloud.
Hybrid Cloud Feature
In-database R language support
Earlier this year, Microsoft acquired Revolution Analytics, a company specializing in tools and its own open-source distribution for the R programming language, used for statistical analysis. SQL Server 2016 has in-database R programming language support. By incorporating R programming language support into SQL Server 2016 product set makes advanced analytics using R more accessible while making deeper insight into data easier. For example, instead of extracting the data from SQL Server database via ODBC and then analyse it R programming language, you can now able to take your R code to the data, where it will be run inside a sandbox process within SQL Server itself. This eliminates the time and storage required to move the data, and gives you all the power of R and CRAN packages to apply to your database.
PolyBase into SQL Server
In today’s world of interconnected devices, such as smart phones, televisions, watches, laptops, tablets, desktops, gaming devices like Xbox and Play Station, and many more, and wide access to data obtained from various sources, such as Twitter, Facebook, LinkedIn, flat files, blogs, websites, system logs, sensors and so on, data growth is among one of the three main challenges companies are facing today. Therefore, in recent years, organizations are using Apache Hadoop for big data processing from various sources, such as blogs, sensors, social media, system logs and other devices. In previous SQL Server versions, SQL Server PDW was the only release of SQL Server that came with PolyBase that let you perform standard SQL queries over relational data and Hadoop data store (eliminating the need to understand HDFS or MapReduce). However, Microsoft now integrated PolyBase into SQL Server 2016, which means you can now perform SQL queries against non-relational Hadoop data and join it on-the-fly with their existing relational data in SQL Server, eliminating the need of having to pre-load data first into the data warehouse. For example, if your ETL process includes processing of large non-relation data stored in Azure Blob Storage or Hadoop, then you can use simply query this data with standard T-SQL as if it resides in database table. You can run analytics and BI on this data. SQL Server 2016 and PolyBase therefore can help you to build out a hybrid solution that delivers deeper insights on your data, wherever it may be located. This seamless integration between traditional relational structured data and non-relational unstructured data makes SQL Server 2016 a leading enterprise ready database and analytics platform. You can run analytics and BI on this data.