SQL Server Hints: 2008

Monday, December 22, 2008

Strategies for Refactoring Cursors

Cursor usage and performance issues

T-SQL PIVOT Statement

http://geekswithblogs.net/lorint/archive/2006/08/04/87166.aspx

Sunday, December 21, 2008

Five Steps to Building a Cursor

1- Declare the variables that will hold the data you want returned by the cursor.
2- Use the DECLARE CURSOR statement to define the SELECT statement for the cursor.
3- Use the OPEN statement to execute the SELECT statement and populate the cursor.
4- Use the FETCH NEXT INTO statement to retrieve values from the next row and to store the column values for that row in the variables.
5- Issue the CLOSE and DEALLOCATE statements to close the cursor and free all cursor resources.

Four Reasons Why Cursors Are Slow

1- Each FETCH in a cursor typically has the same performance as a SELECT statement. Therefore if the cursor needs to retrieve 1,000 rows, this is equivalent to the same number of SELECT statements.
2- Cursors use large amounts of memory.
3- Cursors can cause locking problems in the database.
4- Cursors consume network bandwidth when the results are sent to the client.

Six Reasons Developers Build Cursors

NU :1
Type of problem : Complex Logic
Description : Can be difficult to translate into a set-based solution due to variable formulas and several exceptions.
Often developed by one of two solutions:
- Code all logic within a cursor loop.
- Create a stored procedure that accepts a single ID, processes asingle row, and then returns thecalculated value or updates the row.
Recommended solution : Refactor the logic as a data-driven query by using a case expression to handle the variations. Use a series of set-based queries so that each query handles one type of formula or logic.
When to use cursors : Very rarely

NU :2
Type of problem : Dynamic code iteration
Description : Must construct and execute dynamic data definition language (DDL) code.
Recommended solution : For this situation, cursors are the best solution.
When to use cursors : Always

NU :3
Type of problem : Denormalizing a list
Description : Converts a vertical list of values to a single comma-delimited horizontal list or string. It is often better to report data as a string than as a sub-report. It is easier to read and saves space.
Recommended solution : Although this is often accomplished with a cursor, itis possible to build a set-based solution.
When to use cursors : Sometimes

NU :4
Type of problem : Building a cross-tab query
Description : Traditionally viewed as very difficult using Microsoft® SQL Server™.
Recommended solution : Building a cross-tab query requires using a series of case expressions. SQL Server 2005 has the new PIVOT syntax, which effectively is the same as a series of case expressions.
When to use cursors : Never Constructing a dynamic cross-tab query requires using a cursor to build the columns for the dynamic SQL

NU :5
Type of problem : Cumulative totals (running sums)
Description : Easily added in the reporting tool but sometimes must be calculated within SQL Server and written to a table. Example: when the cumulative total must be retained for integrity reasons, such as a balance column.
Recommended solution : Although cumulative totals can be determined using a correlated subquery, a cursor actually scales better when solving this problem.
hen to use cursors : Always

NU :6
Type of problem : Navigating a hierarchical tree
Description : Procedural programmers typically approach this task by recursively examining each node.
Recommended solution : Solve using several set-based methods using stored procedures or user-defined functions, which perform an order of magnitude faster than the procedural method.
When to use cursors : Never

Thursday, December 18, 2008

Project REAL: Technical Overview

http://www.microsoft.com/technet/prodtechnol/sql/2005/projreal.mspx

Wednesday, December 17, 2008

Schema Design in Optimization

Performance Optimization Model

Monday, December 15, 2008

Blocked Process Report

concurrency related problems and control - Isolation levels

concurrency related problems

1-dirty read
2-Nonrepeatable read

3-lost data

4-phantom read

------------------------------------

concurrency control

1-Pessimistic Control.

(SQL Server locks the resource until user performs the action she/he needs and then release for others.)

1/1- Read Uncommitted Isolation Level

1/2- Read Committed Isolation Level

1/3- Repeatable Read Isolation Level

1/4- Serializable Isolation Level

2-Optimistic Control.

(SQL Server does not hold locks but once read, check for inconsistency for next read.)

2/1- Snapshot Isolation Level

2/2- Read Committed Snapshot Isolation Level

Guidelines for Identifying Locking and Blocking

Query Performance

Query Performance.jpg

Measuring Database Performance

Tuesday, November 25, 2008

Notification Services Tutorial

http://msdn.microsoft.com/en-us/library/ms170337(SQL.90).aspx

SQL Server Notification Services Samples

http://msdn.microsoft.com/en-us/library/ms160889(SQL.90).aspx

Saturday, November 22, 2008

A list of the bugs that are fixed in SQL Server 2005 Service Pack 2

