Getting started with SQL Server Reporting Services links

Nice One for startup...


http://www.codeproject.com/Articles/13725/Getting-started-with-SQL-Server-Reporting-Services

http://www.katieandemil.com/ssrs-create-reports-a-simple-example-of-ssrs-report

Constraints sql server

     A Constraint is a property that we can assign to column in table. By assigning constraint property on column we can prevent the users to enter inconsistent of data in columns. We can assign these Constraint properties during the time of creation of table (By Using CREATE TABLE statement) or during the time of changing the existing table structure (By Using ALTER TABLE statement).

In SQL we have different types of constraints are available those are

1.      Primary Key Constraint

2.      Unique Key Constraint

3.      Foreign Key Constraint

4.      Not Null Constraint

5.      Check Constraint

Now I will explain about each constraint clearly

Primary key constraint is used to uniquely identify each record in database table. It won’t allow repetition or duplication of data. Each table is having only one primary key constraint and it contains only unique values. Primary key constraint doesn’t accept null values.

Example of creating Primary Key constraint during the time of CREATE TABLE

Create Table SampleUserDetail ( UserID integer PRIMARY KEY, UserName varchar(50), FirstName varchar(50), LastName varchar(50) )

Example of creating Primary Key constraint during the time of ALTER TABLE

ALTER TABLE SampleUserDetail ADD PRIMARY KEY (UserID)

To Drop Primary Key constraint on table use the below statement

ALTER TABLE SampleUserDetail DROP Constraint UserID

SQL Unique Key Constraint:

Unique key constraint is same as Primary key Constraint it doesn’t allow duplication or repetition of data in column and we can uniquely identify records in table. The main difference is Primary Key constraint won’t allow null values but unique key constraint allows null values. We have a chance to define only one primary key on table but we can define many unique key constraints on table.

Example of creating Unique Key constraint during the time of table creation

Create Table SampleUserDetail ( UserID integer , UserName varchar(50), FirstName varchar(50), LastName varchar(50) CONSTRAINT us_UserId UNIQUE (UserID) )

Example of creating Unique Key constraint during the time of ALTER TABLE

ALTER TABLE SampleUserDetail ADD CONSTRAINT us_UserId UNIQUE (UserID)

To Drop Unique Key constraint on table use the below statement

ALTER TABLE SampleUserDetail DROP Constraint us_UserId

Example of Foreign key constraint

Create one table with primary key and give name as UserDetails

UserID	UserName	FirstName	LastName
1	SureshDasari	Suresh	Dasari
2	PrasanthiDonthi	Prasanthi	Donthi
3	MaheshDasari	Mahesh	Dasari

After create another table with Foreign Key and give name as SalaryDetails

SalID	Salary	UserID
1	10000	1
2	20000	2
3	30000	3

The column “UserID” is a primary key in UserDetails table

The column “SalID” is a foreign key in SalaryDetails tables

If you observe above two tables UserID in “UserDetails” table points to UserID in “SalaryDetails”

Example of creating Foreign Key constraint during the time of table creation

Create Table SalaryDetails ( SalaryID integer , Salary integer, UserID varchar(50), PRIMARY KEY (SalaryID), CONSTRAINT fk_SalaryID FOREIGN KEY(UserID) REFERENCES UserDetails(UserID) )

Example of creating Foreign Key constraint during the time of ALTER TABLE

ALTER TABLE SalaryDetails ADD CONSTRAINT fk_SalaryID FOREIGN KEY (UserID) REFERENCESUserDetails(UserID)

To Drop Foreign Key constraint on table use the below statement

ALTER TABLE SampleUserDetail DROP Constraint fk_SalaryID

SQL Not NULL Constraint:

If we set Not Null constraint property on any column in table that column won’t accept NULL or Empty values. If you want enforce any column not to accept NULL or empty value just set Not NULL Constraint property for that particular column

Example of creating NOT NULL constraint during the time of table creation

Create Table SampleUserDetail ( UserID integer NOT NULL, UserName varchar(50) NOT NULL, FirstName varchar(50), LastName varchar(50) )

SQL Check Constraint:

The check constraint is used to limit the value range that can be placed in a column. If we set Check constraint property on particular column the values in particular column must satisfy condition set by check constraint.   

Example of creating Check constraint during the time of CREATE TABLE

Create Table SampleUserDetail ( UserID integer NOT NULL CHECK(UserID > 0), UserName varchar(50), FirstName varchar(50), LastName varchar(50) )

Example of creating Check constraint during the time of ALTER TABLE

ALTER TABLE SampleUserDetails ADD CONSTRAINT chk_UserID CHECK(UserID > 0)

To Drop Check constraint on table use the below statement

ALTER TABLE SampleUserDetails DROP CONSTRAINT chk_UserID

SQL Server Stored Procedure Coding Standard

Must read before start writing stored procedure in SQL server 2005

Best practices

