In this example, we will explore some useful select queries that comes handy when we want to retrieve data from different tables. There many ways one can form select queries that returns the same result. For this article, we will use Microsoft’s Pubs database and explore Sub-Queries, Derived Tables and Common Table Expressions (CTE). We will explore these queries one by one.
In this article, we will explore how to use transactions in SQL server. Here, we will explore three distinct types SQL transactions. They are:
1) Auto Commit Transactions
2) Implicit Transactions
3) Explicit Transactions
Ranking Functions are useful to provide ranking on the table columns based on the value stored in it. Say, for example, let us say a table is having students id and their Total Marks as separate columns. Now, it is meaningful if we apply the these function on the Total Marks column rather than Student ID column. With these Functions, we can easily find 2nd maximum, or 4th maximum marks. The Ranking Functions are available in SQL 2005 and later versions.
In this example, first we will learn what is Shared and Exclusive locks. Then we will learn about Read Committed, Read UnCommitted, Repeatable Read , Serializable Transaction Isolations with an T-SQL example for each.
This example shows how one can use Local and Global Temp Tables. You will know the difference between them in two separate sections. Finally, the example creates text file for Bulk Insert and then shows a T-SQL example which reads this text file & performs bulk insert into the SQL Table.