Database With Fun (While 1 = 1)

Include SET NOCOUNT ON statement: With every SELECT and DML statement, the SQL server returns a message that indicates the number of affected rows by that statement. This information is mostly helpful in debugging the code, but it is useless after that. By setting SET NOCOUNT ON, we can disable the feature of returning this extra information. For stored procedures that contain several statements or contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost because network traffic is greatly reduced. CREATE PROC dbo.ProcName AS SET NOCOUNT ON ; --Procedure code here SELECT column1 FROM dbo.TblTable1 -- Reset SET NOCOUNT to OFF SET NOCOUNT OFF ; GO Use schema name with object name: The object name is qualified if used with schema name. Schema name should be used with the stored procedure name and with all objects referenced inside the stored procedure. This help in directly finding the complied plan...

If we will follow following tips or rules while write sql queries we can improve the performance of sql queries: 1. Help Query optimizer to estimate correct statistics In sql server execution plan is decided by: 1. Cardinality: The total number of rows processed at each level of query. 2. Query Plan: Algorithm to use to perform task like searching, sorting etc. When we execute a sql queries, create index, insert records in a table etc sql server automatically creates or updates the statistics of key fields of tables. In simple words we can say statistics is object which keeps information about total numbers of distinct records in a table. There a few cases where query optimizer is unable to get correct information about statistics which decrease the performance of a sql query. For examples: First we are creating a tblMessage table and inserting 97526 records into it and creating three indexes on it: CREATE TABLE tblMessage (     ntMessageID BIGINT IDENTITY PRIMAR...

Use JOINs rather than subqueries If possible (and if it makes sense), I suggest using JOIN statements rather than subqueries to improve performance. When a subquery is used as criteria in a SELECT statement, the values returned from the subquery are distinct. Returning a distinct list of values requires additional processing, which can slow down your queries. Use explicit transactions When data manipulation occurs in the database, the actions are written to the transaction log. If your statements are executing many DML statements, it might be a good idea to place them inside of a transaction for performance purposes. Placing the statements inside of a transaction will prevent all of the statements from being written to the transaction log serially. Use UNION ALL instead of UNION When you use the UNION clause to concatenate the results from two or more SELECT statements, duplicate records are removed. This duplicate removal requires additional computing to accomplish. If you are no...

There are various types of backups in SQL Server: Full backup : A full database backup provides a complete copy of the database and provides a single point-in-time to which the database can be restored. Transaction Log Backup: Transaction log backups are only possible in the FULL or BULK_LOGGED recovery models. A transaction log backup contains all the transaction log records generated since the last log backup and is used to allow the database to be recovered to a specific point in time. Differential Backup: A differential backup performs the same operations as a full backup, but only contains all the data that has changed or been added since the previous full backup. Copy Only Backup: A copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups. A copy-only log backup preserves the existing log archive point and, therefore, does not affect the sequencing of regular log backups. Myth 1: A full backup only contains data...

Consider a scenario- where an upgrade from SQL 2005 to SQL 2008 fails with the following messages while installing Setup Support Files. ******************************************************** TITLE: SQL Server Setup failure. ------------------------------ SQL Server Setup has encountered the following error: MsiGetProductInfo  failed to retrieve ProductVersion for package with Product Code = ' {0826F9E4-787E-481D-83E0-BC6A57B056D5} '. Error code: 1605.. Solution- Get the Product Code = ' {0826F9E4-787E-481D-83E0-BC6A57B056D5} ' from the error message that you are getting. Byte reverse the first part from   0826F9E4   to   4E9F6280. Search in the registry and try to find a match for " 4E9F6280 " Take a backup of the registry and delete the parent key. There can be a lot of search results in this case, but need not worry.  Just delete all the parent registries of this search result which are having only this key v...

To trace T_SQL queries submitted to SQL Express: Start the SQL Express Service from the command prompt (run command prompt as Administrator if you are working on Windows 7 or Vista) with the following Trace Flag: net start MSSQL$SQLEXPRESS /T4032 This now causes all queries issued by all conections to be traced. In order to have the trace output sent to the error log, enable trace flag 3605. Execute the following in SQL Server Management Studio: dbcc traceon(3605, -1) -- The -1 makes this global, i.e. applies to all sessions / connections The logs can be found in  C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Log\ERRORLOG . The logs will now contain the queries that were issued by the clients. To install sql profiler with an express edition- Here are the steps for these installations: Developer or Standard Edition of SQL Server 2008 R2: In installation directory, run the following from an admin command prompt: setup.exe ...

Features of this Tool. 1. In-depth analysis of SQL Server instance from all the performance parameters perspectives. 2. It does not required any database to stored historical performance data. 3. It can be run on SQL Server 2005/2008/2008R2 versions are supported. 4. Output of Performance Baseline report tool is in HTML format which can be very easy to read and interpret. Pre-Requisites of this tool:- 1. It requires XP_cmdshell command to be enable to generate reports on the file system. 2.It create two stored procedure named as following [ InstanceAnalysis_PerformanceBaseLine ] (Collects Performance Related data for specific instance) [SP_InstanceBaselinePerfReport](It generate HTML report based on the data collected by above SP) 3. It will create following job [ DBA_PerfBaseline_Report_Job ] under the SQL Server agent to run this performance baseline report on demand as well on mention schedule. 4. We may need to give proper Write/Full permis...