Pro Programmer

SQL SERVER REPORTING SERVICES: Create report without wizard; Manually

2012-10-31T13:06:00.000-07:00

Intro to SSRS: How to Create a Basic Report

2012-10-14T23:46:00.002-07:00

Subscribe--> http://www.youtube.com/channel/UC_PSpWm9sw6YbcDXQDmLB2A?feature=mhee

Performance Tuning of Stored Procedures

2012-09-06T08:16:00.000-07:00

http://blog.sqlauthority.com/2010/02/16/sql-server-stored-procedure-optimization-tips-best-practices/

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.

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 instead of searching the objects in other possible schema before finally deciding to use a cached plan, if available. This process of searching and deciding a schema for an object leads to COMPILE lock on stored procedure and decreases the stored procedure’s performance.

Try to avoid using SQL Server cursors whenever possible: Cursor uses a lot of resources for overhead processing to maintain current record position in a recordset and this decreases the performance. If we need to process records one-by-one in a loop, then we should use the WHILE clause. Wherever possible, we should replace the cursor-based approach with SET-based approach. Because the SQL Server engine is designed and optimized to perform SET-based operation very fast.

Keep the Transaction as short as possible: The length of transaction affects blocking and deadlocking. In some cases, blocking also converts into deadlocks. So, for faster execution and less blocking, the transaction should be kept as short as possible.

Use TRY-Catch for error handling

Do not use the prefix “sp_” in the stored procedure name: If a stored procedure name begins with “SP_,” then SQL server first searches in the master database and then in the current session database. Searching in the master database causes extra overhead and even a wrong result if another stored procedure with the same name is found in master database.

Use IF EXISTS (SELECT 1) instead of (SELECT *): To check the existence of a record in another table, we uses the IF EXISTS clause. The IF EXISTS clause returns True if any value is returned from an internal statement, either a single value “1” or all columns of a record or complete recordset. The output of the internal statement is not used. Hence, to minimize the data for processing and network transferring, we should use “1” in the SELECT clause of an internal statement, as shown below:

IF EXISTS (SELECT 1 FROM sysobjectsWHERE name = 'MyTable' AND type = 'U')

Use the sp_executesql stored procedure instead of the EXECUTE statement.
The sp_executesql stored procedure supports parameters. So, using the sp_executesql stored procedure instead of the EXECUTE statement improve the re-usability of your code. The execution plan of a dynamic statement can be reused only if each and every character, including case, space, comments and parameter, is same for two statements.

Custom Auditing Vs Logging

2012-08-30T23:51:00.003-07:00

Logging is basically used for capturing information about package execution itself - errors encountered, execution time for the package or steps within the package, data flow buffer
details, etc. Auditing is typically information about the data - how many records inserted,
updated, and deleted; when it was done; what was the source; who ran the package; what
machine did it run on, etc.

Eliminate KEY LOOKUP

2012-08-25T16:58:00.003-07:00

Eliminate Key Lookup

2012-08-25T16:53:00.003-07:00

Nested Loop
The nested loop join works by looping through all the rows of one input and for each row looping through all the rows of the other input, looking for matches. The nested loop join works best when one or both of the input row sets is small. Since the input that is chosen as the second is read as many times as there are rows in the outer, this join can get very expensive as the size of the inputs increases.
For more detail on the nested loop, see Craig Freedman’s post
Merge Join
The merge join works by running through the two inputs, comparing rows and outputting matched rows. Both inputs must be sorted on the joining columns for this join to be possible. Since both inputs are only read once, this is an efficient join for larger row sets. This efficiency may be offset by the sorted requirement. If the join column is not indexed so as to retrieve the data already sorted, then an explicit sort is required.
For more detail on the merge join, see Craig Freedman’s post
Hash Join
The hash join is one of the more expensive join operations, as it requires the creation of a hash table to do the join. That said, it’s the join that’s best for large, unsorted inputs. It is the most memory-intensive of any of the joins
The hash join first reads one of the inputs and hashes the join column and puts the resulting hash and the column values into a hash table built up in memory. Then it reads all the rows in the second input, hashes those and checks the rows in the resulting hash bucket for the joining rows.

