As discussed in our previous article, concurrency that is based solely on locking can cause blocking and deadlocking problems. However, reading data with READ UNCOMMITTED is not a solution for applications that require row-level accuracy when retrieving data; dirty data is generally not a good thing. To solve this problem, SQL Server includes a row versioning mechanism called snapshot isolation, which is intended to improve performance by avoiding the reader-writer blocking scenarios in the previous example.
Read operations do not request shared locks on the data, so transactions that modify data do not block transactions that read data, and transactions that read data do not block transactions that write data, as they normally would under the default READ COMMITTED isolation level.
The term “snapshot” reflects the fact that all queries in the transaction see the same version, or snapshot, of the database, based on the state of the database at the moment the transaction begins. No locks are acquired on the underlying data rows or data pages in a snapshot transaction, which permits transactions to execute without being blocked by a prior incomplete transaction.
NOTE: Two write operations do block each other even while running under row versioning-based isolation levels, because two write operations cannot modify the same data at the same time.
A unique transaction sequence number identifies each transaction. Whenever a transaction modifies a row, an image of the row before modification is copied into a page in tempdb. If multiple transactions modify a row, multiple versions of the row are linked in a version chain.
When a user or application retrieves data, it automatically gets the last saved version of each row.
Snapshot Isolation Caveats
Snapshot isolation is not recommended for every application. Here are several reasons why READ COMMITTED might be a better choice for your application:
- READ COMMITTED consumes less tempdb space than snapshot isolation.
- Snapshot isolation does not work with distributed transactions that span servers.
- Snapshot isolation is vulnerable to update conflicts that do not apply to READ COMMITTED. When a transaction that’s running under snapshot isolation reads data that is then modified by another transaction, an update by the snapshot transaction to the same data causes an update conflict and the transaction terminates and rolls back. This is not an issue with READ COMMITTED.
Enabling Snapshot Isolation
You enable snapshot isolation per database by turning on the ALLOW_SNAPSHOT_ISOLATION database option.
ALTER DATABASE Northwind
SET ALLOW_SNAPSHOT_ISOLATION ON;
This allows snapshot isolation when it is explicitly invoked, but the default READ COMMITTED transaction isolation level remains in effect for implicit transactions that do not specify snapshot isolation.
To replace READ COMMITTED entirely, turn on the READ_COMMITTED_SNAPSHOT option in addition to the ALLOW_SNAPSHOT_ISOLATION option. Snapshot isolation will then be used for all transactions.
ALTER DATABASE Northwind
SET READ_COMMITTED_SNAPSHOT ON;
This post is an excerpt from the online courseware for our SQL Server 2012: T-SQL Working with Data course written by expert Don Kiely.
Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.
Thousands of developers worldwide use LearnNowOnline to gain the technical skills they need to succeed on the job and advance their career.
When you modify data in SQL Server, it is important to understand how isolation levels and locking can affect performance and concurrency. The term concurrency refers to the ability of multiple users or applications to access the same data. When you update data, concurrency conflicts occur when multiple users select data and then try to update the data simultaneously. SQL Server places various kinds of locks on database objects and table rows in order to ensure data integrity.
One problem is that locks on the data can delay the updates. Another problem is that users may be able to update data that has changed since they last inspected the data. Applications often prevent this by adding a WHERE clause to the update to ensure that the update will succeed only if the data in the database still has the values that the user originally retrieved. The following article discusses Blocking and Deadlocks.
Blocking and Deadlocks
SQL Server is very efficient at applying and releasing locks. If you are modifying data using the default READ COMMITTED isolation level, locks will be applied and quickly released on each row of data. But anytime a resource is locked in SQL Server, it may cause other processes to be unable to access data necessary for the other processes to perform its actions. Two situations you have to be aware of and prepared for are blocking and deadlocks.
Blocking
Blocking occurs when locks are held for too long. When a transaction is blocked because another transaction has locks on data, it just sits there and waits. Over a network, blocked transactions will either hang or eventually time out with an error message.
You can reduce the chance of common blocking problems by avoiding user interaction in the middle of transactions, keeping data modifications short, and avoiding recursive operations and nested triggers.
Deadlocks
A deadlock occurs when two separate processes are each holding a resource that the other needs. Each process is waiting to release the resource it is holding until the other resource becomes available. Unless one of the processes is forced to yield, they will stay deadlocked forever. Deadlocks are difficult to simulate in a development environment; they seem to appear only when your database is running and many users attempt to complete the same operation at the same time.
The Figure below displays how a deadlock works. In this case, Transaction 1 has a lock on Resource 1 and needs a lock on Resource 2 to perform its work. Transaction 2 has a lock on Resource 2 and needs a lock on Resource 1 to perform its work. Transaction 1 can’t get a lock on Resource 2, and Transaction 2 can’t get a lock on Resource 1. So the transactions sit, deadlocked, waiting for the other transaction to let go of its locks.
SQL Server uses an interval to determine which processes are running and which are blocked. If this interval passes twice and a process is still blocked, SQL Server chooses a deadlock victim. The victim’s transaction is rolled back, and error code 1205 is returned to the client application of the losing transaction. Your error handling routine in the client application can test for error 1205 and resubmit or cancel the query. If SQL Server did not select a deadlock victim, then eventually your server would run out of available processes and crash.
To avoid deadlocks, access objects in the same order every time, so that if a process is blocked it goes into a wait queue until the process that is holding the lock is complete.
This post is an excerpt from the online courseware for our SQL Server 2012: T-SQL Working with Data course written by expert Don Kiely.
Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.
Thousands of developers worldwide use LearnNowOnline to gain the technical skills they need to succeed on the job and advance their career.
SQL Server provides an UPDATE .WRITE Transact-SQL statement to perform partial updates on columns that are defined as varchar(max), nvarchar(max), or varbinary(max). Here is the abbreviated syntax:
UPDATE { <object> }
SET
{ column_name = { .WRITE ( expression, @Offset,
@Length ) }
The following example uses the Production.Document table in the AdventureWorks database to demonstrate substitution of the word “critical” for the word “important” in one row of data with a DocumentNode value of 0x5B40.
First, select the row to view the existing data:
SELECT DocumentSummary FROM Production.Document
WHERE DocumentNode = 0x5B40;
The first sentence in the results should look like the Figure below (you’ll need to expand the width of the column to view the entire text of the column).
To change the word “important” to the word “critical” you need to specify the offset (the number of characters from the start of the string to the value you want to replace) and the length of the string to replace.
UPDATE Production.Document
SET DocumentSummary .WRITE (N'critical',6,9)
WHERE DocumentNode = 0x5B40;
If you execute the SELECT statement again to view the current value, you’ll see the results as shown in the Figure below.
Execute the following statement to restore the original value:
UPDATE Production.Document
SET DocumentSummary .WRITE (N'important',6,8)
WHERE DocumentNode = 0x5B40;
You can also use the CHARINDEX and PATINDEX functions to calculate the offset location of the substring you want to change. The LEN function returns the number of characters in a string, so the following statement performs the same replacement of ‘critical’ for ‘important’. (In the sample code file, these statements are wrapped in a transaction that is rolled back, so you don’t have to worry about executing another UPDATE statement to undo the change.)
UPDATE Production.Document
SET DocumentSummary .WRITE (N'critical',
CHARINDEX(N'important', DocumentSummary) - 1,
LEN(N'important'))
WHERE DocumentNode = 0x5B40;
WARNING! In past versions of SQL Server, WRITETEXT was used to replace sections of long text values. SQL Server 2012 still supports this statement, but it has been deprecated and will be removed in a future version of SQL Server. Therefore, you should always use the new .WRITE clause of an UPDATE statement rather than using WRITETEXT.
This post is an excerpt from the online courseware for our SQL Server 2012: T-SQL Working with Data course written by expert Don Kiely.
Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.
Thousands of developers worldwide use LearnNowOnline to gain the technical skills they need to succeed on the job and advance their career.
Being able to use a foreign language well, is not only useful in your personal life, it is also incredibly attractive to employers. Foreign languages are not just those that are spoken every day around the world, however. Foreign languages also include computer languages. This is why a working knowledge of Visual Studio can put you out ahead of your job competition in a big way.
Visual Studio supports a wide range of programming languages, making it extremely flexible with regards to programming networks and setting up inter-office systems. An online Visual Studio tutorial video can help you become familiar with the program quickly, so that you can begin to put your knowledge to use immediately. Additionally, a C# video course will help you familiarize yourself with one of the languages supported by Visual Studio. Your newly acquired skills and knowledge will make you more attractive to potential employers, and increase your worth in your current position. So, take the time to learn a computer-based foreign language. You never know where it might take you.
Thousands of developers worldwide use LearnNowOnline to gain the technical skills they need to succeed on the job and advance their career.
The SQL standard draws a distinction between Data Manipulation Language (DML) and Data Definition Language (DDL). DML statements manipulate and maintain data, while DDL statements change the structure and properties of database objects. SELECT statements are considered to be part of DML, even though you might not think of selecting data as a form of “manipulation.”
When you retrieve data with a SELECT statement, SQL returns a result set. However, any actions you take on that data (besides just looking at it) involve data manipulation queries that use one of the following three T-SQL statements: INSERT, UPDATE, or DELETE. SQL queries that use these statements are sometimes known as action queries.
SQL Server was designed to handle data modification extremely efficiently via T-SQL DML statements. This article takes a look at the SELECT INTO statement for creating new tables using T-SQL.
SELECT INTO
The SELECT INTO statement creates a new table with the columns necessary to contain all the values that the SELECT statement returns. The following query creates a new table called Produce, which contains all of the rows that match the produce category in the Products table:
SELECT dbo.Products.* INTO dbo.Produce
FROM dbo.Products
WHERE CategoryID = 7;
If you run the statement again, you’ll receive an error that the Produce table already exists.
NOTE: When you create a new table with SELECT INTO, the new table schema will match the original schema, including identity columns. The Produce table will have a ProductID that is an identity column. The nullability of columns is also copied from the source, but indexes, constraints, and triggers are not copied.
Temporary Tables
Although the SELECT INTO syntax allows you to create tables in a database, you probably won’t want to use this technique very often in applications. When you allow users to create tables at will, problems can occur with backups, security, and database clutter. But sometimes you’ll want to create temporary, or temp, tables as a way to store data that’s necessary for processing but is not required afterward.
You can create temp tables so that only the connection that creates them can access them. Once that connection ends, SQL Server automatically destroys the tables, which eliminates all of the problems with persistent tables. Temp tables are stored in the tempdb database, and are identifiable by the first character of their name: the pound symbol (#).
NOTE: Temp tables created in stored procedures are destroyed when the stored procedure terminates.
The following SELECT INTO query creates a temporary table named #Produce:
SELECT dbo.Products.* INTO #Produce
FROM dbo.Products
WHERE CategoryID = 7;
Namespace conflicts won’t occur if multiple users create a #Produce table, since every connection can have its own #Produce in tempdb. Each #Produce table in tempdb has a suffix with an underscore and the connection information to keep them separate. To select data from the temp table, use the following query:
>SELECT * FROM #Produce;
Another way to create temp tables is to use the standard CREATE TABLE syntax, as shown in the following DDL statement:
CREATE TABLE #Beverages(
ProductID int NOT NULL,
ProductName nvarchar(40),
SupplierID int NULL,
CategoryID int NULL,
QuantityPerUnit nvarchar(20),
UnitPrice money NULL,
UnitsInStock smallint NULL,
UnitsOnOrder smallint NULL,
ReorderLevel smallint NULL,
Discontinued bit NOT NULL
);
The following query inserts data into the temp table. Note that the data types you explicitly define in the temp table must match the data types in the table you are selecting data from:
INSERT INTO #Beverages
SELECT
ProductID, ProductName, SupplierID,
CategoryID, QuantityPerUnit, UnitPrice,
UnitsInStock, UnitsOnOrder, ReorderLevel,
Discontinued
FROM dbo.Products
WHERE Products.CategoryID = 1;
This post is an excerpt from the online courseware for our SQL Server 2012: T-SQL Working with Data course written by expert Don Kiely.
Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.
Thousands of developers worldwide use LearnNowOnline to gain the technical skills they need to succeed on the job and advance their career.
Let's say you're looking to beef up your resume. So you take a SharePoint tutorial, wisely realizing that more and more businesses are adopting this collaborative platform. In doing so, you also learn that pieces of technology - like SharePoint - are more than just ones and zeroes. You'll learn that technology can help businesses grow while lowering costs. And being able to articulate this can go a long way in your interview.
For example, let's take a closer look at SharePoint. It's a platform where hundreds, if not thousands, of business users collaborate on documents, spreadsheets, and other types of media. Without some sort of governance structure and standards, such an environment would quickly become chaotic. Therefore, any SharePoint implementation at the enterprise level requires a cross-functional governance board consisting of individuals from business units and IT.
One of the things the board will talk about is creating and maintaining a common look and feel across SharePoint sites - something that you, as a SharePoint programmer, will be intimately involved in. And that's just the tip of the iceberg: design, implementation guides, testing - all of these things will require input by a SharePoint expert.
Thousands of developers worldwide use LearnNowOnline to gain the technical skills they need to succeed on the job and advance their career.
One of the fundamental concepts of relational databases is the normalization of data into tables, or sets, of similar data. Different data elements are grouped into separate tables. Data about employees is in the dbo.Employees table, data about orders is in the dbo.Orders table, and so forth. The process of organizing the various elements of data into tables is called normalization. The key measure of successful normalization is the ability to join tables effectively.
As the SQL standard has evolved, so has the join notation, both inner and outer. The earliest join notation uses the WHERE clause to enforce the joining criteria. This article takes a deeper dive into outer joins.
Outer Joins
An outer join includes all of the rows from either or both of the tables in the join, even if there is a row in either table that doesn’t have a match in the other table. Three types of outer joins are possible: left, right, and full.
- A left outer join includes all rows of the first table, even if there is no matching row in the second table.
- A right outer join includes all rows of the second table, even if there is no matching row in the first table.
- A full outer join includes both tables so that all rows of both tables are part of the result set even if there isn’t a match in the other table.
The “left” and “right” directions simply reference which table you list first and second in the join clause, respectively.
The challenge of using outer joins is to know when they are appropriate, and which one to use. The following query uses an inner join to generate a list of all customers and the date of each customer’s first order. We’ll use this query to show how the different outer joins work for this relationship between the Customers and Orders table. A second query returns the total number of Customers.
SELECT CompanyName, MIN(Orders.OrderDate) AS FirstOrder
FROM dbo.Customers INNER JOIN dbo.Orders
ON Customers.CustomerID = Orders.CustomerID
GROUP BY CompanyName
ORDER BY CompanyName
SELECT COUNT(*) FROM dbo.Customers;
The query joins the Customers table to the Orders table and groups the results by company name. The MIN aggregate function returns the lowest order date for each customer. The first few rows of the result set shown in the Figure below display each customer and the date of their first order. The query returns 89 rows. You can see in the results for the second query that there are 91 customers in the table.
WARNING! Be careful with the number of rows reported by the query results window when executing multiple queries. When you first run those two SELECT queries, it reports 90 rows. But that is the total of all rows returned by the two queries, including the SELECT COUNT(*) query. To show the row count of 89 in the Figure above, we selected the grid for the first query’s results, so it displays the row count for just that one query.
Why is there a discrepancy between the number of customers in the table and the list of first orders? The list, generated using an INNER JOIN, doesn’t include every customer. The behavior of the inner join excludes any customers who have not placed any orders, which means that there isn’t a matching order record for two customers. If you need to see a list of all customers regardless of whether they’ve placed an order, as well as the date of the first order for customers who have, you need to use an outer join.
This post is an excerpt from the online courseware for our SQL Server 2012: T-SQL Select, Where, and Clauses course written by expert Don Kiely.
Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.
Thousands of developers worldwide use LearnNowOnline to gain the technical skills they need to succeed on the job and advance their career.
For years, software manufacturers have been extolling the virtues of "collaboration." Yet more often than not, this goal was a pipe dream. Corporate IT users were accustomed to the old ways of doing business, and besides, the market failed to deliver a user-friendly, scalable collaborative platform.
Microsoft's SharePoint has changed that. In fact, if SharePoint was to be considered a standalone software platform, it would rank as the fifth most popular one in the world. So it's no surprise that companies are embracing SharePoint and its seemingly limitless collaborative capabilities.
You can even argue that the companies that successfully master SharePoint will have a competitive edge over the competition. So if you're an aspiring IT worker, taking a SharePoint tutorial can do far more than land you a new career. It can help you grow a business - and that's something you'll certainly want to mention in any job interview.
Thousands of developers worldwide use LearnNowOnline to gain the technical skills they need to succeed on the job and advance their career.
A general best practice of data selection queries is that you never want to return more data from the database server than you’re going to use. Even with a tightly focused WHERE clause, a query might still return more data than a user can make use of. You can further restrict the number of rows returned from a query using the TOP clause of a SELECT statement.
The TOP clause lets you limit the number of rows in a result set. For example, the following query selects the three cities with the highest number of employees, no matter how many cities might otherwise be returned from the query.
SELECT TOP 3
City, COUNT(*) AS NumEmployees
FROM dbo.Employees
GROUP BY City
ORDER BY COUNT(*) DESC;
The Figure below shows the result set from the query. A key element is the ORDER BY clause, which sorts the result set in descending order before the TOP clause is applied. (A sort in ascending order would return the three cities with the fewest number of employees.)
However, one problem with a TOP query is the matter of ties. In the previous query, several cities have only one employee. If you want to see the ties for last place, you need to use the WITH TIES clause:
SELECT TOP 3 WITH TIES
City, COUNT(*) AS NumEmployees
FROM dbo.Employees
GROUP BY City
ORDER BY COUNT(*) DESC;
The Figure below shows the result set. The query returns all three cities that tied for last place. For the data in this table, only a TOP 2 query will return unique values.
TOP also enables you to specify a percent value rather than an absolute number. Here’s an example of using TOP with PERCENT to return the top 25% records. The results are shown in the Figure below.
SELECT TOP 25 PERCENT WITH TIES
City, COUNT(*) AS NumEmployees
FROM dbo.Employees
GROUP BY City
ORDER BY COUNT(*) DESC;
TIP: You can use any numeric expression, even a variable, to specify the number in a TOP clause. You can also use TOP in INSERT, UPDATE, and DELETE statements.
The GROUP BY clause in a SELECT statement is a powerful way to shape the data returned by a data selection query to perform basic data analysis. It might take some time to wrap your head around the set-based operation performed by non-trivial grouping, but it provides a very flexible way to wring information out of your data.
This post is an excerpt from the online courseware for our SQL Server 2012: T-SQL Select, Where, and Clauses course written by expert Don Kiely.
Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.
Thousands of developers worldwide use LearnNowOnline to gain the technical skills they need to succeed on the job and advance their career.
With the quick rise of HTML5 in the development of modern websites and applications, it's now more important than ever to learn the ins and outs of what's becoming the standard markup language of the World Wide Web.
With our comprehensive selection of HTML tutorial videos online, it's easy to become an expert in the essential coding language of the modern internet. Here are some more ways in which an HTML5 tutorial can help you:
- Become The Most Productive Developer You Can Be - If you're a developer, a big part of your job is staying up-to-date on all the latest trends in technology and design. A comprehensive online video tutorial will help ensure you're at the head of your professional field.
- Be Prepared To Meet The Demands Of Your Clients - To always be the most productive professional in your field you need to be able to deliver on all of the varied needs of your clients. Be keeping your skills sharp and up-to-date with video tutorials for C# and HTML5, you'll be best prepared to deliver the highest quality service to your clients.
- Be Ahead Of The Curve - HTML5 is becoming the defining language of the modern internet. It's used to build websites for the traditional and modern web, as well as the latest applications. Keeping yourself current on the latest applications of HTML5 will help your professional development.
Thousands of developers worldwide use LearnNowOnline to gain the technical skills they need to succeed on the job and advance their career.