• capitalize reserved words
• main keywords on new line
• can't get used to commas before columns
• always use short meaningful table aliases
• prefix views with v
• prefix stored procs with sp (however don't use "sp_" which is reserved for built in procs)
• don't prefix tables
• table names singular
• Use the brackets around objects, so the query engine excplicitly knows a field when it sees it

• Use THE SAME CASE as table object names and field names
• When calling SPs from application, use the fully qualified [dbo].[procName] with correct owner AND case. Not Kidding! Read the articles above!
• Reference the owner of the object so security is explicitly known and doesn't have to be figured out
• Use SET NOCOUNT ON and SET NOCOUNT OFF to eliminate the extra overhead to keep track of how many records are updated in the stored proc unless you need them. Normally, you don't and you can gain a huge increase in performance.

Preferences

• Prefix stored procs with proc
• Suffix every stored proc with SEL, UPD, DEL, INS (or SELECT, UPDATE, DELETE, INSERT)
• Capitalize reserved words
• Main keywords on new line (scripting)
• Use commas before columns (scripting)
• Prefix views with vw
• Don't prefix tables
• Table names singular
• Add a suffix to the standard names like "_ByPK", "_OrderByLastName", or "_Top15Orders" for variations on the stock SP

Example 

    SELECT

      column1
    , column2
    , column3
    , COALESCE(column4,'foo') column4FROM
    tablenameWHERE
    column1 = 'bar'
ORDER BY 
      column1
    , column2

Do

1. Write SET NOCOUNT ON in top of the procedure
2. Every SELECT statement should return with WITH NOLOCK key word.
3. Procedure should be rerunnable, Use IF EXISTS statement …
4. procedure always should have return value or return parameter
5. Use output parameter wherever necessary
6. Each code (for loop, If else etc) should be in BEGIN .. END block. For conditional checks( if else), please follow the same.
7. Use 'Derived tables' wherever possible, as they perform better. Consider the following query to find the second highest salary from the Employees table:

SELECT MIN(Amount) FROM Department WHERE DeptID IN

( SELECT TOP 2 DeptID FROM Department ORDER BY

Amount Desc)

The same query can be re-written using a derived table, as shown

below, and it performs twice as fast as the above query:

SELECT MIN(Amount) FROM

( SELECT TOP 2 Amount FROM Department ORDER BY

Amount DESC) AS D

Don’t

8. Do not include any business logics in your stored procedures, build a Business Logic Layer for that. Keep to what SQL was made for, inserting, deleting and selecting data.
9. Do not use reserved words, if you can’t avoid the use square brackets like: [event]
10. Do not use ‘SP_’ or ‘SYS_’ as a prefix in the stored procedure name.
11. Do proper Error handling in the procedure : use sp_addmessage to create user defined messages and raiserror for raising and passing the error code and description back to calling application.
12. Try to avoid wildcard characters at the beginning of a word while searching using the LIKE keyword, as that results in an index scan, which defeats the purpose of an index. The following statement results in an index scan, while the second statement results in an index seek:

SELECT Ename FROM Employee WHERE Ename LIKE '%han'

SELECT Ename FROM Employee WHERE Ename LIKE 'A%n'

Also avoid searching using not equals operators (<> and NOT) as they result in table and index scans.

13. Avoid the creation of temporary tables while processing data as much as possible, creating a temporary table means more disk I/O. Consider using advanced SQL, views, SQL Server 2000 table variable, or derived tables, instead of temporary tables.

14. Do not use SELECT * in your queries. Always write the required Column names

after the SELECT statement, like:

SELECT CustomerID, CustomerFirstName, City

This technique results in reduced disk I/O and better performance.

15. Do not wtite insert statement without defining the column name like below

INSERT INTO LMS_ERROR_LOG VALUES

( @pProspectId, @pMethodName, @pErrorMsg,

getdate() )

Always define the column name in the insert statement like below

INSERT INTO LMS_ERROR_LOG

( PROSPECT_ID,METHOD_NAME, EXCEPTION,DATE_TIME ) VALUES

@ProspectId,@pMethodName,@pErrorMsg,getdate() )

16. Do not write multiple declare statement inside the procedure like

Declare @Fname nvarchar(100)

Declare @Mname nvarchar(100)

Instead of that you can write like below

Declare

@Fname nvarchar(100),

@Mname nvarchar(100)

I normally follow below guidelines too for consitancy

17. Stored procedure name should be in CAPS latters for consistancy
18. Do Proper indenting ( 2 tabs for start and same for next levels)
19. Stored procedure name should follow some standrad to identify as below PROJECT_NAME_<SELECT OR INSERT OR UPDATE OR DELETE>_<OPERATION or TABLE_NAME>
20. Global parameter start with "p" and local parameter start with "v" . and for OUT parameter name should @pOut<ParameterName>
21. Created By/Date and purpose must be in the procedure as comment
22. All key word should be in the CAPITAL latters