Category Archives: Microsoft SQL Server

Microsoft SQL Server database, overview, FAQs, links

Database Compatibility and Replication

Our production databases and replication databases are running on SQL Server 2008. Recently a table column type is changed from VARCAHR(100) to VARCHAR(MAX). Replication failed showing the following error:

Column OtherInformation in object rAccounts contains type VarCharMax, which is not supported in the target server version, SQL Server 2000.

It is found that the DB compatibility level was set at 80 on all databases; it was modified to compatibility level 100 using the command:

EXEC sp_dbcmptlevel ‘CollectLive’, 100;

The replication is re-initialized, and it worked.

Data Partitioning in SQL Server 2000

There are two types of data partitioning: horizontal partitioning and vertical partitioning. In Horizontal Partitioning, a table is split horizontally with a subset of rows to form another similar table with the same number of columns. The structure of the table will remain the same. In Vertical Partitioning, a table with a large number of columns or very large columns is split into multiple partitions, each with the same number of rows but less number of columns. 

Here I am discussing the Horizontal Partitioning. We have a large table ORDERLINES with columns such as ORDER_NO, LINE_NO, CROSS_REF, USED_BUT_NOT_PRICED, LOST, and ORIG_COMM. Let us first partition ORDERLINES into four different tables. The data is divided with the following criteria:


1.       ORDER_NO <= ‘1020000’
2.       ORDER_NO BETWEEN ‘1020000’ AND ‘1040000’
3.       ORDER_NO BETWEEN ‘1040000’ AND ‘1060000’
4.       ORDER_NO >= ‘1060000’ 

Given below are the steps involved in this process: 

I. Create 4 tables with the same structure as that of ORDERLINES 

1. CREATE TABLE [dbo].[ORDERLINES01] (
[OLID] [int],
[ORDER_NO] [nchar] (15) NOT NULL ,
[LINE_NO] [int] NOT NULL ,
[LOST] [bit] NULL ,
[ORIG_COMM] [numeric](5, 2) NULL
) ON [PRIMARY]

2. CREATE TABLE [dbo].[ORDERLINES02] (
[OLID] [int],
[ORDER_NO] [nchar] (15) NOT NULL ,
[LINE_NO] [int] NOT NULL ,
[LOST] [bit] NULL ,
[ORIG_COMM] [numeric](5, 2) NULL
) ON [PRIMARY]

3. CREATE TABLE [dbo].[ORDERLINES03] (
[OLID] [int],
[ORDER_NO] [nchar] (15) NOT NULL ,
[LINE_NO] [int] NOT NULL ,
[LOST] [bit] NULL ,
[ORIG_COMM] [numeric](5, 2) NULL
) ON [PRIMARY]

4. CREATE TABLE [dbo].[ORDERLINES04] (
[OLID] [int],
[ORDER_NO] [nchar] (15) NOT NULL ,
[LINE_NO] [int] NOT NULL ,
[LOST] [bit] NULL ,
[ORIG_COMM] [numeric](5, 2) NULL
) ON [PRIMARY]

II. Insert data into these four tables  


1. INSERT INTO ORDERLINES01 ([ORDER_NO], [LINE_NO],
 [CROSS_REF], [SED_BUT_NOT_PRICED], [LOST], [ORIG_COMM])
 SELECT *
 FROM ORDERLINES
 WHERE (ORDER_NO <= '1020000')
     GO
     UPDATE ORDERLINES01
     SET OLID = '1'
     GO

2. INSERT INTO ORDERLINES02 ([ORDER_NO], [LINE_NO],
 [CROSS_REF], [USED_BUT_NOT_PRICED], [LOST], [ORIG_COMM])
 SELECT *
 FROM ORDERLINES
 WHERE (ORDER_NO BETWEEN '1020000' AND '1040000')
 GO
 UPDATE ORDERLINES02
 SET OLID = '2'
 GO

3. INSERT INTO ORDERLINES03 ([ORDER_NO], [LINE_NO],
 [CROSS_REF], [USED_BUT_NOT_PRICED], [LOST], [ORIG_COMM])
 SELECT *
 FROM ORDERLINES
 WHERE (ORDER_NO BETWEEN '1040000' AND '1060000')
    GO
    UPDATE ORDERLINES03
    SET OLID = '3'
    GO

