Getting the list of parameter for stored procedure

There are 2 ways to list down the parameters for stored procedure.

(1) In SQL2000 or above, you may do this:

  select *
  from syscolumns
  where id = object_id('pr_get_sales_data')

(2) In SQL2005 or above, you may do this:

  select *
  from information_schema.parameters
  where specific_name = 'pr_get_sales_data'

Installing SQL Express silently

Run the following command to install the SQL Express without letting the user to choose the configuration. Please take note that the "checking pre-requisite" window will still appear on the screen.

   SQLEXPR.EXE ADDLOCAL=SQL_Engine INSTANCENAME=testsql /qb

Encrypting database in SQL 2008

SQL 2008 provides a transparent data encryption. No more complicated calls to the encryption function and SQL statement.


http://msdn.microsoft.com/en-us/library/bb934049.aspx

Maximum Capacity Specifications for SQL Server

Want to know how far MSSQL Server can go? Check this out:

http://msdn.microsoft.com/en-us/library/ms143432.aspx

Query performance

Checklist for Analyzing Slow-Running Queries

http://technet.microsoft.com/en-us/library/ms177500.aspx

Advanced Query Tuning Concepts

http://technet.microsoft.com/en-us/library/ms191426.aspx

SQL analyzer - graphical execution plan icons

There are 2 ways to analyze the query. Either using the "SET SHOWPLAN_TEXT" option or the graphical execution option (available in the SQL Management Studio).

The following URL contains the icons that were used in the graphical execution option.

http://technet.microsoft.com/en-us/library/ms175913.aspx

Indexed view

To speed up the aggregate functions, you may consider using indexed view. The following script shows you how to create an indexed view.

Indexed view basic requirements:

1. COUNT_BIG() must be included in the field list.
2. An unique clustered index must be created.
   

create view vw_test1
with schemabinding
as
select cust_id, total_amt = sum(amt), count = count_big(*)
from invoice
group by cust_id

go

create unique clustered index ix_vw_test1
on vw_test1 (cust_id)

go

select *
from vw_test1 with (noexpand)

Notes:

• "WITH (NOEXPAND)" option must be used when you are querying the data through the indexed view. Otherwise, the query optimizer will take use the view definition to query the data and the indexes created for the view will not be used.
  

References:

http://technet.microsoft.com/en-us/library/ms191432.aspx
http://technet.microsoft.com/en-us/library/cc917715.aspx