Copied from SQL In The Wild

Covering Index VS Composite Index

2012-08-25T16:49:00.000-07:00

Composite index: An index that contains more than one column. In both SQL Server 2005 or higher, you can include up to 16 columns in an index, as long as the index doesn’t exceed the 900 byte limit. Both clustered and nonclustered indexes can be composite indexes.

Covering index: A type of index that includes all the columns that are needed to process a particular query. For example, your query might retrieve the FirstName and LastName columns from a table, based on a value in the ContactID column. You can create a covering index that includes all three columns.

If an index has more than a column in it, it is called a composite index. There is no KEYWORD associated with it. The best performing indexes are narrow with good selectivity.

A table can have maximum 1 clustered index since the data is layed out according to this index. The leaf level of the B-TREE is the data pages. One application is range searches. The INT IDENTITY(1,1) PRIMARY KEY is usually the clustered index.
A table can have 0 or more non-clustered indexes.

An index can include non-key columns. It is called a covering index if It has all the columns needed to run certain queries.

Union, Union All, Except, and Intersect

2012-08-25T16:23:00.002-07:00

The UNION operator combines the output of two query expressions into a single result set. Query expressions are executed independently, and their output is combined into a single result table.
The purpose of the SQL UNION ALL command is also to combine the results of two queries together. The difference between UNION ALL and UNION is that, while UNIONonly selects distinct values, UNION ALLselects all values.The purpose of the SQL UNION ALL command is also to combine the results of two queries together. The difference between UNION ALL and UNION is that, while UNIONonly selects distinct values, UNION ALLselects all values.

· The EXCEPT operator evaluates the output of two query expressions and returns the difference between the results. The result set contains all rows returned from the first query expression except those rows that are also returned from the second query expression.

· The INTERSECT operator evaluates the output of two query expressions and returns only the rows common to each.

Group By (Rollup & Cube & Grouping Sets)

2012-08-25T16:11:00.001-07:00

Rollup, CUBE, and Grouping Sets are all extensions of the Group By Clause.

The GROUP BY clause can be used in a SELECT statement to collect data across multiple records and group the results by one or more columns.

ROLLUP enables a SELECT statement to calculate multiple levels of subtotals across a specified group of dimensions. It also calculates a grand total. ROLLUP is a simple extension to the GROUP BY clause, so its syntax is extremely easy to use. The ROLLUP extension is highly efficient, adding minimal overhead to a query.

CUBE enables a SELECT statement to calculate subtotals for all possible combinations of a group of dimensions. It also calculates a grand total. This is the set of information typically needed for all cross-tabular reports, so CUBE can calculate a cross-tabular report with a single SELECT statement. Like ROLLUP, CUBE is a simple extension to the GROUP BY clause, and its syntax is also easy to learn.

The GROUPING SETS operator can generate the same result set as that generated by using a simple GROUP BY, ROLLUP, or CUBE operator. When all the groupings that are generated by using a full ROLLUP or CUBE operator are not required, you can use GROUPING SETS to specify only the groupings that you want. The GROUPING SETS list can contain duplicate groupings; and, when GROUPING SETS is used with ROLLUP and CUBE, it might generate duplicate groupings. Duplicate groupings are retained as they would be by using UNION ALL.

Cross Apply VS Outer Apply

2012-08-25T15:59:00.002-07:00

The APPLY operator is required when you have to use table-valued function in the query, but it can also be used with an inline SELECT statements.

The CROSS APPLY operator returns only those rows from left table expression (in its final output) if it matches with right table expression. In other words, the right table expression returns rows for left table expression match only. Whereas the OUTER APPLY operator returns all the rows from left table expression irrespective of its match with the right table expression. For those rows for which there are no corresponding matches in right table expression, it contains NULL values in columns of right table expression. So you might now conclude, the CROSS APPLY is semantically equivalent to INNER JOIN (or to be more precise it’s like a CROSS JOIN with a correlated sub-query) with an implicit join condition of 1=1 whereas OUTER APPLY is semantically equivalent to LEFT OUTER JOIN.

Cross Apply returns only rows from the outer table that produces a result set from the table-valued function.

