SQL-Articles

Query tuning is an interested topic in SQL Server. Deepak has written an article on query tuning in our main website, please read the article below and make use of it.

Query Tuning Steps

Deepak has written an aritcle on Index Usage in www.sql-articles.com. You can check out the article in the link http://sql-articles.com/index.php?page=articles/index_usage.html

To improve the query performance we decided to have separate filegroup for indexes. We decided to move the indexes of very large tables indexes to the new filegroup. Our intention was to separate the index from the data i.e. have the table data (Clustered index) in one filegroup and Nonclustered indexes in another separate filegroup to improve I/O.

We have a table with 1.5 million records; the following is the table schema and its present in [Primary] filegroup which is the default:

CREATE TABLE [dbo].[testmember](
 [memberid] [bigint] IDENTITY(1,1) NOT NULL,
 [name] [nvarchar](50) NULL,
 [emailaddress] [nvarchar](100) NULL,
 [firstname] [nvarchar](50) NULL,
 [createddate] [datetime] NULL,
 [testindex] [varchar](4000) NULL,
 CONSTRAINT [PK_testmember] PRIMARY KEY CLUSTERED (
 [memberid] ASC
 )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,

ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)) ON [primary]

 Read more...

VN:F [1.0.8_357]
please wait...
Rating: 0.0/5 (0 votes cast)
				

														
				

			

SQL Server 2005 SP3 released today after the release of SP3 beta a couple of weeks ago. You can download SP3 from the link below.

DOWNLOAD SQL SERVER 2005 SP3

Above download link is applicable to below products.
* SQL Server 2005 Enterprise
* SQL Server 2005 Enterprise Evaluation
* SQL Server 2005 Developer
* SQL Server 2005 Standard
* SQL Server 2005 Workgroup

For Express edition check the link http://go.microsoft.com/fwlink/?LinkId=64064 for SP3.

For a list of new features and improvements that are included in SQL Server 2005 SP3, review the What’s New document.
Once the server is patched with SP3, SQL Server version will be changed to 9.00.4035. You can use the below query to check it.

SELECT SERVERPROPERTY('ProductLevel')

SELECT SERVERPROPERTY('ProductVersion')

In previous versions of SQL Server we have used sp_makewebtask procedure to generate HTML output for a query. From SQL Server 2008 MS has depreciated web assitant procedures. You can find this option under surface area configuration (SAC) in SSMS 2008 however if you enable this option for SQL Server 2008 you will be thrown with the below error message.

TITLE: Microsoft SQL Server Management Studio
——————————

This method cannot be called on the server version 10. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.1600.22+((SQL_PreRelease).080709-1414+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.UnsupportedVersion&LinkId=20476

——————————
BUTTONS:
OK
——————————

This parameter is provided in SSMS 2008 for backward compatibility. i.e If you connect to SQL 2005 and then you can use this option.

Ok Now comes the question. How can I generate HTML output in SQL Server 2008 without using web assistant stored procedure?
The only option is to use reporting services to generate HTML output in SQL Server 2008. As an alternate you can also write your own T-SQL statements to print HTML tags while generating the output thereby creating a HTML page.