Wrote a simple query to look at basic attributes of indexing in a specific database:
SELECT [IndexGroup]
,[FileName]
,[TableName]
,[IndexName]
,[dpages]
,[rowcnt]
,[rows]
,[OrigFillFactor]
,[groupid]
,[allocpolicy]
,[status]
,[groupname]
FROM [dbo].[INDEX_INFORMATION]
[Click Below to get the complete code used to create the database view]
[UPDATE] — SQLServerCentral.com has a great article( from 2002) on the use of Temp Tables. You may find the information useful.
I have gotten a ton of hits some links that I created years ago on T-SQL Cursors. Sometimes they are the the best course of action , but may times they are not the most efficient. This posting has some a possible alternative.
Creating Tables with Identity Columns
I like to create tables and temp tables with identity columns. You can then do row by row process the data using the id column. In another post, I will show you how to create and some uses for Table-Valued functions to provide similar utility found with using cursors. Oh, don’t forget our friend the Case Statement, either.
We were creating a extract at work from T-SQL and new that the we would need to export data with leading zeros. The data was not stored in the database with those leading zero values. The solution, create a simple scalar function to dynamically add the zeros that could accept two parameters ( text , and total length).
The Function is rather simple takes the total length value passed by the user and then subtracts the Length of the the text value.
What would make this better?
The below example gives prior and current month first and last day values.The code below dynamically formats the date to give you date range values for reporting:
[LFM-FirstDay] – First Day of thePrior Month or Last Full Month First Day,
[LFM-LastDay] – 11:59 PM of the last day of the prior month,
[CM-FirstDay] – Current Month First Day and
[CM-LastDay] – 11:59 PM of the last day of the Current month.
USE [dbofchoice]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [DBO].[ReportDates]
AS
/*
LFM = LAST FULL MONTH
CM = CURRENT MONTH
*/
SELECT
DATEADD(month, -1 ,DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0))
as [LFM-FirstDay]
,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,getdate()),0))
as [LFM-LastDay]
,DATEADD(mm, DATEDIFF(m,0,getdate()),0)
as [CM-FirstDay]
,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,getdate())+1,0))
as [CM-LastDay]
GO
Found this link that give a deep-dive into fomat and functionality of charts in SSRS 2005. Adds a lot to making charts meaniningful and jumps to details.
TechNet Article:
More
SQL Server Data Services (SSDS) are highly scalable, on-demand data storage and query processing utility services. Built on robust SQL Server database and Windows Server technologies, these services provide high availability, security and support standards-based web interfaces for easy programming and quick provisioning.
From : Main SSDS Site
InfoWeek: InformationWeek Site
Detailed Blog with Info From Mix08
Query Syntax – Etc
A Bit More
http://dev.live.com/blogs/devlive/archive/2008/03/12/220.aspx
Found this link on http://sql-server-performance.com to various Reporting Services Links : HERE
Good FAQ on http://www.ssw.com.au on issues and set-up of SSRS – HERE
Some not so bad How-to’s on You-tube for SQl Server. Some pretty advanced topics like SS with EMC best practices.
Take a Look:
Checkout Related and others Video Here.
Saw a link to this in a http://www.sqlservercentral.com/ Newsletter.
SearchSQLServer.techtarget.com – Memory Sizing
Worth checking out for server sizing and links to KB articles on using AWE memory, etc.
SQL Server Management Studio 2005 (SP2) updates has a wide range of reports that give the users vital information about server health and database objects. You can even add your own custom reports to create you own application database dashboards. The reports are created just like SQL Server Reporting Services RDL’s.
Some Links to help you along:
Listing of some Available Reports HERE
How to make your own Custom Reports HERE