Outer Apply returns both rows that produce a result set and rows that do not, with NULL values in the columns produced by the table-valued function.

RaiseError

2012-08-25T15:28:00.000-07:00

RaiseError generates an error message and initiates error processing for the system.

RaisEerror can either reference defined message stored in the sys.messages catalogview or build a message dynamically. The message is returned as a server error message to the calling application or to an associated CATCH block of a TRY…ATCH construct.

Merge Statement

2012-08-25T15:22:00.001-07:00

MERGE is a new feature that provides an efficient way to perform multiple DML operations. In previous versions of SQL Server, we had to write separate statements to INSERT, UPDATE, or DELETE data based on certain conditions, but now, using MERGE statement we can include the logic of such data modifications in one statement that even checks when the data is matched then just update it and when unmatched then insert it. One of the most important advantages of MERGE statement is all the data is read and processed only once.

@@error

2012-08-25T15:09:00.003-07:00

@@Error: Returns the value of the last t-sql statement's error message, if there was an error. @@ERROR returns 0 if the statement executed successfully. You can view the text associated with an @@ERROR error number in the sysmessages system table.
@@error is cleared and reset on each statement executed, so check it immediately following the statement validated, or save it to a local variable that can be checked later.

Stored Procedure VS User-Defined Functions

2012-08-25T14:48:00.002-07:00

UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be. UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables. Inline UDF's can be thought of as views that take parameters and can be used in JOINs and other Rowset operations.

Here's the full list of Differences:

UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.
UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
Inline UDF's can be though of as views that take parameters and can be used in JOINs and other Rowset operations.
Unlike Stored Procedure DML operations, like INSERT/UPDATE/DELETE, are not allowed in UDF.
A stored procedure can have both input and output parameters whereas UDF can only have input parameters.

UDF can be directly selected from, whereas a stored procedure requires one to insert the results into a tempory table or table variabl
Stored procedures different than multi-line TVF is that the optimizer can use statistics to create a better query plan for a stored procedure; however, no statistics are maintained on Multi-line UDFs, so the optimizer has to guess at the number of rows, which can lead to a sub-optimal plan.
Stored procs can create a table but can’t return table.
Functions can create, update and delete the table variable. It can return a table
Stored Procedures can affect the state of the database by using insert, delete, update and create operations.
Functions cannot affect the state of the database which means we cannot perform insert, delete, update and create operations operations on the database.
Stored procedures are stored in database in the compiled form.
Function are parsed and conpiled at runtime only.
Stored procs can be called independently using exec keyword. Stored procedure cannot be used in the select/where/having clause.
Function are called from select/where/having clause. Even we can join two functions.
Stored procedure allows getdate () or other non-deterministic functions can be allowed.
Function won’t allow the non-deterministic functions like getdate().
In Stored procedures we can use transaction statements. We can’t use in functions.
The stored procedures can do all the DML operations like insert the new record, update the records and delete the existing records.
The function won’t allow us to do the DML operations in the database tables like in the stored procedure. It allows us to do only the select operation. It will not allow to do the DML on existing tables. But still we can do the DML operation only on the table variable inside the user defined functions.
Temporary tables (derived) can be created in stored procedures.
It is not possible in case of functions.
when sql statements encounters an error, T-SQL will ignore the error in a SPROC and proceed to the next statement in the remaining code.
In case of functions, T-SQL will stop execution of next statements.

Delete VS Truncate

2012-08-25T14:32:00.002-07:00

Truncate	Delete
TRUNCATE is a DDL command	DELETE is a DML command
TRUNCATE TABLE always locks the table and page but not each row	DELETE statement is executed using a row lock, each row in the table is locked for deletion
Cannot use Where Condition	We can specify filters in where clause
It Removes all the data	It deletes specified data if where condition exists.
TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.	Delete activates a trigger because the operation are logged individually.
Faster in performance wise, because it is minimally logged in transaction log.	Slower than truncate because, it maintain logs for every record