913939 (http://support.microsoft.com/kb/913939/ )
FIX: The return value of the Member_Unique_Name property in SQL Server 2005 Analysis Services differs from the return value in SQL Server 2000 Analysis Services
917872 (http://support.microsoft.com/kb/917872/ )
FIX: Error message when you try to connect to SQL Server 2005 Analysis Services from Object Explorer: "System.OutOfMemoryException"
917885 (http://support.microsoft.com/kb/917885/ )
Error message when you process a data mining model in SQL Server 2005 Business Intelligence Development Studio: "Memory error: Allocation failure : Not enough storage is available to process this command"
917886 (http://support.microsoft.com/kb/917886/ )
FIX: Error message after you roll back transactions on a table in SQL Server 2005: "Error: 3315, Severity: 21, State: 1"
917888 (http://support.microsoft.com/kb/917888/ )
FIX: Error message when you use a server-side cursor to run a large complex query in SQL Server 2005: "Error: 8623, Severity: 16, State: 1 The query processor ran out of internal resources"
917905 (http://support.microsoft.com/kb/917905/ )
FIX: SQL Server 2005 performance may be slower than SQL Server 2000 performance when you use an API server cursor
917940 (http://support.microsoft.com/kb/917940/ )
FIX: The prediction performance of the Microsoft Naive Bayes algorithm may be slower than the prediction performance of other algorithms for the same mining model in SQL Server 2005 Analysis Services
917971 (http://support.microsoft.com/kb/917971/ )
FIX: You may receive more than 100,000 page faults when you try to back up a SQL Server 2005 database that contains hundreds of files and file groups
918105 (http://support.microsoft.com/kb/918105/ )
FIX: You may receive an error message when you run Database Engine Tuning Advisor by using the dta command-prompt utility in SQL Server 2005
918222 (http://support.microsoft.com/kb/918222/ )
Cumulative hotfix package (build 2153) for SQL Server 2005 is available
918276 (http://support.microsoft.com/kb/918276/ )
FIX: You notice additional random trailing character in values when you retrieve the values from a fixed-size character column or a fixed-size binary column of a table in SQL Server 2005
918529 (http://support.microsoft.com/kb/918529/ )
FIX: You cannot open a SQL Server 2005 report in some versions of Excel after you render the report to an Excel format
918555 (http://support.microsoft.com/kb/918555/ )
FIX: Error message when you use Report Manager in SQL Server 2005 Reporting Services to view a report: "The request failed with an empty response"
918644 (http://support.microsoft.com/kb/918644/ )
FIX: The SQL Server Integration Services (SSIS) service does not start when you install SQL Server 2005 SP1 on a computer that is already running the SSIS service
918735 (http://support.microsoft.com/kb/918735/ )
FIX: You may experience decreased performance when you browse a ragged hierarchy dimension that uses the HideMemberIf property in SQL Server 2005 Analysis Service
918747 (http://support.microsoft.com/kb/918747/ )
FIX: You receive an access violation error message when you try to restore a SQL Server 2005 Analysis Services database
918753 (http://support.microsoft.com/kb/918753/ )
FIX: You receive an access violation error message on a server that is running SQL Server 2005 Analysis Services when you run a MDX query that contains a user-defined function
918756 (http://support.microsoft.com/kb/918756/ )
FIX: The restore operation may fail when you try to restore a database to a new database name in SQL Server 2005 Analysis Services
918757 (http://support.microsoft.com/kb/918757/ )
FIX: The value of a cell is not updated correctly when you use multiple UPDATE CUBE statements inside a single transaction in SQL Server 2005 Analysis Services
918833 (http://support.microsoft.com/kb/918833/ )
FIX: A cell writeback operation in SQL Server 2005 Analysis Services takes a significantly longer time than a cell writeback operation in SQL Server 2000 Analysis Services
918882 (http://support.microsoft.com/kb/918882/ )
FIX: A query plan is not cached in SQL Server 2005 when the text of the hint is a large object
919144 (http://support.microsoft.com/kb/919144/ )
FIX: An access violation may occur when you enable the "lazy schema validation" option for any linked server and when you run a distributed partitioned view query in SQL Server 2005
919243 (http://support.microsoft.com/kb/919243/ )
FIX: Some rows in the Text Data column are always displayed for a trace that you create by using SQL Server Profiler in SQL Server 2005
919478 (http://support.microsoft.com/kb/919478/ )
FIX: "Select All" may not be available when you try to preview a report in Report Designer after you install SQL Server 2005 Service Pack 1
919538 (http://support.microsoft.com/kb/919538/ )
FIX: SQL Server 2005 Reporting Services cannot generate an error message in a report when the data source of the report calls a stored procedure that contains the RAISERROR statement
919556 (http://support.microsoft.com/kb/919556/ )
FIX: You cannot select the only available value from a drop-down list of a muti-value parameter in Report Manager in Microsoft SQL Server 2005 Reporting Services
919611 (http://support.microsoft.com/kb/919611/ )
FIX: The value of the automatic growth increment of a database file may be very large in SQL Server 2005 with Service Pack 1
919636 (http://support.microsoft.com/kb/919636/ )
FIX: Memory usage of the compiled query plan may unexpectedly increase in SQL Server 2005
919731 (http://support.microsoft.com/kb/919731/ )
FIX: SQL Server Analysis Services 2005 system performance may be slow after you use the ADOMD.NET data provider to run an MDX query that returns a large result set from a cube
919775 (http://support.microsoft.com/kb/919775/ )
FIX: The BULK INSERT statement may not return any errors when you try to import data from a text file to a table by using the BULK INSERT statement in Microsoft SQL Server 2005
919839 (http://support.microsoft.com/kb/919839/ )
FIX: The Distribution Agent may not apply transactions when you use transactional replication with a published Oracle table and the table contains a column that uses the number data type in SQL Server 2005
919905 (http://support.microsoft.com/kb/919905/ )
FIX: A query may take longer to run in SQL Server 2005 SP1 than it takes to run in the original release version of SQL Server 2005 or in SQL Server 2000
919929 (http://support.microsoft.com/kb/919929/ )
FIX: Error message when the Replication Merge Agent runs in SQL Server 2005: "Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199402"
919949 (http://support.microsoft.com/kb/919949/ )
FIX: The sp_MSinitDynamicSubscriber stored procedure is called more than one time by using the same parameters in SQL Server 2005
919955 (http://support.microsoft.com/kb/919955/ )
FIX: You may receive incorrect results when you run a Multidimensional Expressions (MDX) query that involves a calculated member and the autoexist functionality in SQL Server 2005 Analysis Services
919956 (http://support.microsoft.com/kb/919956/ )
FIX: Error message when you execute an MDX query that calls a VBA function in SQL Server 2005 Service Pack 1: "PFE_ASSEMBLY_NOT_SAFETOPREPARE"
919957 (http://support.microsoft.com/kb/919957/ )
FIX: Some cells return the NULL value instead of returning the actual value when you query a dimension that contains a parent/child hierarchy in a SQL Server 2005 Analysis Services cube
920203 (http://support.microsoft.com/kb/920203/ )
FIX: You receive inconsistent return values when you use Microsoft SQL Server 2005 Analysis Services to perform several queries for the same measure in a local cube
920205 (http://support.microsoft.com/kb/920205/ )
FIX: A memory leak may occur when you run SQL Server 2005 Integration Services packages that are stored in the SSIS Package store
920206 (http://support.microsoft.com/kb/920206/ )
FIX: System performance may be slow when an application submits many queries against a SQL Server 2005 database that uses simple parameterization
920340 (http://support.microsoft.com/kb/920340/ )
FIX: A query may return incorrect results when you run the query against a SQL Server 2005 Analysis Services cube
920346 (http://support.microsoft.com/kb/920346/ )
FIX: SQL Server 2005 may overestimate the cardinality of the JOIN operator when a SQL Server 2005 query contains a join predicate that is a multicolumn predicate
920347 (http://support.microsoft.com/kb/920347/ )
FIX: The SQL Server 2005 query optimizer may incorrectly estimate the cardinality for a query that has a predicate that contains an index union alternative
920794 (http://support.microsoft.com/kb/920794/ )
FIX: The size of the e-mail message is very large when you use Database Mail in SQL Server 2005 to send query results to users
920882 (http://support.microsoft.com/kb/920882/ )
FIX: You cannot deploy a SQL Server 2005 Integration Services package if you use an XML configuration file whose file name contains an uppercase letter
920974 (http://support.microsoft.com/kb/920974/ )
FIX: SQL Server 2005 treats an identity column in a view as an ordinary int column when the compatibility level of the database is set to 80
921003 (http://support.microsoft.com/kb/921003/ )
FIX: You may receive an error message when you manually define a Back Up Database task in SQL Server 2005 to back up the transaction log
921106 (http://support.microsoft.com/kb/921106/ )
FIX: You receive an error message when you try to create a differential database backup in SQL Server 2005
921292 (http://support.microsoft.com/kb/921292/ )
FIX: You may receive a "0x80004005 (E_FAIL)" error message when you use a range rowset in OLE DB for OLAP in a SQL Server 2005 Analysis Services-based application
921294 (http://support.microsoft.com/kb/921294/ )
FIX: You may receive an error message when you try to process a measure group under a low-memory condition in SQL Server 2005 Analysis Services
921395 (http://support.microsoft.com/kb/921395/ )
FIX: The color and the background image may not appear when you try to display a report in HTML format in Report Manager in SQL Server 2005 Reporting Services
921405 (http://support.microsoft.com/kb/921405/ )
FIX: An image placeholder that contains a red X appears when you run a report in SQL Server 2005 Reporting Services
921536 (http://support.microsoft.com/kb/921536/ )
FIX: A handled access violation may occur in the CValSwitch::GetDataX function when you run a complex query in SQL Server 2005
922063 (http://support.microsoft.com/kb/922063/ )
FIX: You may notice a large increase in compile time when you enable trace flags 2389 and 2390 in SQL Server 2005 Service Pack 1
922296 (http://support.microsoft.com/kb/922296/ )
FIX: You may receive an access violation error message when you run a query against a linked server in SQL Server 2005
922438 (http://support.microsoft.com/kb/922438/ )
FIX: A query may take a long time to compile when the query contains several JOIN clauses against a SQL Server 2005 database
922527 (http://support.microsoft.com/kb/922527/ )
FIX: Error message when you schedule some SQL Server 2005 Integration Services packages to run as jobs: "Package has been cancelled"
922577 (http://support.microsoft.com/kb/922577/ )
FIX: You may receive "UnavailableException" and "LogonFailedException" error messages when you perform a stress test on the report server in SQL Server 2005 Reporting Services
922578 (http://support.microsoft.com/kb/922578/ )
FIX: In SQL Server 2005, the sp_altermessage stored procedure does not suppress system error messages that are logged in the SQL Server error log and in the Application log
922594 (http://support.microsoft.com/kb/922594/ )
FIX: Error message when you use SQL Server 2005: "High priority system task thread Operating system error Exception 0xAE encountered"
922638 (http://support.microsoft.com/kb/922638/ )
FIX: An EXCEPTION_STACK_OVERFLOW exception may occur when you try to run a Transact-SQL query in SQL Server 2005
922798 (http://support.microsoft.com/kb/922798/ )
FIX: A member property is returned in an unexpected data type when you call the "IMDDataset::GetAxisRowset" interface in SQL Server 2005 Analysis Services
922804 (http://support.microsoft.com/kb/922804/ )
FIX: After you detach a Microsoft SQL Server 2005 database that resides on network-attached storage, you cannot reattach the SQL Server database
922853 (http://support.microsoft.com/kb/922853/ )
FIX: You may receive an error message when you try to use the Aggregation Design Wizard to design aggregations in SQL Server 2005 Analysis Services
923008 (http://support.microsoft.com/kb/923008/ )
FIX: Error message when you use the Bcp.exe utility to import data into a table from a text file in SQL Server 2005: "The memory could not be written"
923296 (http://support.microsoft.com/kb/923296/ )
FIX: Log Reader Agent fails, and an assertion error message is logged when you use transactional replication in SQL Server 2005
923300 (http://support.microsoft.com/kb/923300/ )
FIX: The calculated members are not returned in subsequence levels when you run an XML for Analysis (XMLA) script to retrieve child members at a level that is in a hierarchy in SQL Server 2005 Analysis Services
923301 (http://support.microsoft.com/kb/923301/ )
FIX: SQL Server 2005 Analysis Services continually generates minidump files when you try to deploy a new cube
923304 (http://support.microsoft.com/kb/923304/ )
FIX: Error message when you run a MDX query after you install the cumulative hotfix package for SQL Server 2005 Analysis Services for Itanium-based operating systems (build 9.0.2153): "Internal error: An unexpected exception occured"
923475 (http://support.microsoft.com/kb/923475/ )
FIX: The cells may be incorrectly updated when you execute multiple UPDATE CUBE statements in a single transaction in SQL Server 2005 Analysis Services
923476 (http://support.microsoft.com/kb/923476/ )
FIX: The query may never finish when you run a Multidimensional Expressions (MDX) query against a parent/child dimension in SQL Server 2005 Analysis Services
923477 (http://support.microsoft.com/kb/923477/ )
FIX: Unfiltered results may be generated when you use a drillthrough action on a dimension that is stored in the ROLAP storage mode in SQL Server 2005 Analysis Services
923478 (http://support.microsoft.com/kb/923478/ )
FIX: You receive an error message when you deploy a cube dimension that has a dimension description in Business Intelligence Development Studio
923605 (http://support.microsoft.com/kb/923605/ )
FIX: A deadlock occurs and a query never finishes when you run the query on a computer that is running SQL Server 2005 and has multiple processors
923623 (http://support.microsoft.com/kb/923623/ )
FIX: You receive an error message, when you use Report Builder after you install SQL Server 2005 Reporting Services SP1: “Short time pattern does not include hours and minutes”
923624 (http://support.microsoft.com/kb/923624/ )
FIX: Error message when you run an application against SQL Server 2005 that uses many unique user logins or performs many user login impersonations: "insufficient system memory to run this query"
923849 (http://support.microsoft.com/kb/923849/ )
FIX: When you run a query that references a partitioned table in SQL Server 2005, query performance may decrease
924012 (http://support.microsoft.com/kb/924012/ )
FIX: Error message when you try to process a cube that has a relationship between two tables in a data source view that contains more than four keys in SQL Server 2005 Analysis Services: "000042ac"
924264 (http://support.microsoft.com/kb/924264/ )
FIX: The metadata of the Description object of a Key Performance Indicator appears in the default language after you define a translation for the Description object in SQL Server 2005 Business Intelligence Development Studio
924291 (http://support.microsoft.com/kb/924291/ )
FIX: Error message when you execute a user-defined function in SQL Server 2005: "Invalid length parameter passed to the SUBSTRING function" or "A transport-level error has occurred when receiving results from the server"
924344 (http://support.microsoft.com/kb/924344/ )
FIX: You may receive incorrect results when you use the bulk copy program command-line utility to import data in SQL Server 2005
924345 (http://support.microsoft.com/kb/924345/ )
FIX: Error message when you try to open a file attachment in a message that is generated by the sp_send_dbmail procedure in SQL Server 2005: "This file is not in a recognizable format"
924346 (http://support.microsoft.com/kb/924346/ )
FIX: An access violation may intermittently occur when you run a SQL Server Integration Services package in SQL Server 2005
924601 (http://support.microsoft.com/kb/924601/ )
FIX: A partitioned table may generate an inefficient query plan when you run a Transact-SQL query that uses the Top n clause on a partitioned table in SQL Server 2005
924807 (http://support.microsoft.com/kb/924807/ )
FIX: The restore operation may take a long time to finish when you restore a database in SQL Server 2005
924808 (http://support.microsoft.com/kb/924808/ )
FIX: You receive an error message, or you obtain an incorrect result when you query data in a partitioned table that does not have a clustered index in SQL Server 2005
924939 (http://support.microsoft.com/kb/924939/ )
An update is available for SQL Server 2005 Analysis Services that adds partition information to the existing schema that is returned by the DISCOVER_PARTITION_INFO rowset
924954 (http://support.microsoft.com/kb/924954/ )
FIX: Error message when you use a table-valued function (TVF) together with the CROSS APPLY operator in a query in SQL Server 2005: "There is insufficient system memory to run this query"
925057 (http://support.microsoft.com/kb/925057/ )
FIX: Color images in a report appear in grayscale after you export the report to a PDF file in SQL Server 2005 Reporting Services
925058 (http://support.microsoft.com/kb/925058/ )
FIX: Error message when you create a new subscription to a report in SQL Server 2005 Reporting Services: "Error: Object expected"
925135 (http://support.microsoft.com/kb/925135/ )
FIX: An empty string is replicated as a NULL value when you synchronize a table to a SQL Server 2005 Compact Edition subscriber
925153 (http://support.microsoft.com/kb/925153/ )
FIX: You may receive different date values for each row when you use the getdate function within a case statement in SQL Server 2005
925227 (http://support.microsoft.com/kb/925227/ )
FIX: Error message when you call the SQLTables function against an instance of SQL Server 2005: "Invalid cursor state (0)"
925277 (http://support.microsoft.com/kb/925277/ )
FIX: You may experience very large growth increments of a principal database after you manually fail over a database mirroring session in SQL Server 2005
925278 (http://support.microsoft.com/kb/925278/ )
FIX: A query may crash, and you may receive an error message when an MDX query uses the Filter function at the start to slice a partition in SQL Server 2005 Analysis Services: "An unexpected exception occurred"
925335 (http://support.microsoft.com/kb/925335/ )
FIX: Error message when you use a label after a Transact-SQL query in SQL Server 2005: "Incorrect syntax near 'X'"
925387 (http://support.microsoft.com/kb/925387/ )
FIX: Error message when you use a unary operator in a parent/child dimension in a SQL Server 2005 Analysis Services cube: "Record ID is not valid"
925566 (http://support.microsoft.com/kb/925566/ )
FIX: Error message when you run a query against a cube that contains linked objects in SQL Server 2005 Analysis Services: "Internal error An unexpected error occurred"
925744 (http://support.microsoft.com/kb/925744/ )
FIX: Error message when you try to use a SQL Server authenticated login to log on to an instance of SQL Server 2005: "Logon error: 18456"
925754 (http://support.microsoft.com/kb/925754/ )
FIX: You may experience stack corruption and SQL Server 2005 Analysis Services may stop responding when you run SQL Server 2005 Analysis Services on a Dual-Core Intel Itanium 2 Processor 9000 Series-based computer
925843 (http://support.microsoft.com/kb/925843/ )
FIX: Error message when you use the SQL Server Native Client provider to run queries between two linked servers in SQL Server 2005 Service Pack 1: "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'"
926106 (http://support.microsoft.com/kb/926106/ )
FIX: You receive an error message when you use the Print Preview option on a large report in SQL Server 2005 Reporting Services
926235 (http://support.microsoft.com/kb/926235/ )
FIX: In SQL Server 2005 Analysis Services, the query may not return all the expected rows when you use the TopCount function in an MDX query
926240 (http://support.microsoft.com/kb/926240/ )
FIX: SQL Server 2005 may stop responding when you use the SqlBulkCopy class to import data from another data source
926285 (http://support.microsoft.com/kb/926285/ )
Fix: Error message when you convert a column from the varbinary(max) data type to the XML data type in SQL Server 2005: "Msg 6322, Level 16, State 1, Line 2 Too many attributes or namespace definitions"
926335 (http://support.microsoft.com/kb/926335/ )
FIX: Error message when you trace the Audit Database Management event and you try to bring a database online in SQL Server 2005: “Msg 942, Level 14, State 4, Line 1”
926422 (http://support.microsoft.com/kb/926422/ )
FIX: You receive error messages when you use SQL Server Management Studio or SQL Server Business Intelligence Development Studio after you install Microsoft Office Excel 2007 on a computer that has SQL Server 2005 Analysis Services installed
926493 (http://support.microsoft.com/kb/926493/ )
FIX: Error message when you restore a transaction-log backup that is generated in SQL Server 2000 SP4 to an instance of SQL Server 2005: "Msg 3456, Level 16, State 1, Line 1. Could not redo log record"
926515 (http://support.microsoft.com/kb/926515/ )
FIX: You may receive unexpected results when you run an MDX query in SQL Server 2005 Analysis Services
926611 (http://support.microsoft.com/kb/926611/ )
FIX: SQL Server 2005 may not send a message notification that is based on the specific string in the forwarded event when a computer that is running SQL Server 2000 forwards an event to a computer that is running SQL Server 2005
926612 (http://support.microsoft.com/kb/926612/ )
FIX: SQL Server Agent does not send an alert quickly or does not send an alert when you use an alert of the SQL Server event alert type in SQL Server 2005
926613 (http://support.microsoft.com/kb/926613/ )
FIX: You may receive incorrect results when you query a table that is published in a transactional replication in SQL Server 2005
926773 (http://support.microsoft.com/kb/926773/ )
FIX: Error message when you run a query that uses a fast forward-only cursor in SQL Server 2005: "Query processor could not produce a query plan because of the hints defined in this query"
926774 (http://support.microsoft.com/kb/926774/ )
FIX: You may receive incorrect results when you try to aggregate a non-additive measure on the parent-child dimension in SQL Server 2005 Analysis Services
926775 (http://support.microsoft.com/kb/926775/ )
FIX: The sum total of a column contains incorrect data when you browse or query a cube in SQL Server 2005 Analysis Services
926962 (http://support.microsoft.com/kb/926962/ )
FIX: The user can see all the data in a cube, and SQL Server 2005 Analysis Services may crash
926967 (http://support.microsoft.com/kb/926967/ )
FIX: You obtain an incorrect result when you run a Multidimensional Expressions (MDX) query in SQL Server 2005 Service Pack 1
927109 (http://support.microsoft.com/kb/927109/ )
FIX: Many query notifications are sent to the data warehouse when proactive caching is enabled in SQL Server 2005 Analysis Services
927179 (http://support.microsoft.com/kb/927179/ )
FIX: Processing is never completed and cannot be stopped when you process a cube in SQL Server 2005 Analysis Services
927289 (http://support.microsoft.com/kb/927289/ )
FIX: Updates to the SQL Server Mobile subscriber may not be reflected in the SQL Server 2005 merge publication
927290 (http://support.microsoft.com/kb/927290/ )
FIX: Error message when you open a report in Report Manager in SQL Server 2005 Reporting Services: "An error has occurred during report processing. There is already an open DataReader associated with this Command which must be closed first"
927455 (http://support.microsoft.com/kb/927455/ )
FIX: The query runs much slower than you expect when you use the InStr function in a calculated member of an MDX query in SQL Server 2005 Analysis Services
927643 (http://support.microsoft.com/kb/927643/ )
FIX: Some search results are missing when you perform a full-text search operation on a Windows SharePoint Services 2.0 site after you upgrade to SQL Server 2005
927859 (http://support.microsoft.com/kb/927859/ )
FIX: You may experience decreased performance when you run a query that contains the CREATE SET statement against a ragged dimension in SQL Server 2005 Analysis Services
928083 (http://support.microsoft.com/kb/928083/ )
FIX: You may receive an error message when you run a CLR stored procedure or CLR function that uses a context connection in SQL Server 2005
928243 (http://support.microsoft.com/kb/928243/ )
FIX: When you run a SQL Server 2005 Integration Services package, the package may stop responding
928372 (http://support.microsoft.com/kb/928372/ )
FIX: Error message when you use a synonym for a stored procedure in SQL Server 2005: "A severe error occurred on the current command"
928394 (http://support.microsoft.com/kb/928394/ )
FIX: The changes are not reflected in the publication database after you reinitialize the subscriptions in SQL Server 2005
928537 (http://support.microsoft.com/kb/928537/ )
FIX: The full-text index population for the indexed view is very slow in SQL Server 2005
928538 (http://support.microsoft.com/kb/928538/ )
FIX: The Report Server Windows service may stop responding, and you may receive the following error message in SQL Server 2005 Reporting Services: "An internal error occurred on the report server"
928539 (http://support.microsoft.com/kb/928539/ )
FIX: An access violation is logged in the SQL Server Errorlog file when you run a query that uses a plan guide in SQL Server 2005
928789 (http://support.microsoft.com/kb/928789/ )
FIX: Error message in the database mail log when you try to use the sp_send_dbmail stored procedure to send an e-mail in SQL Server 2005: "Invalid XML message format received on the ExternalMailQueue"
928792 (http://support.microsoft.com/kb/928792/ )
FIX: The output rows of the Aggregate transformation are divided into multiple rows in SQL Server 2005 Integration Services
929179 (http://support.microsoft.com/kb/929179/ )
FIX: A memory leak may occur every time that you synchronize a SQL Server Mobile subscriber in SQL Server 2005
929240 (http://support.microsoft.com/kb/929240/ )
FIX: I/O requests that are generated by the checkpoint process may cause I/O bottlenecks if the I/O subsystem is not fast enough to sustain the IO requests in SQL Server 2005
929278 (http://support.microsoft.com/kb/929278/ )
FIX: SQL Server 2005 may not perform histogram amendments when you use trace flags 2389 and 2390
930283 (http://support.microsoft.com/kb/930283/ )
FIX: You receive error 1456 when you add a witness to a database mirroring session and the database name is the same as an existing database mirroring session in SQL Server 2005
930284 (http://support.microsoft.com/kb/930284/ )
FIX: You receive error 1456 when you try to add a witness to a DBM session in SQL Server 2005
930409 (http://support.microsoft.com/kb/930409/ )
FIX: Error message when you run a Multidimensional Expressions (MDX) query that uses a calculated member in SQL Server 2005 Analysis Services: "Internal error An unexpected exception occurred"
930410 (http://support.microsoft.com/kb/930410/ )
You may unexpectedly receive a NULL value when you run a Multidimensional Expressions (MDX) query in SQL Server 2005 Analysis Services
929376 (http://support.microsoft.com/kb/929376/ )
FIX: A "17187" error message may be logged in the Errorlog file when an instance of SQL Server 2005 is under a heavy load
922852 (http://support.microsoft.com/kb/922852/ )
FIX: You may experience high CPU utilization across multiple processors when you run a SQL Server 2005 Analysis Services query against a large partition or lots of partitions
917035 (http://support.microsoft.com/kb/917035/ )
FIX: Query performance may decrease after SQL Server 2005 has been running for a while
918832 (http://support.microsoft.com/kb/918832/ )
FIX: An inefficient or incorrect SQL query is generated when you try to use SQL Server 2005 to browse a ROLAP dimension
919193 (http://support.microsoft.com/kb/919193/ )
FIX: A forward-only cursor may be implicitly converted to a keyset cursor in SQL Server 2005
921293 (http://support.microsoft.com/kb/921293/ )
FIX: The description for the Dimension field is not set in the local cube file when you use the CREATE LOCAL CUBE statement in SQL Server 2005
921295 (http://support.microsoft.com/kb/921295/ )
FIX: You may receive an incorrect result when you try to run a Multidimensional Expressions (MDX) query by using SQL Server 2005
922579 (http://support.microsoft.com/kb/922579/ )
FIX: The operation may take longer than you expect when you run a warm query to obtain information from the Microsoft Search service in SQL Server 2005
923473 (http://support.microsoft.com/kb/923473/ )
FIX: SQL Server 2005 Analysis Services may stop responding when you try to process a ROLAP dimension two times
924686 (http://support.microsoft.com/kb/924686/ )
FIX: The database mirroring session may remain in the synchronizing state and may stop responding when a database failover occurs in SQL Server 2005
926024 (http://support.microsoft.com/kb/926024/ )
FIX: The query performance is very slow when you use a fast forward-only cursor to run a query in SQL Server 2005
929404 (http://support.microsoft.com/kb/929404/ )
FIX: Error message when you perform a transaction log backup operation and another data backup operation in parallel in SQL Server 2005: "Error 3633"

-------------------------------------------------------
http://support.microsoft.com/?kbid=921896

Tuesday, November 18, 2008

SQL Server Schema Binding and Indexed Views

http://www.mssqltips.com/tip.asp?tip=1610

Temporary tables

Temporary Tables

CREATE TABLE #Yaks
(YakID int,
YakName char(30)
)

You'll notice I prefixed the table with a pound sign (#). This tells SQL Server that this table is a local temporary table. This table is only visible to this session of SQL Server. When I close this session, the table will be automatically dropped. You can treat this table just like any other table with a few exceptions. The only real major one is that you can't have foreign key constraints on a temporary table.

Temporary tables are created in tempdb

select name from tempdb..sysobjects where name like '#yak%'

Table Variables

If you are using SQL Server 2000 or higher, you can take advantage of the new TABLE variable type. These are similar to temporary tables except with more flexibility and they always stay in memory. The code above using a table variable might look like this:

DECLARE @TibetanYaks TABLE
(YakID int,YakName char(30)
)

INSERT INTO @TibetanYaks (YakID, YakName)
SELECT YakID, YakNameFROM dbo.Yaks WHERE YakType = 'Tibetan'

Table variables don't need to be dropped when you are done with them.

Which to Use
- If you have less than 100 rows generally use a table variable. Otherwise use a temporary table. This is because SQL Server won't create statistics on table variables.

- If you need to create indexes on it then you must use a temporary table.

- When using temporary tables always create them and create any indexes and then use them. This will help reduce recompilations. The impact of this is reduced starting in SQL Server 2005 but it's still a good idea.

----------------------------------------------------------------------------------
http://www.sqlteam.com/article/temporary-tables

Monday, November 17, 2008

Designing INSTEAD OF Triggers

CREATE TABLE Person
(
SSN char(11) PRIMARY KEY,
Name nvarchar(100),
Address nvarchar(100),
Birthdate datetime
)
CREATE TABLE EmployeeTable
(
EmployeeID int PRIMARY KEY,
SSN char(11) UNIQUE,
Department nvarchar(10),
Salary money,
CONSTRAINT FKEmpPer FOREIGN KEY (SSN)
REFERENCES Person (SSN)
)

---------------------------------------------------

CREATE VIEW Employee AS
SELECT P.SSN as SSN, Name, Address,
Birthdate, EmployeeID, Department, Salary
FROM Person P, EmployeeTable E
WHERE P.SSN = E.SSN

---------------------------------------------------

CREATE TABLE PersonDuplicates
(
SSN char(11),
Name nvarchar(100),
Address nvarchar(100),
Birthdate datetime,
InsertSNAME nchar(100),
WhenInserted datetime
)

----------------------------------------------------

CREATE TRIGGER IO_Trig_INS_Employee ON Employee
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
-- Check for duplicate Person. If there is no duplicate, do an insert.
IF (NOT EXISTS (SELECT P.SSN
FROM Person P, inserted I
WHERE P.SSN = I.SSN))
INSERT INTO Person
SELECT SSN,Name,Address,Birthdate
FROM inserted
ELSE
-- Log an attempt to insert duplicate Person row in PersonDuplicates table.
INSERT INTO PersonDuplicates
SELECT SSN,Name,Address,Birthdate,SUSER_SNAME(),GETDATE()
FROM inserted
-- Check for duplicate Employee. If no there is duplicate, do an INSERT.
IF (NOT EXISTS (SELECT E.SSN
FROM EmployeeTable E, inserted
WHERE E.SSN = inserted.SSN))
INSERT INTO EmployeeTable
SELECT EmployeeID,SSN, Department, Salary
FROM inserted
ELSE
--If there is a duplicate, change to UPDATE so that there will not
--be a duplicate key violation error.
UPDATE EmployeeTable
SET EmployeeID = I.EmployeeID,
Department = I.Department,
Salary = I.Salary
FROM EmployeeTable E, inserted I
WHERE E.SSN = I.SSN
END

Sunday, November 16, 2008

Considerations for All Types of Replication

http://msdn.microsoft.com/en-us/library/ms152479(SQL.90).aspx

NOT FOR REPLICATION - SQL 2005

In some cases, it is desirable for user activity in a replication topology to be treated differently from agent activity. For example, if a row is inserted by a user at the Publisher and that insert satisfies a check constraint on the table, it might not be required to enforce the same constraint when the row is inserted by a replication agent at the Subscriber. The NOT FOR REPLICATION option allows you to specify that the following database objects are treated differently when a replication agent performs an operation:

Foreign key constraints
The foreign key constraint is not enforced when a replication agent performs an insert, update, or delete operation.

Check constraints
The check constraint is not enforced when a replication agent performs an insert, update, or delete operation.

Identity columns
The identity column value is not incremented when a replication agent performs an insert operation.

Triggers
The trigger is not executed when a replication agent performs an insert, update, or delete operation.

---------------------------------------------------------------
http://msdn.microsoft.com/en-us/library/ms152529(SQL.90).aspx

hints about SQL server 2005 from pdfslibrary.com

create SNAPSHOT database

CREATE DATABASE andrew_test_snapshot_250206_1800 ON ( NAME = N'andrew_test_datafile', FILENAME = N'E:\DB_Snapshots\andrew_ test_snapshot_2502061800.snap' ) AS SNAPSHOT OF andrew_test;

------------------------------------------------------

http://www.simple-talk.com/sql/database-administration/sql-server-2005-snapshots/

Wednesday, November 12, 2008

using xml (SQL 2005)

-- RAW mode

USE AdventureWorks
SELECT ProductID, Name, ListPrice
FROM Production.Product
FOR XML RAW
-- ELEMENTS with RAW mode
USE AdventureWorks
SELECT ProductID, Name, ListPrice
FROM Production.Product
FOR XML RAW, ELEMENTS

-- Named element in RAW mode

SELECT ProductID, Name, ListPrice
FROM Production.Product
FOR XML RAW('Product')
-- root element
SELECT ProductID, Name, ListPrice
FROM Production.Product
FOR XML RAW, ROOT('Products')

-- Join in RAW mode

SELECT Category.Name CategoryName, Product.ProductID, Product.Name
FROM Production.ProductSubCategory Category
JOIN Production.Product Product
ON Product.ProductSubCategoryID = Category.ProductSubCategoryID
ORDER BY Category.Name, Product.ProductID
FOR XML RAW

-- AUTO Mode

SELECT ProductID, Name, ListPrice
FROM Production.Product Product
FOR XML AUTO

-- Join in AUTO mode

SELECT Category.Name CategoryName, Product.ProductID, Product.Name
FROM Production.ProductSubCategory Category
JOIN Production.Product Product
ON Product.ProductSubCategoryID = Category.ProductSubCategoryID
ORDER BY Category.Name, Product.ProductID
FOR XML AUTO

-- Nested XML with TYPE

SELECT ProductID,
Name, ListPrice,
(SELECT ReviewerName, Comments
FROM Production.ProductReview ProductReview
WHERE ProductReview.ProductID = Product.ProductID
FOR XML AUTO, ELEMENTS, TYPE)
FROM Production.Product Product
WHERE ProductID > 795
FOR XML AUTO

-- EXPLICIT Mode

SELECT 1 AS TAG,
NULL AS Parent,
ProductID AS [Product!1!ProductID],
Name AS [Product!1]
FROM Production.Product
FOR XML EXPLICIT

-- Nesting in EXPLICIT Mode

SELECT 1 AS TAG,
NULL AS Parent,
ProductID AS [Product!1!ProductID],
Name AS [Product!1!ProductName!Element],
NULL AS [Review!2!Reviewer],
NULL AS [Review!2]
FROM Production.Product
WHERE ProductID > 795
UNION ALL
SELECT 2,
1,
p.ProductID,
NULL,
r.ReviewerName AS [Review!2!Reviewer],
r.Comments AS [Review!2]
FROM Production.ProductReview r
JOIN Production.Product p
ON p.ProductID = r.ProductID
WHERE p.ProductID > 795
ORDER BY [Product!1!ProductID]
FOR XML EXPLICIT

-- PATH mode

SELECT ProductID AS "@ProductID",
Name AS "*",
Size AS "Description/@Size",
Color AS "Description/text()"
FROM Production.Product
ORDER BY Name
FOR XML PATH('Product')

Tuesday, November 11, 2008

User-Defined Table Types (SQL 2008)

In SQL Server 2008, a user-defined table type is a user-defined type that represents the definition of a table structure. You can use a user-defined table type to declare table-valued parameters for stored procedures or functions, or to declare table variables that you want to use in a batch or in the body of a stored procedure or function.

To create a user-defined table type, use the CREATE TYPE statement. To ensure that the data in a user-defined table type meets specific requirements, you can create unique constraints and primary keys on the user-defined table type.

Restrictions
User-defined table types have the following restrictions:

- A user-defined table type cannot be used as a column in a table or a field in a structured user-defined type.

- Alias types based on a user-defined table type The [NOT FOR REPLICATION] option is not allowed.

- CHECK constraints require a computed column to be persisted.

- The primary key on computed columns must be PERSISTED and NOT NULL.

- A nonclustered index cannot be created on a user-defined table type unless the index is the result of creating a PRIMARY KEY or UNIQUE constraint on the user-defined table type. (SQL Server enforces any UNIQUE or PRIMARY KEY constraint by using an index.)

- A DEFAULT value cannot be specified in the definition of a user-defined table type.

- The user-defined table type definition cannot be modified after it is created.

- User-defined functions cannot be called within the definition of computed columns of a user-defined table type.

Creating a user-defined table type
USE AdventureWorks;GO
/* Create a user-defined table type */
CREATE TYPE LocationTableType AS TABLE
(
LocationName VARCHAR(50), CostRate INT
);GO

Creating a User-Defined Type (SQL 2005)

To create a user-defined type (UDT) capable of being installed in Microsoft SQL Server 2005, you must first create a class in one of the supported Microsoft .NET Framework programming languages, such as Microsoft Visual C# or Microsoft Visual Basic, which conforms to the specifications for creating UDTs. The class can then be compiled as a dynamic-link library (DLL), which can be loaded in SQL Server 2005. You can also create and deploy UDTs using Microsoft Visual Studio. The ability to execute common language runtime (CLR) code is set to OFF by default in SQL Server 2005. The CLR can be enabled by using the sp_configure system stored procedure, as shown in the following Transact-SQL statements:

sp_configure 'clr enabled', 1
Reconfigure

The following code listing defines the Point UDT

using System;
using System.Data;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text;
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native,
IsByteOrdered=true, ValidationMethodName = "ValidatePoint")]
public struct Point : INullable
{
private bool is_Null;
private Int32 _x;
private Int32 _y;
public bool IsNull
{
get
{
return (is_Null);
}
}
public static Point Null
{
get
{
Point pt = new Point();
pt.is_Null = true;
return pt;
}
}
// Use StringBuilder to provide string representation of UDT.
public override string ToString()
{
// Since InvokeIfReceiverIsNull defaults to 'true'
// this test is unneccesary if Point is only being called
// from SQL.
if (this.IsNull)
return "NULL";
else
{
StringBuilder builder = new StringBuilder();
builder.Append(_x);
builder.Append(",");
builder.Append(_y);
return builder.ToString();
}
}
[SqlMethod(OnNullCall = false)]
public static Point Parse(SqlString s)
{
// With OnNullCall=false, this check is unnecessary if
// Point only called from SQL.
if (s.IsNull)
return Null;
// Parse input string to separate out points.
Point pt = new Point();
string[] xy = s.Value.Split(",".ToCharArray());
pt.X = Int32.Parse(xy[0]);
pt.Y = Int32.Parse(xy[1]);
// Call ValidatePoint to enforce validation
// for string conversions.
if (!pt.ValidatePoint())
throw new ArgumentException("Invalid XY coordinate values.");
return pt;
}
// X and Y coordinates exposed as properties.
public Int32 X
{
get
{
return this._x;
}
// Call ValidatePoint to ensure valid range of Point values.
set
{
Int32 temp = _x;
_x = value;
if (!ValidatePoint())
{
_x = temp;
throw new ArgumentException("Invalid X coordinate value.");
}
}
}
public Int32 Y
{
get
{
return this._y;
}
set
{
Int32 temp = _y;
_y = value;
if (!ValidatePoint())
{
_y = temp;
throw new ArgumentException("Invalid Y coordinate value.");
}
}
}
// Validation method to enforce valid X and Y values.
private bool ValidatePoint()
{
// Allow only zero or positive integers for X and Y coordinates.
if ((_x >= 0) && (_y >= 0))
{
return true;
}
else
{
return false;
}
}
// Distance from 0 to Point method.
[SqlMethod(OnNullCall = false)]
public Double Distance()
{
return DistanceFromXY(0, 0);
}
// Distance from Point to the specified point method.
[SqlMethod(OnNullCall = false)]
public Double DistanceFrom(Point pFrom)
{
return DistanceFromXY(pFrom.X, pFrom.Y);
}
// Distance from Point to the specified x and y values method.
[SqlMethod(OnNullCall = false)]
public Double DistanceFromXY(Int32 iX, Int32 iY)
{
return Math.Sqrt(Math.Pow(iX - _x, 2.0) + Math.Pow(iY - _y, 2.0));
}
}

CREATE RULE (SQL 2005)

Creates an object called a rule. When bound to a column or an alias data type, a rule specifies the acceptable values that can be inserted into that column.

1-Creating a rule with a range

The following example creates a rule that restricts the range of integers inserted into the column or columns to which this rule is bound.

CREATE RULE range_rule
AS
@range>= $1000 AND @range <$20000;

----------------------------------------------

2- Creating a rule with a list

The following example creates a rule that restricts the actual values entered into the column or columns (to which this rule is bound) to only those listed in the rule.

CREATE RULE list_rule
AS
@list IN ('1389', '0736', '0877');

----------------------------------------------

3- Creating a rule with a pattern

The following example creates a rule to follow a pattern of any two characters followed by a hyphen (-), any number of characters or no characters, and ending with an integer from 0 through 9.

CREATE RULE pattern_rule
AS
@value LIKE '__-%[0-9]'

Sunday, November 9, 2008

SET ANSI_NULLS (SQL 2005)

-- Create table t1 and insert values.

CREATE TABLE t1 (a INT NULL)
INSERT INTO t1 values (NULL)
INSERT INTO t1 values (0)
INSERT INTO t1 values (1)
GO

-- Print message and perform SELECT statements.

PRINT 'Testing default setting'
DECLARE @varname int
SELECT @varname = NULL
SELECT *
FROM t1
WHERE a = @varname
SELECT *
FROM t1
WHERE a <> @varname
SELECT *
FROM t1
WHERE a IS NULL
GO

-- SET ANSI_NULLS to ON and test.

PRINT 'Testing ANSI_NULLS ON'
SET ANSI_NULLS ON
GO
DECLARE @varname int
SELECT @varname = NULL
SELECT *
FROM t1
WHERE a = @varname
SELECT *
FROM t1
WHERE a <> @varname
SELECT *
FROM t1
WHERE a IS NULL
GO

-- SET ANSI_NULLS to OFF and test.

PRINT 'Testing SET ANSI_NULLS OFF'
SET ANSI_NULLS OFF
GO
DECLARE @varname int
SELECT @varname = NULL
SELECT *
FROM t1
WHERE a = @varname
SELECT *
FROM t1
WHERE a <> @varname
SELECT *
FROM t1
WHERE a IS NULL
GO

-- Drop table t1.

DROP TABLE t1

Tuesday, October 28, 2008

create table

--Complete table definitions

--This example shows complete table definitions with all constraint definitions for three tables (jobs, employee, and publishers) created in the pubs database.

/* ************************** jobs table ************************** */
CREATE TABLE jobs
(
job_id smallint
IDENTITY(1,1)
PRIMARY KEY CLUSTERED,
job_desc varchar(50) NOT NULL
DEFAULT 'New Position - title not formalized yet',
min_lvl tinyint NOT NULL
CHECK (min_lvl >= 10),
max_lvl tinyint NOT NULL
CHECK (max_lvl <= 250)
)

/* ************************* employee table ************************* */
CREATE TABLE employee
(
emp_id int
CONSTRAINT PK_emp_id PRIMARY KEY NONCLUSTERED
CONSTRAINT CK_emp_id CHECK (emp_id LIKE
'[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]' or
emp_id LIKE '[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]'),
/* Each employee ID consists of three characters that
represent the employee's initials, followed by a five
digit number ranging from 10000 through 99999 and then the
employee's gender (M or F). A (hyphen) - is acceptable
for the middle initial. */
fname varchar(20) NOT NULL,
minit char(1) NULL,
lname varchar(30) NOT NULL,
job_id smallint NOT NULL
DEFAULT 1
/* Entry job_id for new hires. */
REFERENCES jobs(job_id),
job_lvl tinyint
DEFAULT 10,
/* Entry job_lvl for new hires. */
pub_id char(4) NOT NULL
DEFAULT ('9952')
REFERENCES publishers(pub_id),
/* By default, the Parent Company Publisher is the company
to whom each employee reports. */
hire_date datetime NOT NULL
DEFAULT (getdate())
/* By default, the current system date is entered. */
)

/* ***************** publishers table ******************** */
CREATE TABLE publishers
(
pub_id char(4) NOT NULL
CONSTRAINT UPKCL_pubind PRIMARY KEY CLUSTERED
CHECK (pub_id IN ('1389', '0736', '0877', '1622', '1756')
OR pub_id LIKE '99[0-9][0-9]'),
pub_name varchar(40) NULL,
city varchar(20) NULL,
state char(2) NULL,
country varchar(30) NULL
DEFAULT('USA')
)

Creating a database

Creating a database without specifying files

CREATE DATABASE mytest

Creating a database that specifies the data and transaction log files

CREATE DATABASE Sales
ON
( NAME = Sales_dat,
FILENAME = '''+ @data_path + 'saledat.mdf'',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = Sales_log,
FILENAME = '''+ @data_path + 'salelog.ldf'',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )'
);
GO

Creating a database by specifying multiple data and transaction log files

'CREATE DATABASE Archive
ON
PRIMARY
(NAME = Arch1,
FILENAME = '''+ @data_path + 'archdat1.mdf'',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20),
( NAME = Arch2,
FILENAME = '''+ @data_path + 'archdat2.ndf'',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20),
( NAME = Arch3,
FILENAME = '''+ @data_path + 'archdat3.ndf'',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20)
LOG ON
(NAME = Archlog1,
FILENAME = '''+ @data_path + 'archlog1.ldf'',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20),
(NAME = Archlog2,
FILENAME = '''+ @data_path + 'archlog2.ldf'',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20)'
);
GO

Creating a database that has filegroups

CREATE DATABASE Sales
ON PRIMARY
( NAME = SPri1_dat,
FILENAME = '''+ @data_path + 'SPri1dat.mdf'',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 15% ),
( NAME = SPri2_dat,
FILENAME = '''+ @data_path + 'SPri2dt.ndf'',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 15% ),
FILEGROUP SalesGroup1
( NAME = SGrp1Fi1_dat,
FILENAME = '''+ @data_path + 'SG1Fi1dt.ndf'',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 ),
( NAME = SGrp1Fi2_dat,
FILENAME = '''+ @data_path + 'SG1Fi2dt.ndf'',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 ),
FILEGROUP SalesGroup2
( NAME = SGrp2Fi1_dat,
FILENAME = '''+ @data_path + 'SG2Fi1dt.ndf'',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 ),
( NAME = SGrp2Fi2_dat,
FILENAME = '''+ @data_path + 'SG2Fi2dt.ndf'',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = Sales_log,
FILENAME = '''+ @data_path + 'salelog.ldf'',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )'
);
GO

Attaching a database

'CREATE DATABASE Archive
ON (FILENAME = '''+ @data_path + 'archdat1.mdf'')
FOR ATTACH');
GO

create function

create function myfuns(@x int) returns char(10)
as
begin
declare @xx char(10)
select @xx = nam from Table_1 where id= @x
return @xx
end

create stored procedure

create proc myproc
@id int,
@nam char(10)
as
insert into Table_1 values(@id,@nam)

Monday, September 15, 2008

Create sample Database with data

Northwind
----------
http://www.4shared.com/file/71580360/c9e94375/instnwnd.html
Pubs
-----
http://www.4shared.com/file/71579808/62655b7b/instpubs.html

Cursors

SQL Server is very good at handling sets of data. For example, you can use a single UPDATE statement to update many rows of data. There are times when you want to loop through a series of rows a perform processing for each row. In this case you can use a cursor.

Please note that cursors are the SLOWEST way to access data inside SQL Server. The should only be used when you truly need to access one row at a time. The only reason I can think of for that is to call a stored procedure on each row.

The basic syntax of a cursor is:
--------------------------------

----------------------------------------------------------------------------------
DECLARE @AuthorID char(11) DECLARE c1 CURSOR READ_ONLYFORSELECT au_idFROM authorsOPEN c1FETCH NEXT FROM c1INTO @AuthorIDWHILE @@FETCH_STATUS = 0BEGIN PRINT @AuthorID FETCH NEXT FROM c1 INTO @AuthorIDENDCLOSE c1DEALLOCATE c1
-----------------------------------------------------------------------------------

The DECLARE CURSOR statement defines the SELECT statement that forms the basis of the cursor. You can do just about anything here that you can do in a SELECT statement. The OPEN statement statement executes the SELECT statement and populates the result set. The FETCH statement returns a row from the result set into the variable. You can select multiple columns and return them into multiple variables. The variable @@FETCH_STATUS is used to determine if there are any more rows. It will contain 0 as long as there are more rows. We use a WHILE loop to move through each row of the result set.

The READ_ONLY clause is important in the code sample above. That dramatically improves the performance of the cursor.

In this example, I just print the contents of the variable. You can execute any type of statement you wish here. In a recent script I wrote I used a cursor to move through the rows in a table and call a stored procedure for each row passing it the primary key. Given that cursors are not very fast and calling a stored procedure for each row in a table is also very slow, my script was a resource hog. However, the stored procedure I was calling was written by the software vendor and was a very easy solution to my problem. In this case, I might have something like this:

EXEC spUpdateAuthor (@AuthorID)

instead of my Print statement. The CLOSE statement releases the row set and the DEALLOCATE statement releases the resources associated with a cursor.

If you are going to update the rows as you go through them, you can use the UPDATE clause when you declare a cursor. You'll also have to remove the READ_ONLY clause from above.

----------------------------------------------------------------------------
DECLARE c1 CURSOR FORSELECT au_id, au_lnameFROM authorsFOR UPDATE OF au_lname
----------------------------------------------------------------------------

You can code your UPDATE statement to update the current row in the cursor like this

---------------------------------
UPDATE authors
SET au_lname = UPPER(Smith)
WHERE CURRENT OF c1
----------------------------------

-------------------------------------------------------------------------
http://www.sqlteam.com/article/cursors-an-overview

Monday, September 8, 2008

what is Surrogate Keys ?

the following articale from this link
http://decipherinfosys.wordpress.com/2007/02/01/surrogate-keys-vs-natural-keys-for-primary-key/

Surrogate Keys vs Natural Keys for Primary Key?

This topic probably is one of those that you cannot get any two database developers/DBAs to agree upon. Everyone has their own opinion about this and it is also one of the most discussed topics over the web when it comes to data modeling. Rather than taking any side :-), we are just listing out our experiences when it comes to chosing between a surrogate key vs the natural keys for the tables.0

Surrogate Key:

Surrogate keys are keys that have no “business” meaning and are solely used to identify a record in the table. Such keys are either database generated (example: Identity in SQL Server, Sequence in Oracle, Sequence/Identity in DB2 UDB etc.) or system generated values (like generated via a table in the schema).

Natural Key:

Keys are natural if the attribute it represents is used for identification independently of the database schema. What this basically means is that the keys are natural if people use them example: Invoice-Numbers, Tax-Ids, SSN etc.

Design considerations for choosing the Primary Key:
Primary Key should meet the following requirements:

- It should be not null, Unique and should apply to all rows.

- It should be minimal (i.e. less number of columns in the PK: ideally it should be 1, if using composite keys, then make sure that those are surrogates and using integer family data-types).

- It should be stable over a period of time (should not change i.e. update to the PK columns should not happen).

Keeping these in mind, here are the pros and cons of Surrogate vs. Natural keys:

Surrogate Key

I prefer surrogate keys to be DB controlled rather than being controlled via a next-up table in the schema since that is a more scalable approach.

Pros:

- Business Logic is not in the keys.

- Small 4-byte key (the surrogate key will most likely be an integer and SQL Server for example requires only 4 bytes to store it, if a bigint, then 8 bytes).

- Joins are very fast.

- No locking contentions because of unique constraint (this refers to the waits that get developed when two sessions are trying to insert the same unique business key) as the surrogates get generated by the DB and are cached - very scalable.

Cons:

- An additional index is needed. In SQL Server, the PK constraint will always creates a unique index, in Oracle, if an index already exists, PK creation will use that index for uniqueness enforcement (not a con in Oracle).

- Cannot be used as a search key.

- If it is database controlled, for products that support multiple databases, different implementations are needed, example: identity in SS2k, before triggers and sequences in Oracle, identity/sequence in DB2 UDB.

- Always requires a join when browsing the child table(s).

Natural Key

Pros:

- No additional Index.

- Can be used as a search key.

Cons:

- If not chosen wisely (business meaning in the key(s)), then over a period of time additions may be required to the PK and modifications to the PK can occur.

- If using strings, joins are a bit slower as compared to the int data-type joins, storage is more as well. Since storage is more, less data-values get stored per index page. Also, reading strings is a two step process in some RDBMS: one to get the actual length of the string and second to actually perform the read operation to get the value.

- Locking contentions can arise if using application driven generation mechanism for the key.

- Can’t enter a record until value is known since the value has some meaning.

Choosing Surrogate vs. Natural Keys:

There is no rule of thumb in this case. It has to be evaluated table by table:

- If we can identify an appropriate natural key that meets the three criteria for it to be a PK column, we should use it. Look-up tables and configuration tables are typically ok.

- Data-Type for the PK: the smaller the better, choose an integer or a short-character data type. It also ensures that the joins will be faster. This becomes even more important if you are going to make the PK as a clustered index since non-clustered indexes are built off the clustered index. RDBMS processes integer data values faster than the character data values because it converts characters to ASCII equivalent values before processing, which is an extra step.

Some notes and pics about install SQL Sever 2008

1- setup needs to upgrade windows installer in the current windows with i nstall DotNetFramework 3.5 with SP1

2- The Installer will install some hot fix for windows when it's needed

3- Hardware and Software Requirements for Installing SQL Server 2008 http://msdn.microsoft.com/en-us/library/ms143506.aspx

4-Security Considerations for a SQL Server Installation
http://msdn.microsoft.com/en-us/library/ms144228.aspx

5-Microsoft SQL Server 2008 Release Notes http://download.microsoft.com/download/4/9/e/49eeb41a-a769-4520-80d6-671b8ae2bd06/SQLServer2008ReleaseNotes.htm
6-System configuration checker : contain the following elements :

-Minimum operating system version
-Setup administrator
-Restart computer
-Windows managment Instrumentation(WMI) service
-Comnsistancy validation for SQL Server registry keys
-Long path names to files on SQL Server installation media
-Unsupported Sql server products
-Performance counter registry hive consistency
-Previous releases of Sql server 2008 Business Intelligence Development Studio
-Previous CTP installation
-Computer domain controller
-Microsoft .NET Application Security
-Edition WOW64 platform
-Windows PowerShell

7- Getting Started with SQL Server 2008 Failover Clustering
http://msdn.microsoft.com/en-us/library/ms189134.aspx

8- Step by step for installing SQL Server 2008