More on SQL indexes

the command to show index statistics:

dbcc show_statistics (tablename, indexname)

The lower the "All density" value the better, means the index is highly selective.

--
How to view index fragmentation (replace database and table names)

   1:  select str(s.index_id,3,0) as indid,

   2:  left(i.name, 20) as index_name,

   3:  left(index_type_desc, 20) as index_type_desc,

   4:  index_depth as idx_depth,

   5:  index_level as level,

   6:  str(avg_fragmentation_in_percent, 5,2) as avg_frgmnt_pct,

   7:  str(page_count, 10,0) as pg_cnt

   8:  FROM sys.dm_db_index_physical_stats

   9:  (db_id('_DBNAME_<dbname>'), object_id('<tablename>_TABLENAME_'),1, 0, 'DETAILED') s

  10:  join sys.indexes i on s.object_id = i.object_id and s.index_id = i.index_id

How to reorganise an index (online operation) - only affects the leaf nodes.

   1:   

   2:  ALTER INDEX <indexname> _INDEXNAME_ on _TABLENAME_ <tablename> REORGANIZE

To rebuild the index without taking the database offline you need the enterprise edition of MSSQL 2005.

   1:  ALTER INDEX  _INDEXNAME_ on _TABLENAME_  REBUILD

If space is an issue it is better to disable a nonclustered index before rebuilding it, then we only need about 20% of the index size as free space to do the rebuild.

Also the Fill Factor can be adjusted based on how we expect the data to be used. This specifies how full the data pages or leaf level index pages will be. Lower fill factor is better when there are many data updates and inserts.

Clustered and non-clustered indexes in MSSQL server

Columns to include in clustered index

. Those that are often accessed sequentially
. Those that contain a large number of distinct values
. Those that are used in range queries that use operators such as BETWEEN, >
<= in the WHERE clause . Those that are frequently used by queries to join or group the result set When to use non-clustered index . Queries that do not return large result sets . Columns that are frequently used in the WHERE clause that return exact match . Columns that have many distinct values (that is, high cardinality) . All columns referenced in a critical query (A special nonclustered index called a covering index that eliminates the need to go to the underlying data pages.) (based on the book SQL Server 2005 Unleashed, Sams 2006). Note: if there are columns usually required in the results but not in the query conditions, we can add them as "included columns" (for non-clustered index), which means they will exist in the leaf nodes of the index for faster retrieval. This is similar to a covering index (same?) To improve index performance, we can "REBUILD" them (drop and recreate) or just "REORGANIZE" them (like defrag, without drop). When we reorganise we have the option to compact large objects. Index can be disabled, in this case if we rebuild it we don't need to have extra temp space. Index can be applied on a view, but there are several conditions that must be met before this can be done. See online documentation. A composite index will only be used optimally (i.e. index seek instead of index scan) if its first column is part of the search argument or join clause. When deciding on an index another parameter to consider is the selectivity of the index i.e. the number of distinct rows returned on the index keys / number of rows in the table. The higher this number the better. e.g.

   1:  select count(distinct surname) from sometable / select count(*) from sometable 

as a rule of thumb we require this to be > 0.85

Example sql code to determine the statistical distribution of values for a specific column

   1:  select state, count(*) as numrows, count(*)/b.totalrows * 100 as percentage 

   2:  from authors a, (select convert(numeric(6,2), count(*)) as totalrows from  authors) as b group by state, b.totalrows 

   3:  having count(*) > 1

   4:  order by 2 desc

   5:  go

SQL datetime as string representation

   1:  CONVERT(VARCHAR(10), [StartDate], 103)

Also experiment with different values instead of 10, to get only the days/month part of date for example.

GreyBox popup window with C# ASP.NET DataBound GridView application

GreyBox is a nice free animated popup window: http://orangoo.com/labs/GreyBox/

In order to get it to work dynamically with a databound GridView in ASP.NET in C# I had to make the following change in the .aspx page:

in the part of the gridview definition, added another template field:

and of course include the greybox folder in the website, and the correct entries in the header section. I hope this helps someone, it took me some time to figure out.

get database views based on system table - uses cursor

   1:  declare @fetchStatus as int; set @fetchStatus = 0

   2:  declare @str as varchar(100), @views as varchar(100)

   3:  declare tmp cursor for

   4:   

   5:  select [name] from sys.views

   6:   

   7:  open tmp

   8:   

   9:  while @fetchStatus = 0 begin

  10:      fetch tmp into @views

  11:      set @fetchStatus = @@fetch_status

  12:      if @fetchStatus = 0 begin

  13:          print @views

  14:          set @str = 'select * from ' + @views + ' where 0 = 1'

  15:          exec (@str)

  16:      end

  17:  end

  18:   

  19:  close tmp

  20:  deallocate tmp

MSSQL catch block to raiseerror using ErrorMessage, ErrorSeverity and ErrorState

   1:  BEGIN CATCH

   2:   

   3:  DECLARE @ErrorMessage        NVARCHAR(4000)

   4:  DECLARE @ErrorSeverity        INT

   5:  DECLARE @ErrorState            INT

   6:   

   7:  SELECT

   8:  @ErrorMessage = ERROR_MESSAGE(),

   9:  @ErrorSeverity = ERROR_SEVERITY(),

  10:  @ErrorState = ERROR_STATE();

  11:   

  12:  RAISERROR (@ErrorMessage, -- Message text.

  13:  @ErrorSeverity, -- Severity.

  14:  @ErrorState -- State.

  15:  );

  16:   

  17:  END CATCH

Nice GUI article

http://www.smashingmagazine.com/2008/12/15/10-useful-techniques-to-improve-your-user-interface-designs/