Drop all object’s statistics and marks like High Water Mark free extents and leave the object really empty with the first extent. zero pages are left in the table	keeps object’s statistics and all allocated space. After a DELETE statement is executed,the table can still contain empty pages.
TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction lo	The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row
If the table contains an identity column, the counter for that column is reset to the seed value that is defined for the column	DELETE retain the identity
Restrictions on using Truncate Statement 1. Are referenced by a FOREIGN KEY constraint. 2. Participate in an indexed view. 3. Are published by using transactional replication or merge replication.	Delete works at row level, thus row level constrains apply

Datepart,Datename DateAdd, DateDiff, Date

2012-08-25T14:27:00.000-07:00

DatePart- Returns an integer tat represents the specified datepart of the specified date.
DATEPART (DatePart, Date)

DateName- Returns a character string that represents the specified datepart of the specified date.
DATENAME (DatePart, Date)

DateAdd-Returns a specified date with the specified number interval added to a specified datepart of that date.
DATEADD (DatePart, number, date)

DateDiff-Returns the time between two dates.
DATEDIFF (DatePart, StartDate, EndDate)

Try...Catch

2012-08-25T12:55:00.001-07:00

Errors in tsql can be processed by using a TRY…CATCH construct which contains two parts: a try block and a catch block. When an error condition is detected in a t-sql statement that is inside a try block, control is passed to a catch block where the error can be processed.

Materialized View/Index View

2012-08-25T11:39:00.002-07:00

A View created with a unique clustered index is known as an Indexed View or Materialized View.

Unlike views, an indexed view exists on the disk like a table in which the clustered index is created. Once a Clustered Index is created you may create non-clustered indexes on the view.

Script Task Vs Script Component

2012-08-21T08:00:00.000-07:00

The Script component hosts script and enables a package to include and run custom script code.

You can use the Script component in packages for the following purposes:

Apply multiple transformations to data instead of using multiple transformations in the data flow. For example, a script can add the values in two columns and then calculate the average of the sum.
Access business rules in an existing .NET assembly. For example, a script can apply a business rule that specifies the range of values that are valid in an Income column.
Use custom formulas and functions in addition to the functions and operators that the Integration Services expression grammar provides. For example, validate credit card numbers using the LUHN formula.
Validate column data and skip records that contain invalid data. For example, a script can assess the reasonableness of a postage amount and skip records with extremely high or low amounts.

The Script component provides an easy and quick way to include custom functions in a data flow. However, if you plan to reuse the script code in multiple packages, you should consider programming a custom component instead of using the Script component. For more information, see Extending the Data Flow with Custom Components.

The Script component can be used as a source, a transformation, or a destination. This component supports one input and multiple outputs. Depending on how the component is used, it supports either an input or outputs or both. The script is invoked by every row in the input or output.

If used as a source, the Script component supports multiple outputs.
If used as a transformation, the Script component supports one input and multiple outputs.
If used as a destination, the Script component supports one input.

The Script component does not support error outputs.

You can configure the Script component in the following ways:

Select input columns to reference.
Provide the script that the component runs.
Specify whether the script is precompiled.
Provide comma-separated lists of read-only and read/write variables.
Add more outputs, and add output columns to which the script assigns values.

The Script task provides code to perform functions that are not available in the built-in tasks and transformations that SQL Server 2005 Integration Services provides. The Script task can also combine functions in one script instead of using multiple tasks and transformations. The code is custom Microsoft Visual Basic .NET code that is compiled and executed at package run time.

You can use the Script task for the following purposes:

Access data using other technologies that are not supported by built-in connection types. For example, a script can use Active Directory Service Interfaces (ADSI) to access and extract user names from Active Directory.
Create a package-specific performance counter. For example, a script can create a performance counter that is updated while a complex or poorly performing task runs.
Replace data in the source with different data in the destination. For example, a script can replace a two-digit state code in the source data with the state name in the destination data.
Validate important columns in the source data and skip records that contain invalid data to prevent them from being copied to the destination. For example, a script can assess the reasonableness of a postage amount and skip records with extremely high or low amounts

Execution Plan

2012-08-20T22:36:00.004-07:00