4. INSERT INTO ORDERLINES04 ([ORDER_NO], [LINE_NO],
 [CROSS_REF], [USED_BUT_NOT_PRICED], [LOST],  ORIG_COMM])
 SELECT *
 FROM ORDERLINES
 WHERE (ORDER ORDER_NO >= '1060000')
    GO
    UPDATE ORDERLINES04
    SET OLID = '4'
    GO


 III. Build Constraints

For the Query Processor to know that each partitioned table contains only a certain type of data, we need to build CHECK constraints on each table on the ID that we have partitioned. Hence we will build CHECK constraints on OLID.                        

 1. ALTER TABLE ORDERLINES01
   ADD CONSTRAINT CK_OL1 CHECK (OLID=1)
  2. ALTER TABLE ORDERLINES02
   ADD CONSTRAINT CK_OL2 CHECK (OLID=2)
  3. ALTER TABLE ORDERLINES03
   ADD CONSTRAINT CK_OL3 CHECK (OLID=3)
  4. ALTER TABLE ORDERLINES04
   ADD CONSTRAINT CK_OL4 CHECK (OLID=4)

IV. Build the View

CREATE VIEW ORDERLINES
   AS
    SELECT * FROM ORDERLINES01
   UNION ALL
    SELECT * FROM ORDERLINES02
   UNION ALL
    SELECT * FROM ORDERLINES03
   UNION ALL
    SELECT * FROM ORDERLINES04

Conclusion 

The View shown above is the same as the original ORDERLINES table. But a query like

SELECT * FROM ORDERLINES WHERE OLID=2,

will not seek all the ORDERLINES records. It will limit the seek to ORDERLINES02 table only.

Bookmarks for SQL Server

These are my favourite references for SQL Server. Please feel free to suggest more:

SQL Server 2005 Command Rerference
DevGuru
SQL Server FAQ
Host .NET In SQL Server 2005 Express [ASP & .NET Tutorials]
How to connect to a SQL 2005 Server
How to debug T-SQL stored procedures
www.sqlservercentral.com
Idera Tools for Data Management – Products – SQLcheck
Introducing SQL Server 2005’s CLR Integration
Microsoft OLAP by Mosha Pasumansky Different forms of CrossJoin
Microsoft SQL Server – Lessons
Microsoft SQL Server 2005 Home
SQL Server Database – Administration and T-SQL Programming Tips, Tutorials and Resources
SQL Server Hardware Tuning and Performance Monitoring
SMO Capture Mode – SMO Tutorials
Sql Authority.com
SQL Backup – Compressed database backups, encrypted database backups
SQL Server Business Intelligence
SQL Server Magazine
SQL Server administration best practices Kondreddi’s home page
SQL Server Covering today’s SQL Server topics
SQL Server Database Performance Tuning And Other Articles
SQL Server DBA Questions & Answers
SQL Server Reference Guide
SQL Server Tips, Techniques and Articles
SQL Server undocumented stored procedures
SQL-server-2005-school at Programmers Heaven
SQL Server – ITtoolbox Groups
SQLIS.com – SQL Server Integration Services
SqlJunkies
T-SQL Learning Guide
The OLAP Report
Virtual Labs Home SQL Server 2005
What is new in SQL Server 2005
XQuery Inside SQL Server 2005

SQL Server 2005 Integration Services (SSIS): An Introduction

Microsoft SQL Server 2005 Integration Services (SSIS) is a platform for building high performance data integration solutions, including extraction, transformation, and load (ETL) packages for data warehousing.

Integration Services includes graphical tools and wizards for building and debugging packages; tasks for performing workflow functions such as FTP operations, for executing SQL statements, or for sending e-mail messages; data sources and destinations for extracting and loading data; transformations for cleaning, aggregating, merging, and copying data; a management service, the Integration Services service, for administering Integration Services; and application programming interfaces (APIs) for programming the Integration Services object model.

Administrators frequently want to automate administrative functions such as backing up and restoring databases, copying SQL Server databases and the objects they contain, copying SQL Server objects, and loading data. Integration Services packages can perform these functions.

Integration Services includes tasks that are specifically designed to copy SQL Server database objects such as tables, views, and stored procedures; copy SQL Server objects such as databases, logins, and statistics; and add, change, and delete SQL Server objects and data by using Transact-SQL statements.  

Administration of an OLTP or OLAP database environment frequently includes the loading of data. Integration Services includes several tasks that facilitate the bulk loading of data. You can use a task to load data from text files directly into SQL Server tables and views, or you can use a destination component to load data into SQL Server tables and views after applying transformations to the column data.

