Posts
1147
Comments
891
Trackbacks
1
February 2005 Blog Posts
General SQL FAQ Word Doc

http://www.apmaheshwari.com/mahen/SQL%20FAQ.doc

posted @ Wednesday, February 23, 2005 4:09 PM | Feedback (0)
Detecting and Reporting Errors in Stored Procedures

http://www.sqljunkies.com/article/564f5d77-2f7e-41fb-91c7-353b6d84bf94.scuk

posted @ Wednesday, February 23, 2005 9:57 AM | Feedback (2)
.NET Architecture Center: Service Oriented Architecture

http://msdn.microsoft.com/architecture/soa/

posted @ Friday, February 18, 2005 4:25 PM | Feedback (2)
Nunit and Nunitasp

http://www.nunit.org/

http://nunitasp.sourceforge.net/

posted @ Friday, February 18, 2005 4:09 PM | Feedback (2)
Five Ways to Search a DataSet in .NET

http://www.aspnetpro.com/features/2005/03/asp200503rw_f/asp200503rw_f.asp

posted @ Friday, February 18, 2005 4:05 PM | Feedback (2)
Data Type Performance Tuning Tips for SQL Server

http://www.sql-server-performance.com/datatypes.asp

posted @ Friday, February 18, 2005 11:03 AM | Feedback (2)
ASP.NET memory setting

The process may recycle because of the memoryLimit attribute of the processModel tag in the Machine.config file. The memoryLimit attribute specifies the percentage of physical memory that the ASP.NET worker process can exhaust before the process is automatically recycled. Recycling prevents memory leaks from causing ASP.NET to crash or to stop responding.

posted @ Wednesday, February 16, 2005 10:19 AM | Feedback (2)
Tips & Tricks: Closing Connections with the Using Statement in C#

Here's a tip from Sreekanth Sunku: "The using statement simplifies code for C# developers by automatically generating a try and finally block when the code is compiled. This ensures that the Dispose method is called even if an exception occurs. The following code fragment shows how to use the using statement."

using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
. . .
} // Dispose is automatically called on the conn variable here

One limitation of the using statement is that you can only put a single type in the parentheses. If you want to ensure that Dispose is called on additional resources, you must nest the using statements as shown in the following example.

using (SqlConnection conn = new SqlConnection(connString))
{
SqlCommand cmd = new SqlCommand("CustomerRead");

conn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
Console.WriteLine(dr.GetString(0));
}
}

posted @ Tuesday, February 15, 2005 8:40 PM | Feedback (2)
Index/Search Tests for SQL

create table Test
  (ID int primary key, TestDate datetime)

create index dt_index on Test (TestDate)

create table Days
(DateVal datetime primary key, [WeekNo] int, [year] int)

create index days_wd on Days ([WeekNo])
create index days_yr on Days ([Year])

go

declare @i int;
declare @d datetime;

set @i=0
set nocount on
while @i < 100000
begin
  insert into Test (ID,TestDate) values (@i, convert(datetime, '1/1/2000') + (@i/4))
  set @d = convert(datetime,'1/1/2000') + @i
  insert into Days (DateVal, [WeekNo], [year]) values (@d, datepart(ww,@d), Year(@d))  
  set @i=@i+1
end

set nocount off

-- Here's the slowest:
select * from Test
where Datepart(ww,TestDate) = 14 and DatePart(yy,testdate) = 2000

-- Here's the fastest
select * from Test where TestDate between '3/26/2000' and '4/1/2000'

-- Here's the second fastest (twice as fast as the first):
select Test.* from test
inner join Days on Test.TestDate = Days.dateval
where Days.WeekNo = 14 and Days.Year = 2000

go
drop table Days
drop table Test

posted @ Tuesday, February 15, 2005 11:48 AM | Feedback (2)
SQL Cast for int division

SELECT cast(3 as decimal)/ cast(4 as decimal) * 100

posted @ Monday, February 14, 2005 1:16 PM | Feedback (2)
Scrollable DataGrid

The following code will render a DataGrid inside of an area that is 400 pixels wide and 500 pixels high:

<div style="OVERFLOW:auto;WIDTH:400px;HEIGHT:500px">

 <asp:DataGrid id="DataGrid1" runat="server"></asp:DataGrid>

</div>

posted @ Saturday, February 12, 2005 9:54 AM | Feedback (2)
Transact-SQL Query Performance Tuning Tips

http://www.sql-server-performance.com/transact_sql.asp

posted @ Wednesday, February 09, 2005 9:37 AM | Feedback (2)
SQL Server 2000 I/O Basics

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqliobasics.mspx