Execution plans can tell you how a query will be executed, or how a query was executed. They are, therefore, the DBA's primary means of troubleshooting a poorly performing query. You can use the estimated execution plan to estimate how the query will run and perform. It may not be exact however it will be close. The reason it may not be exact is because when you execute your query it might have planned to go that way of the estimated plan but might have encountered a problem along the way and it might have to take a detour and go another way or use something different. The Actual Execution Plan is returned after the query has been executed and gives you the chance to see how well or badly it performed. It could very well help you realize what you can replace and how to make your query perform better.

After Trigger and Instead Of Trigger

2012-08-20T22:23:00.000-07:00

The following is from Technet:

AFTER triggers are stored procedures that occur after a data manipulation statement has occurred in the database, such as a delete statement. DDL triggers are new to SQL Server 2005, and allow you to respond to object definition level events that occur in the database engine, such as a DROP TABLE statement. INSTEAD-OF triggers are objects that will execute instead of data manipulation statements in the database engine. For example, attaching an INSTEAD-OF INSERT trigger to a table will tell the database engine to execute that trigger instead of executing the statement that would insert values into that table.

Why use an INSTEAD-OF trigger?

INSTEAD-OF triggers are very powerful objects in SQL Server. They allow the developer to divert the database engine to do something different than what the user is trying to do. An example of this would be to add an INSTEAD-OF trigger to any table in your database that rolls back transactions on tables that you do not want modified. You must be careful when using this method because the INSTEAD-OF trigger will need to be disabled before any specified modifications can occur to this table.
Perhaps a more functional reason to use an INSTEAD-OF trigger would be to add the trigger to a view. Adding an INSTEAD-OF trigger to a view essentially allows you to create updateable views. Updateable views allow you to totally abstract your database schema so you can potentially design a system in such a way that your database developers do not have to worry about the OLTP database schema and instead rely upon a standard set of views for data modifications.

Cascading parameters

2012-08-20T01:23:00.003-07:00

Cascading parameters provide a way of managing large amounts of report data. You can define a set of related parameters so that the list of values for one parameter depends on the value chosen in another parameter. For example, the first parameter is independent and might present a list of product categories. When the user selects a category, the second parameter is dependent on the value of the first parameter. Its values are updated with a list of subcategories within the chosen category. When the user views the report, the values for both the category and subcategory parameters are used to filter report data.

The above definition is from Technet: http://technet.microsoft.com/en-us/library/dd255197.aspx

Differences between....

2012-08-17T20:30:00.000-07:00

a. merge and merge join-- Merge Acts like a union all in T-sql but you can provide only two input to this transformation which must be pre- sorted and merge join joins the two datasets like T-SQL joins(inner join , left join, right join )

b. union and union all---- The union command is used to select related information from two tables but with union distinct values are selected. The union all command is equal to the union command, except union all selects all values and will not get rid of duplicate rows.

c. merge join and union all--- Merge can only accept two datasets while Union All can accept more than two datasets for input. The second difference is that Merge requires both datasets to be sorted while Union All does not require sorted datasets

d. row sampling and percentage sampling--- Row sampling is used to randomly select some rows and move them as output to the required process as and when required to divide the rows. Percentage Sampling on the other hand, selects rows randomly by percentage.

e. dts and ssis---- DTS is a set of objects using an ETS tool to extract, transform, and load information to or from a database; SSIS is an ETL tool provided by Microsoft to extract data from different sources.

f. pivot and unpivot---- Pivot will convert rows to columns and un-pivot will go the other way, columns to rows

g. scnhronous transformation and ascnchronous transformation--- A synchronous transformation processes incoming rows and passes them on in the data flow one row at a time. Output is synchronous with input, meaning that it occurs at the same time. The transformation does not need information about other rows in the data set. The Rows are grouped into Buffers as they pass from one component to the next. Meanwhile, in an asynchronous transformation, you cannot pass each row along in the data flow as it is processed, but instead must output data asynchronously, or at a different time, than the input.

h. look-up and merge join--- The Lookup transformation performs lookups by joining data in input columns with columns in a reference dataset. Lookup uses an ole db connection manager. While a merge join combines two sorted datasets using a FULL, LEFT, or INNER join.

Planning

2012-08-15T15:29:00.000-07:00

Creating Sample Databases

2012-08-15T14:37:00.001-07:00