An Integration Services package can run other packages. A data transformation solution that includes many administrative functions can be separated into multiple packages so that managing and reusing the packages is easier.

If you need to perform the same administrative functions on different servers, you can use packages. A package can use looping to enumerate across the servers and perform the same functions on multiple computers. To support administration of SQL Server, Integration Services provides an enumerator that iterates across SQL Management Objects (SMO) objects. For example, a package can use the SMO enumerator to perform the same administrative functions on every job in the Jobs collection of a SQL Server installation.

SSIS packages can also be scheduled using SQL Server Agent Jobs. SQL Server is controlled by a set of services. The main service that starts the database is called MSSQLServer. If you see that service name followed by a $ sign and then more letters, your server has been installed more than once, with something called an Instance Name. An instance is just a way of having more than one SQL Server running on a single box. Another main service is the SQLServerAgent. This is a service that controls all of the automatic things that run on the server, sort of an AT command or a CRON (in Unix) system if you’re used to those programs.

New with T-SQL in SQL Server 2005

Three most important features in T-SQL are: 

  • Error handling: TRY and CATCH
  • Transferring rows to columns: PIVOT and UNPIVOT
  • XML enhancements

1. Error handling with TRY and CATCH

The TRY and CATCH paradigm is similar to many .NET languages.

BEGIN TRYSELECT 1/0;END TRY 

BEGIN CATCHSELECTERROR_NUMBER() AS ErrorNumber,ERROR_SEVERITY() AS ErrorSeverity,ERROR_STATE() AS ErrorState,ERROR_PROCEDURE() AS ErrorProcedure,ERROR_LINE() AS ErrorLine,ERROR_MESSAGE() AS ErrorMessage;END CATCH;

2. Transferring rows to columns with PIVOT and UNPIVOT

These commands enable the quick shifting of rows to columns and vice versa with few coding changes.

USE AdventureWorks;GOSELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5FROM (SELECT PurchaseOrderID, EmployeeID, VendorIDFROM Purchasing.PurchaseOrderHeader) pPIVOT(COUNT (PurchaseOrderID)FOR EmployeeID IN( [164], [198], [223], [231], [233] )) AS pvtORDER BY VendorID

3. XML enhancements

XML has become prevalent in transferring data across many heterogeneous environments and between many Microsoft applications; the SQL Server 2005 XML features improve the inherent capabilities to create, store, transport and query XML data. It is now possible to achieve the following natively in SQL Server:

  • Create an XML schema that can be referenced in a table’s column.
CREATE XML SCHEMA COLLECTION [ . ]sql_identifier AS Expression
  • Create a native data type for table creation with pointers out to the XML Schema Collection, which are separate data pages from the base table, similar to a BLOB in SQL Server 2000.
CREATE TABLE Orders
(OrderID int PRIMARY KEY NOT NULL, 
OrderDetailsID int NOT NULL,
OrderDate datetime NOT NULL,
XMLOrder xml NOT NULL)
  • Create a variable as the XML data type for stored procedure or ad-hoc transactions.
DECLARE @OrdersSchema xml
CREATE XML SCHEMA COLLECTION
OrdersSchema AS @OrdersSchema
  • Improve the access to the XML data by creating primary and secondary indexes.
CREATE PRIMARY XML INDEX PXML_Orders_OrderID
ON OrdersSchema.Orders (OrderID);
GO
CREATE XML INDEX SXML_Order_OrderDetailsID
ON OrdersSchema.Orders (OrderDetailsID)
USING XML INDEX PXML_Orders_OrderID FOR PATH ;
GO
  • Query XML data via a simple SELECT statement to return the XML as a portion of the result set with the remainder of the columns in the table.
SELECT * 
FROM Orders
WHERE OrderID = 123

Q&A: Database Programming

  1. What are cursors? Explain different types of cursors. What are the disadvantages of cursors? How can you avoid cursors?

Cursors allow row-by-row processing of the resultsets. Types of cursors: Static, Dynamic, Forward-only, Keyset-driven. See books online for more information. Disadvantages of cursors: Each time you fetch a row from the cursor, it results in a network roundtrip, where as a normal SELECT query makes only one roundtrip, however large the resultset is. Cursors are also costly because they require more resources and temporary storage (results in more IO operations). Further, there are restrictions on the SELECT statements that can be used with some types of cursors. Most of the times, set based operations can be used instead of cursors. Here is an example: If you have to give a flat hike to your employees using the following criteria:

 

