Posts
832
Comments
691
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

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

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)  
  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:

 

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

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

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

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

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

Modified From:

 

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: */
/* 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: */
/* VARCHAR(30) Name of table to estimate */
/* INT Number of rows in the table */
/* */
/* Output Parameters: */
/* - */
/* */
/* Return Status: */
/* - */
/* */
/* Usage: */
/* EXEC CalcSpace 'MyTable', 10000 */
/* */
/* Other info: */
/* 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: */
/* Date Author Purpose */
/* 2000-07-04 Magnus Andersson Changed from tinyint */
/* to int to prevent overflow */
/* scenario. Added documentation. */
/* */
/************************************************************************/

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

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

set = (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 = )

-- Give usage statement if is null

if = null or = 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 int,
int,
l int,
int,
int,
ize int,
int,
int,
ytes int,
eg real,
bytes real,
int,
int,
int,
age int,
int,
cols int,
int,
ey_cols int,
ze int,
map int,
ize int,
int,
_page int,
int,
l_0 int,
l_1 int,
l_x int,
l_y int,
s int,
_size_in_bytes int,
ols int,
int,
y_cols int,
e int,
ap int,
ze int,
ze int,
er_page int,
int,
page int,
_per_page int,
_0 int,
_1 int,
_x int,
_y int,
int,
dex_size int,
ustered_index_pages int,
s_per_page int,
int

-- initialize variables
select =0,
=0,
l =0,
=0,
=0,
ize =0,
=0,
=0,
ytes =0,
eg =0,
bytes =0,
=0,
=0,
=0,
cols =0,
=0,
ey_cols =0,
ze =0,
map =0,
ize =0,
=0,
_page =0,
=0,
l_0 =0,
l_1 =0,
s =0,
_size_in_bytes =0,
ols =0,
=0,
y_cols =0,
e =0,
ap =0,
ze =0,
ze =0,
er_page =0,
=0,
page =0,
_per_page =0,
_0 =0,
_1 =0,
_x =0,
_y =0,
=0,
dex_size =0,
ustered_index_pages =0,
s_per_page =0,
=0

set nocount on

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

select = sysstat,
= id
from sysobjects where name =

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

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

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

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

if = 0 is null. change to 0
select =0

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

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

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

select =2+((+7)/8)

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

-- get row size

select = +
ize +
+ 4 -- 4 represents the data row header


-- get number of rows per page

select = (8096) / (+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 = 100 -- initialize it to the maximum
select age = 0 --initialize to no free rows/page
select =OrigFillFactor
from sysindexes
where id = and indid=1 -- indid of 1 means the index is clustered

if <>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 age=8096 * ((

-- get number of pages needed to store all rows

select = ceiling(convert(dec,) / (-age))

-- get storage needed for table data

select =8192*


--*********************************************
-- 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= 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 cols=count(name),
= sum(length)
from syscolumns
where id= and xtype in
(
select xtype from systypes where variable=0
)
and colid in (select * from #col_list)

if cols= 0 is null. change to 0
select =0

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

select ey_cols=count(name),
ze= sum(length)
from syscolumns
where id= and xtype in
(
select xtype from systypes where variable=1
)
and colid in (select * from #col_list)

if ey_cols= 0 is null. change to 0
select ze=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 cols <> 0
select map=2+((cols + 7)/8)
else
select map=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 ey_cols <> 0
select ize=2+(ey_cols *2)+ze
else
select ize=0

-- Calculate the index row size

select =

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

select _page=(8096)/(+2)

-- consider fillfactor
if =0
select s_per_page = 2
else
select s_per_page= 8096 * ((

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

select l_0=ceiling(convert(decimal,(/8192))/(_page-s_per_page))
select s=l_0
select l_x=l_0

while l_x <> 1
begin
select l_y=ceiling(convert(decimal,l_x)/(_page-s_per_page))
select s=s+l_y
select l_x=l_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= 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 ols = 0,
= 0,
y_cols = 0,
e = 0,
ap = 0,
ze = 0,
ze = 0,
er_page = 0,
= 0,
page = 0,
_per_page = 0,
_0 = 0,
_x = 0,
_y = 0,
= 0

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

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

select y_cols=count(name),
e= sum(length)
from syscolumns
where id= and xtype in
(
select xtype from systypes where variable=1
)
and colid in (select colid from #col_list2 where )
if y_cols= 0 is null. change to 0
select e=0

if ols = 0 and y_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 ols <> 0
select ap=2+((ols + 7)/8)
else
select ap=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 y_cols <> 0
select ze=2+(y_cols *2)+e
else
select ze=0

-- Calculate the non-leaf index row size
select ze=

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

select er_page=(8096)/(ze+2)

-- Next, calculate the leaf index row size

select = + + ze+ap+1

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

select page = 8096/( + 2)

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

if =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 _per_page=0
else
select _per_page= 8096 * ((

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

select _0=ceiling(convert(decimal,)/page-_per_page)

select =_0
select _x=_0

while _x <> 1
begin
select _y=ceiling(convert(decimal,_x)/er_page)
select =+_y
select _x=_y
end

select ustered_index_pages=ustered_index_pages+
end
end
--*********************************************
-- END NON-CLUSTERED INDEX SECTION
--*********************************************
-- display numbers

select = + s + ustered_index_pages
select ytes=
select bytes= ytes/1024.0
select eg= str(bytes/1000.0,17,2)

select substring(,1,20) as 'Table Name',
convert(varchar(10),eg) as 'MB Estimate',
as 'Total Pages',
as '#Data Pgs',
s as '#Clustered Idx Pgs',
ustered_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:

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)