SQL Server 2005 Rules

Procedures

1) The maximum number of parameters you can use with a stored procedure is 2100.

2) You cannot create view, trigger, default, procedure, or rule within a stored procedure.

3) The maximum size of a Transact-SQL stored procedure is 128 MB.

Triggers

1) If a table contains a foreign key with an update or delete cascade, then “instead of” triggers should not be defined on that table

2) You cannot create, alter, or drop databases from triggers

3) The ‘instead of’ trigger cannot be created on a view which has been created with check option.

4) Inserted, Deleted logical tables cannot access text, ntext, or image data types

5) You can alter a trigger to change its type from instead of to after and vice versa

6) We can create tables, global temp tables inside a trigger

Views

1) The quoted_identifier setting must be on to create or modify indexed views or indexes on calculated columns

2) To use schemabinding option with a view, 1. '*' is not allowed within the query, and 2.Table name should be mentioned       along with schema name

3) You cannot create views on temporary tables

4) An indexed view should be created with schemabinding option

5) Aggregate functions, such as SUM () and AVG (), are not allowed in updateable views

6) You must create indexed views using only two-part notation; no three- or four-part notation is allowed.

7) Nondeterministic functions such as GETDATE () can’t be used in a view you intend to index.

8) You cannot use order by clause inside a view unless use TOP operator
9) Cannot create indexed view, if the view contains a sub query

Functions

1) Scalar functions will not return text, ntext, or image data type variables or they cannot return table type or cursor type variables.

2) Inline table valued functions will return table data type, they will not have begin and end body

3) You cannot use DML or DDL statements inside a function

Indexing

1) For the index type to be changed, the primary-key constraint must be dropped and    recreated

2) A table column may not be dropped if it is an include column in a covering index

3) A primary key or a unique constraint automatically creates a unique index.

4) The ignore duplicate key option directs to ignore any rows that violate the unique index.

5) When building indexes on views, the first index to be created must be a unique clustered index.

6) We cannot create clustered indexes with include option

7) “Set quoted_identifier” should be on to create nonclustered index with include option

8) We cannot create nonclustered indexes on table variables

9) Unique constraint will create nonclustered index by default

10) Cannot define an index on a view with ignore_dup_key index option.

11) By default, a nonclustered index will be created

12) If we have first column of the index in where clause and selecting only those columns which  have been indexed, then Index Seek will be performed, otherwise Index Scan

13) If you use count (*), index scan will be performed using non-clustered index
14) Priority will be given to the index which contains where clause columns, then order by

SQL

1) We can access up to 256 tables from a single select statement.

2) To explicitly insert values into an identity column you need to set identity_insert to on using dbo.table name

3) To insert value into an identity column you need to define list of columns inside the insert query

4) When identity_insert is on then you must enter values explicitly

5) Cannot use full-text search in master, tempdb, or model database

6) To use COMPUTE...BY clause, ORDER BY clause must be added to the query

7) We cannot enter values into a calculated column

8) TOP is a T-Sql extension and is not portable

9) We can create constraints on table variables

10) FOREIGN KEY constraints are not enforced on local or global temporary tables.

11) The INTO keyword must go in the first select statement of union.

12) CTEs cannot be nested, and they cannot reference the main query.

13) Aggregate queries will ignore the null values.

14) A table can have up to 253 foreign keys and 1024 columns

15) There should be on clause in all the joins except cross join

16) USE keyword cannot be used inside a procedure, trigger, or function

17) SELECT INTO can be used with temp tables

18) You cannot define a foreign key alone without REFERENCES keyword

19) We cannot assign a default value to a local variable.

20) Cross-database foreign key references are not supported.

21) A foreign key can take reference of a column which belongs to the same table

22) To modify a FOREIGN KEY constraint, you must first delete the existing FOREIGN KEY constraint and then re-create it with     the new definition.

23) Primary key will create a nonclustered index if a clustered index already exists on that table

24) In the case of composite keys, combination of keys should be unique (individuals can have duplicates)

25) ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or the table must be empty

26) You can enter duplicate values into an identity column explicitly

Snapshots

1) You cannot back up, restore, or detach the Database Snapshot.

2) It must exist on the same SQL Server instance as the source database.

3) Full-text indexes are not supported.

4) You cannot drop, detach, or restore the source database when a Database Snapshot is present.

5) You cannot create Database Snapshots against system databases.

6) Only a single Database Snapshot can exist against a source database

Temp tables

1) You can create indexes and statistics on temporary tables.

2) You can apply DDL statements against temp tables to add constraints, defaults, and primary keys.

3) We can use sp_help with temp tables (use tempdb)

4) We can use select into statement with temp tables

    (Ex: select * into table from ##temp)

5) We cannot create a view on a temp table

6) Transactions will be logged

Table variables

1) Indexes cannot be created explicitly on table variables

2) Statistics can not be created against table variables

3) We cannot use the any Of DDL commands

4) We cannot log transaction

5) We cannot use select into with table variables