Salary between 30000 and 40000 — 5000 hike

Salary between 40000 and 55000 — 7000 hike

Salary between 55000 and 65000 — 9000 hike.

 

In this situation many developers tend to use a cursor, determine each employee’s salary and update his salary according to the above formula. But the same can be achieved by multiple update statements or can be combined in a single UPDATE statement as shown below:

UPDATE tbl_emp SET salary = CASE WHEN salary BETWEEN 30000 AND 40000 THEN salary + 5000 WHEN salary BETWEEN 40000 AND 55000 THEN salary + 7000 WHEN salary BETWEEN 55000 AND 65000 THEN salary + 10000 END

Another situation in which developers tend to use cursors: You need to call a stored procedure when a column in a particular row meets certain condition. You don’t have to use cursors for this. This can be achieved using WHILE loop, as long as there is a unique key to identify each row.

 

2. Write down the general syntax for a SELECT statements covering all the options.

Here’s the basic syntax:

SELECT select_list [INTO new_table_] FROM table_source [WHERE search_condition] [GROUP BY group_by_expression] [HAVING search_condition] [ORDER BY order_expression [ASC | DESC] ]

3. What is a join and explain different types of joins.

Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table. Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS. For more information see pages from books online titled: “Join Fundamentals” and “Using Joins”.

 

Join conditions can be specified in either the FROM or WHERE clauses; specifying them in the FROM clause is recommended. WHERE and HAVING clauses can also contain search conditions to further filter the rows selected by the join conditions. Joins can be categorized as:

  • Inner joins (the typical join operation, which uses some comparison operator like = or <>).

These include equi-joins and natural joins. Inner joins use a comparison operator to match rows from two tables based on the values in common columns from each table. For example, retrieving all rows where the student identification number is the same in both the students and courses tables.

  • Outer joins. Outer joins can be a left, a right, or full outer join.

Outer joins are specified with one of the following sets of keywords when they are specified in the FROM clause: LEFT JOIN or LEFT OUTER JOIN. The result set of a left outer join includes all the rows from the left table specified in the LEFT OUTER clause, not just the ones in which the joined columns match. When a row in the left table has no matching rows in the right table, the associated result set row contains null values for all select list columns coming from the right table.

  • RIGHT JOIN or RIGHT OUTER JOIN

A right outer join is the reverse of a left outer join. All rows from the right table are returned. Null values are returned for the left table any time a right table row has no matching row in the left table.

  • FULL JOIN or FULL OUTER JOIN

A full outer join returns all rows in both the left and right tables. Any time a row has no match in the other table, the select list columns from the other table contain null values. When there is a match between the tables, the entire result set row contains data values from the base tables.

  • Cross joins

Cross joins return all rows from the left table. Each row from the left table is combined with all rows from the right table. Cross joins are also called Cartesian products.

For example, here is an inner join retrieving the employees who are also sales persons:

 

USE AdventureWorks;
GO
SELECT e.EmployeeID FROM HumanResources.Employee AS e INNER JOIN
Sales.SalesPerson AS s ON e.EmployeeID = s.SalesPersonID

 

The tables or views in the FROM clause can be specified in any order with an inner join or full outer join. However, the order of tables or views specified when using either a left or right outer join is important.
Inner joins return rows only when there is at least one row from both tables that matches the join condition. Inner joins eliminate the rows that do not match with a row from the other table. Outer joins, however, return all rows from at least one of the tables or views mentioned in the FROM clause, as long as those rows meet any WHERE or HAVING search conditions. All rows are retrieved from the left table referenced with a left outer join, and all rows from the right table referenced in a right outer join. All rows from both tables are returned in a full outer join.

4. Can you have a nested transaction?Yes. Check out BEGIN TRAN, COMMIT, ROLLBACK, SAVE TRAN and @@TRANCOUNT

5. What is an extended stored procedure? Can you instantiate a COM object by using T-SQL?

An extended stored procedure is a function within a DLL (written in a programming language like C, C++ using Open Data Services (ODS) API) that can be called from T-SQL, just the way we call normal stored procedures using the EXEC statement. See books online to learn how to create extended stored procedures and how to add them to SQL Server. You can instantiate a COM (written in languages like VB, VC++) object from T-SQL by using sp_OACreate stored procedure. Also see books online for sp_OAMethod, sp_OAGetProperty, sp_OASetProperty, sp_OADestroy.