posted @ Sunday, February 06, 2005 2:03 PM | Feedback (2)
How to choose primary keys

http://www.aspfaq.com/show.asp?id=2504

posted @ Friday, February 04, 2005 11:19 AM | Feedback (2)
Improving SQL Performance

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag/html/scalenetchapt14.asp

posted @ Friday, February 04, 2005 10:44 AM | Feedback (1)
Index Tuning Strategies

http://www.dlt.com/quest/pdf/microsoft%20infrastructure/microsoft%20sql%20server/tuning%20part%202.pdf

posted @ Friday, February 04, 2005 10:40 AM | Feedback (1)
Estimate SQL Table Size

Modified From: http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=4924?

 

if exists (select * from sysobjects where id = object_id(N'[dbo].[calcspace]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[calcspace]
GO

create procedure CalcSpace
/************************************************************************/
/* Stored Procedure: CalcSpace */
/* Creation Date: 1999-04-11 */
/* Copyright: - */
/* Written by: Sharon Dooley */
/* */
/* Purpose: <purpose of the script> */
/* A procedure to estimate the disk space requirements of a table. */
/* Refer to Books OnLine topic "Estimating the size of a table..." */
/* for a detailed description */
/* */
/* Input Parameters: <list any input parameters> */
/* @table_name VARCHAR(30) Name of table to estimate */
/* @num_rows INT Number of rows in the table */
/* */
/* Output Parameters: <list any output parameters> */
/* - */
/* */
/* Return Status: <list any return codes> */
/* - */
/* */
/* Usage: <a sample usage statement> */
/* EXEC CalcSpace 'MyTable', 10000 */
/* */
/* Other info: <other info for this SP> */
/* The is a direct copy from the CalcSpace stored procedure made by*/
/* Sharon Dooley, 1999-04-11. The only change is the added */
/* documentation header and a small bug fix mentioned below. */
/* */
/* Updates: <this section is used to track changes to the script> */
/* Date Author Purpose */
/* 2000-07-04 Magnus Andersson Changed @sysstat from tinyint */
/* to int to prevent overflow */
/* scenario. Added documentation. */
/* */
/************************************************************************/

(@table_name varchar(30)=null-- name of table to estimate
)
as

declare @msg varchar(120)
declare @num_rows int --number of rows in the table

set @num_rows = (select rowcnt from (SELECT object_name(id) as tablename ,rowcnt
FROM sysindexes with (nolock)
WHERE indid IN (1,0) AND OBJECTPROPERTY(id, 'IsUserTable') = 1) a where tablename = @table_name)

-- Give usage statement if @table_name is null

if @table_name = null or @num_rows = 0
begin
print 'Usage is:'
print ' calcspace table_name, no_of_rows'
print 'where table_name is the name of the table,'
print ' no_of_rows is the number of rows in the table,'
print ' '
return
end

declare @num_fixed_col int,
@fixed_data_size int,
@num_variable_col int,
@max_var_size int,
@null_bitmap int,
@variable_data_size int,
@table_id int,
@num_pages int,
@table_size_in_bytes int,
@table_size_in_meg real,
@table_size_in_kbytes real,
@sysstat int,
@row_size int,
@rows_per_page int,
@free_rows_per_page int,
@fillfactor int,
@num_fixed_ckey_cols int,
@fixed_ckey_size int,
@num_variable_ckey_cols int,
@max_var_ckey_size int,
@cindex_null_bitmap int,
@variable_ckey_size int,
@cindex_row_size int,
@cindex_rows_per_page int,
@data_space_used int,
@num_pages_clevel_0 int,
@num_pages_clevel_1 int,
@num_pages_clevel_x int,
@num_pages_clevel_y int,
@Num_CIndex_Pages int,
@clustered_index_size_in_bytes int,
@num_fixed_key_cols int,
@fixed_key_size int,
@num_variable_key_cols int,
@max_var_key_size int,
@index_null_bitmap int,
@variable_key_size int,
@nl_index_row_size int,
@nl_index_rows_per_page int,
@index_row_size int,
@index_rows_per_page int,
@free_index_rows_per_page int,
@num_pages_level_0 int,
@num_pages_level_1 int,
@num_pages_level_x int,
@num_pages_level_y int,
@num_index_pages int,
@nonclustered_index_size int,
@total_num_nonclustered_index_pages int,
@free_cindex_rows_per_page int,
@tot_pages int

-- initialize variables
select @num_fixed_col =0,
@fixed_data_size =0,
@num_variable_col =0,
@max_var_size =0,
@null_bitmap =0,
@variable_data_size =0,
@table_id =0,
@num_pages =0,
@table_size_in_bytes =0,
@table_size_in_meg =0,
@table_size_in_kbytes =0,
@sysstat =0,
@row_size =0,
@rows_per_page =0,
@num_fixed_ckey_cols =0,
@fixed_ckey_size =0,
@num_variable_ckey_cols =0,
@max_var_ckey_size =0,
@cindex_null_bitmap =0,
@variable_ckey_size =0,
@cindex_row_size =0,
@cindex_rows_per_page =0,
@data_space_used =0,
@num_pages_clevel_0 =0,
@num_pages_clevel_1 =0,
@Num_CIndex_Pages =0,
@clustered_index_size_in_bytes =0,
@num_fixed_key_cols =0,
@fixed_key_size =0,
@num_variable_key_cols =0,
@max_var_key_size =0,
@index_null_bitmap =0,
@variable_key_size =0,
@nl_index_row_size =0,
@nl_index_rows_per_page =0,
@index_row_size =0,
@index_rows_per_page =0,
@free_index_rows_per_page =0,
@num_pages_level_0 =0,
@num_pages_level_1 =0,
@num_pages_level_x =0,
@num_pages_level_y =0,
@num_index_pages =0,
@nonclustered_index_size =0,
@total_num_nonclustered_index_pages =0,
@free_cindex_rows_per_page =0,
@tot_pages =0

set nocount on

--*********************************************
-- MAKE SURE TABLE EXISTS
--*********************************************

select @sysstat = sysstat,
@table_id = id
from sysobjects where name = @table_name

if @sysstat & 7 not in (1,3)
begin
select @msg = 'I can''t find the table '+@table_name
print @msg
return
end

--*********************************************
-- ESTIMATE SIZE OF TABLE
--*********************************************

-- get total number and total size of fixed-length columns

select @num_fixed_col = count(name),
@fixed_data_size = sum(length)
from syscolumns
where id= @table_id and xtype in
(
select xtype from systypes where variable=0
)

if @num_fixed_col= 0 --@fixed_data_size is null. change to 0
select @fixed_data_size=0

-- get total number and total maximum size of variable-length columns

select @num_variable_col=count(name),
@max_var_size= sum(length)
from syscolumns
where id= @table_id and xtype in
(
select xtype from systypes where variable=1
)
if @num_variable_col= 0 --@max_var_size is null. change to 0
select @max_var_size=0

-- get portion of the row used to manage column nullability

select @null_bitmap=2+((@num_fixed_col+7)/8)

-- determine space needed to store variable-length columns
-- this assumes all variable length columns will be 100% full
if @num_variable_col = 0
select @variable_data_size=0
else
select @variable_data_size = 2 + (@num_variable_col *2 )+ @max_var_size

-- get row size

select @row_size= @fixed_data_size +
@variable_data_size +
@null_bitmap + 4 -- 4 represents the data row header


-- get number of rows per page

select @rows_per_page = (8096) / (@row_size+2)

-- If a clustered index is to be created on the table,
-- calculate the number of reserved free rows per page,
-- based on the fill factor specified.
-- If no clustered index is to be created, specify Fill_Factor as 100.

select @fillfactor = 100 -- initialize it to the maximum
select @free_rows_per_page = 0 --initialize to no free rows/page
select @fillfactor=OrigFillFactor
from sysindexes
where id = @table_id and indid=1 -- indid of 1 means the index is clustered

if @fillfactor<>0
-- a 0 fill factor ALMOST fills up the entire page, but not quite.
--The doc says that fill factor zero leaves 2 empty rows (keys)
--in each index page and no free rows in data pages of clustered
--indexes and leaf pages of non-clustered.
--We are working on the data pages in this section
select @free_rows_per_page=8096 * ((100-@fillfactor)/100)/@row_size

-- get number of pages needed to store all rows

select @num_pages = ceiling(convert(dec,@num_rows) / (@rows_per_page-@free_rows_per_page))

-- get storage needed for table data

select @data_space_used=8192*@num_pages


--*********************************************
-- COMPUTE SIZE OF CLUSTERED INDEX IF ONE EXISTS
--*********************************************

-- create a temporary table to contain columns in clustered index. System table
-- sysindexkeys has a list of the column numbers contained in the index

select colid into #col_list
from sysindexkeys where id= @table_id and indid=1 -- indid=1 means clustered

if (select count(*) from #col_list) >0 -- do the following only if clustered index exsists
begin
-- get total number and total maximum size of fixed-length columns in clustered index

select @num_fixed_ckey_cols=count(name),
@fixed_ckey_size= sum(length)
from syscolumns
where id= @table_id and xtype in
(
select xtype from systypes where variable=0
)
and colid in (select * from #col_list)

if @num_fixed_ckey_cols= 0 --@fixed_ckey_size is null. change to 0
select @fixed_ckey_size=0

-- get total number and total maximum size of variable-length columns in clustered index

select @num_variable_ckey_cols=count(name),
@max_var_ckey_size= sum(length)
from syscolumns
where id= @table_id and xtype in
(
select xtype from systypes where variable=1
)
and colid in (select * from #col_list)

if @num_variable_ckey_cols= 0 --@max_var_ckey_size is null. change to 0
select @max_var_ckey_size=0

-- If there are fixed-length columns in the clustered index,
-- a portion of the index row is reserved for the null bitmap. Calculate its size:
if @num_fixed_ckey_cols <> 0
select @cindex_null_bitmap=2+((@num_fixed_ckey_cols + 7)/8)
else
select @cindex_null_bitmap=0

-- If there are variable-length columns in the index, determine how much
-- space is used to store the columns within the index row:

if @num_variable_ckey_cols <> 0
select @variable_ckey_size=2+(@num_variable_ckey_cols *2)+@max_var_ckey_size
else
select @variable_ckey_size=0

-- Calculate the index row size

select @cindex_row_size=@fixed_ckey_size +@variable_ckey_size+@cindex_null_bitmap+1+8

--Next, calculate the number of index rows per page (8096 free bytes per page):

select @cindex_rows_per_page=(8096)/(@cindex_row_size+2)

-- consider fillfactor
if @fillfactor=0
select @free_cindex_rows_per_page = 2
else
select @free_cindex_rows_per_page= 8096 * ((100-@fillfactor)/100)/@cindex_row_size

-- Next, calculate the number of pages required to store
-- all the index rows at each level of the index.

select @num_pages_clevel_0=ceiling(convert(decimal,(@data_space_used/8192))/(@cindex_rows_per_page-@free_cindex_rows_per_page))
select @Num_CIndex_Pages=@num_pages_clevel_0
select @num_pages_clevel_x=@num_pages_clevel_0

while @num_pages_clevel_x <> 1
begin
select @num_pages_clevel_y=ceiling(convert(decimal,@num_pages_clevel_x)/(@cindex_rows_per_page-@free_cindex_rows_per_page))
select @Num_CIndex_Pages=@Num_CIndex_Pages+@num_pages_clevel_y
select @num_pages_clevel_x=@num_pages_clevel_y
end
end

--*********************************************
-- END CLUSTERED INDEX SECTION
--*********************************************

--*********************************************
-- BEGIN NON-CLUSTERED INDEX SECTION
--*********************************************

-- create temp table with non-clustered index info

select indid, colid into #col_list2
from sysindexkeys where id= @table_id and indid<>1 -- indid=1 means clustered

if (select count(*) from #col_list2) >0 -- do the following only if non-clustered indexes exsist
begin
declare @i int -- a counter variable
select @i=1 -- initilize to 2, because 1 is id of clustered index

while @i< 249 -- max number of non-clustered indexes
begin
select @i=@i+1 -- look for the next non-clustered index
-- reinitialize all numbers
select @num_fixed_key_cols = 0,
@fixed_key_size = 0,
@num_variable_key_cols = 0,
@max_var_key_size = 0,
@index_null_bitmap = 0,
@variable_key_size = 0,
@nl_index_row_size = 0,
@nl_index_rows_per_page = 0,
@index_row_size = 0,
@index_rows_per_page = 0,
@free_index_rows_per_page = 0,
@num_pages_level_0 = 0,
@num_pages_level_x = 0,
@num_pages_level_y = 0,
@Num_Index_Pages = 0

-- get total number and total maximum size
-- of fixed-length columns in nonclustered index
select @num_fixed_key_cols=count(name),
@fixed_key_size= sum(length)
from syscolumns
where id= @table_id and xtype in
(
select xtype from systypes where variable=0
)
and colid in (select colid from #col_list2 where indid=@i)
if @num_fixed_key_cols= 0 --@fixed_key_size is null. change to 0
select @fixed_key_size=0

-- get total number and total maximum size of variable-length columns in index

select @num_variable_key_cols=count(name),
@max_var_key_size= sum(length)
from syscolumns
where id= @table_id and xtype in
(
select xtype from systypes where variable=1
)
and colid in (select colid from #col_list2 where indid=@i)
if @num_variable_key_cols= 0 --@max_var_key_size is null. change to 0
select @max_var_key_size=0

if @num_fixed_key_cols = 0 and @num_variable_key_cols = 0 --there is no index
continue
-- If there are fixed-length columns in the non-clustered index,
-- a portion of the index row is reserved for the null bitmap. Calculate its size:
if @num_fixed_key_cols <> 0
select @index_null_bitmap=2+((@num_fixed_key_cols + 7)/8)
else
select @index_null_bitmap=0

-- If there are variable-length columns in the index, determine how much
-- space is used to store the columns within the index row:

if @num_variable_key_cols <> 0
select @variable_key_size=2+(@num_variable_key_cols *2)+@max_var_key_size
else
select @variable_key_size=0

-- Calculate the non-leaf index row size
select @nl_index_row_size=@fixed_key_size +@variable_key_size+@index_null_bitmap+1+8

--Next, calculate the number of non-leaf index rows per page (8096 free bytes per page):

select @nl_index_rows_per_page=(8096)/(@nl_index_row_size+2)

-- Next, calculate the leaf index row size

select @index_row_size=@cindex_row_size + @fixed_key_size + @variable_key_size+@index_null_bitmap+1

-- Next, calculate the number of leaf level index rows per page

select @index_rows_per_page = 8096/(@index_row_size + 2)

-- Next, calcuate the number of reserved free index rows/page based on fill factor

if @fillfactor=0
-- a 0 fill factor ALMOST fills up the entire page, but not quite.
--The doc says that fill factor zero leaves 2 empty rows (keys)
--in each index page and no free rows in data pages of clustered
--indexes and leaf pages of non-clustered.
--We are working on the non-clustered index pages in this section
select @free_index_rows_per_page=0
else
select @free_index_rows_per_page= 8096 * ((100-@fillfactor)/100)/@index_row_size

-- Next, calculate the number of pages required to store
-- all the index rows at each level of the index.

select @num_pages_level_0=ceiling(convert(decimal,@num_rows)/@index_rows_per_page-@free_index_rows_per_page)

select @Num_Index_Pages=@num_pages_level_0
select @num_pages_level_x=@num_pages_level_0

while @num_pages_level_x <> 1
begin
select @num_pages_level_y=ceiling(convert(decimal,@num_pages_level_x)/@nl_index_rows_per_page)
select @Num_Index_Pages=@Num_Index_Pages+@num_pages_level_y
select @num_pages_level_x=@num_pages_level_y
end

select @total_num_nonclustered_index_pages=@total_num_nonclustered_index_pages+@Num_Index_Pages
end
end
--*********************************************
-- END NON-CLUSTERED INDEX SECTION
--*********************************************
-- display numbers

select @tot_pages=@num_pages + @Num_CIndex_Pages + @total_num_nonclustered_index_pages
select @table_size_in_bytes= 8192*@tot_pages
select @table_size_in_kbytes= @table_size_in_bytes/1024.0
select @table_size_in_meg= str(@table_size_in_kbytes/1000.0,17,2)

select substring(@table_name,1,20) as 'Table Name',
convert(varchar(10),@table_size_in_meg) as 'MB Estimate',
@tot_pages as 'Total Pages',
@num_pages as '#Data Pgs',
@Num_CIndex_Pages as '#Clustered Idx Pgs',
@total_num_nonclustered_index_pages as '#NonClustered Idx Pgs'

posted @ Friday, February 04, 2005 9:19 AM | Feedback (4)
How to get a rowcount in T-SQL without using Count(*)

From: http://www.microsoft.com/india/msdn/articles/SQL2000BetterPractices.aspx

SQL Server Books Online (BOL) documents the structure of sysindexes; the value of sysindexes.indid will always be 0 for a table and 1 for a clustered index. If a table doesn't have a clustered index, its entry in sysindexes will always have an indid value of 0. If a table does have a clustered index, its entry in sysindexes will always have an indid value of 1.
 

SELECT object_name(id) as tablename ,rowcnt
FROM sysindexes
WHERE indid IN (1,0) AND OBJECTPROPERTY(id, 'IsUserTable') = 1
order by tablename

posted @ Friday, February 04, 2005 8:54 AM | Feedback (1)
Server tags cannot contain '<'%= ... %'>' constructs

use the binding syntax "<"%# bindingexpression %">"

 

posted @ Thursday, February 03, 2005 11:40 PM | Feedback (0)
Working with hierarchical data in SQL Server databases

http://vyaskn.tripod.com/hierarchies_in_sql_server_databases.htm

posted @ Wednesday, February 02, 2005 10:54 AM | Feedback (1)