6. What is the system function to get the current user’s user id?

USER_ID(). Also check out other system functions like USER_NAME(), SYSTEM_USER, SESSION_USER, CURRENT_USER, USER, SUSER_SID(), HOST_NAME().

7. What are triggers? How many triggers you can have on a table? How to invoke a trigger on demand?

Triggers are special kind of stored procedures that get executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table. In SQL Server 6.5 you could define only 3 triggers per table, one for INSERT, one for UPDATE and one for DELETE. From SQL Server 7.0 onwards, this restriction is gone, and you could create multiple triggers per each action. But in 7.0 there’s no way to control the order in which the triggers fire. In SQL Server 2000 you could specify which trigger fires first or fires last using sp_settriggerorder. Triggers can’t be invoked on demand. They get triggered only when an associated action (INSERT, UPDATE, DELETE) happens on the table on which they are defined. Triggers are generally used to implement business rules, auditing. Triggers can also be used to extend the referential integrity checks, but wherever possible, use constraints for this purpose, instead of triggers, as constraints are much faster. Till SQL Server 7.0, triggers fire only after the data modification operation happens. So in a way, they are called post triggers. But in SQL Server 2000 you could create pre triggers also. Search SQL Server 2000 books online for INSTEAD OF triggers. Also check out books online for ‘inserted table’, ‘deleted table’ and COLUMNS_UPDATED()

8. There is a trigger defined for INSERT operations on a table, in an OLTP system. The trigger is written to instantiate a COM object and pass the newly inserted rows to it for some custom processing. What do you think of this implementation? Can this be implemented better?

Instantiating COM objects is a time consuming process and since you are doing it from within a trigger, it slows down the data insertion process. Same is the case with sending emails from triggers. This scenario can be better implemented by logging all the necessary data into a separate table, and have a job which periodically checks this table and does the needful.

9. What is a self join? Explain it with an example.

Self join is just like any other join, except that two instances of the same table will be joined in the query. Here is an example: Employees table which contains rows for normal employees as well as managers. So, to find out the managers of all the employees, you need a self join.

CREATE TABLE emp 
(
empid int,
mgrid int,
empname char(10)
)
INSERT emp SELECT 1,2,’Vyas’
INSERT emp SELECT 2,3,’Mohan’
INSERT emp SELECT 3,NULL,’Shobha’
INSERT emp SELECT 4,2,’Shridhar’
INSERT emp SELECT 5,2,’Sourabh’
SELECT t1.empname [Employee], t2.empname [Manager]
FROM emp t1, emp t2
WHERE t1.mgrid = t2.empid
Here’s an advanced query using a LEFT OUTER JOIN that even returns the employees without managers (super bosses)SELECT t1.empname [Employee], COALESCE(t2.empname, ‘No manager’) [Manager]
FROM emp t1
LEFT OUTER JOIN
emp t2
ON
t1.mgrid = t2.empid 

 10. Given an employee table, how would you find out the second highest salary?

CREATE PROC nth 
(
        @table_name sysname,
        @column_name sysname,
        @nth int
)
AS
BEGIN
--Purpose: To find out the nth highest number in a column. 
--Input parameters: Table name, Column name, and the nth position
SET @table_name = RTRIM(@table_name)
SET @column_name = RTRIM(@column_name)
DECLARE @exec_str CHAR(400)
IF (SELECT OBJECT_ID(@table_name,'U')) IS NULL
BEGIN
        RAISERROR('Invalid table name',18,1)
        RETURN -1
END
IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = @table_name AND COLUMN_NAME = @column_name)
BEGIN
        RAISERROR('Invalid column name',18,1)
        RETURN -1
END 
IF @nth <= 0
BEGIN
        RAISERROR('nth highest number should be greater than Zero',18,1)
        RETURN -1
END
SET @exec_str = 'SELECT MAX(' + @column_name + ') from ' + @table_name + ' 
WHERE ' + @column_name + ' NOT IN ( SELECT TOP ' + LTRIM(STR(@nth - 1)) + ' ' + @column_name + ' FROM ' + @table_name 
+ ' ORDER BY ' + @column_name + ' DESC )'
EXEC (@exec_str)
END