Re: Clustered Index question
From: Tom Moreau (tom_at_dont.spam.me.cips.ca)
Date: 12/12/04
- Next message: Dan Guzman: "Re: Need to select first record by column"
- Previous message: Kevin_at_test.com: "Re: Table variable question (sql 2000) (follow-up)"
- In reply to: Paul fpvt2: "Re: Clustered Index question"
- Next in thread: Paul fpvt2: "Re: Clustered Index question"
- Reply: Paul fpvt2: "Re: Clustered Index question"
- Messages sorted by: [ date ] [ thread ]
Date: Sun, 12 Dec 2004 17:58:00 -0500
You can make the identity a bigint, if need be.
-- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada www.pinnaclepublishing.com . "Paul fpvt2" <anonymous@discussions.microsoft.com> wrote in message news:30e301c4e094$19a3bfc0$a301280a@phx.gbl... Thank you. >I'd still keep the primary key. What if you want to >delete apparent duplicates? The only time I want to delete the data is to delete data that are 4 days or older. I also never need to modify the existing data, once the data is written to the database, it stays there until 4 days later when they will be deleted. If I keep the primary key, the primary key number (identity number) can get very big very quickly. In 1 day alone, we are writing about 2.5 million of data. Pretty soon the primary key will be out of number. Thank you. >-----Original Message----- >I'd try it both ways and measure the results. If you cluster just on >PACKET_TIME, you won't get much page-splitting. Adding the nonclustered >index on PACKET_CONTRACT would give you index coverage and that may expedite >the query. > >I'd still keep the primary key. What if you want to delete apparent >duplicates? > >-- > Tom > >---------------------------------------------------- >Thomas A. Moreau, BSc, PhD, MCSE, MCDBA >SQL Server MVP >Columnist, SQL Server Professional >Toronto, ON Canada >www.pinnaclepublishing.com >.. >"Paul fpvt2" <Paulfpvt2@discussions.microsoft.com> wrote in message >news:EDDA85B1-77A5-4774-8CAC- 93000E4DBDB0@microsoft.com... >Thank you for your reply. >> Which one you choose will depend on the total suite of queries you throw >at >> it - not just this one. >Data is added to the database every milliseconds, and the only 2 queries to >the database is the ones in the Stored Procedure, which is something like: >select packet_time,packet_data from packet where packet_time >= >dateadd(second, @iNum * - 1, getdate()) and packet_contract = >@sContract -->> >99% of the time this query is the one which is executed. >or >select packet_time,packet_data from packet where packet_time >= >dateadd(second, @iNum * - 1, getdate()) -->> but this query is hardly >called. >99% of the time the query above is the one who will be run. > >So, shall I recluster on (PACKET_CONTRACT, PACKET_TIME) ? > >> Why are you returning PACKET_CONTRACT in your result set when you are >> feeding that as a parameter to the proc? You can reduce bandwidth >> consumption by not including that column in your results. >I am sorry, it's a typo, it should be returning PACKET_DATA. > >Another thing, I mentioned that my table has 4 columns: >[PACKET_ID] [int] IDENTITY (1, 1) NOT NULL , >[PACKET_TIME] [datetime] NOT NULL , >[PACKET_CONTRACT] [varchar] (8) NOT NULL , >[PACKET_DATA] [text] ) >The only reason why I have the identity column is to have a Primary key in >the table, because PACKET_TIME and PACKET_CONTRACT are not unique. >I do not need this primary key for anything else, I will not query for it. >I am wondering can I get rid of the Primary Key/identity column and only >have the clustered index (either on PACKET_TIME or on PACKET_CONTRACT, >PACKET_TIME) on the table ? Will it be ok to have a table without a primary >key but have the clustered index ? > >Thank you very much. > > >"Tom Moreau" wrote: > >> There are two approaches you can take: >> >> 1) Stay with the existing indexes and add a nonclustered one on >> PACKET_CONTRACT. >> 2) Recluster on (PACKET_CONTRACT, PACKET_TIME). >> >> Which one you choose will depend on the total suite of queries you throw >at >> it - not just this one. >> >> <sidebar> >> Why are you returning PACKET_CONTRACT in your result set when you are >> feeding that as a parameter to the proc? You can reduce bandwidth >> consumption by not including that column in your results. >> </sidebar> >> >> -- >> Tom >> >> ---------------------------------------------------- >> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA >> SQL Server MVP >> Columnist, SQL Server Professional >> Toronto, ON Canada >> www.pinnaclepublishing.com >> .. >> "Paul fpvt2" <anonymous@discussions.microsoft.com> wrote in message >> news:0e4b01c4deb6$37e5d5c0$a401280a@phx.gbl... >> I am using VB6 with ADO accessing a SQL Server 2000 >> database that has about 10 million records. The database >> has 1 table, and the table has 4 columns. >> CREATE TABLE [dbo].[Packet] ( >> [PACKET_ID] [int] IDENTITY (1, 1) NOT NULL , >> [PACKET_TIME] [datetime] NOT NULL , >> [PACKET_CONTRACT] [varchar] (8) NOT NULL , >> [PACKET_DATA] [text] ) >> >> [PACKET_ID] is the primary key (non clustered), and I >> have a clustered indexed for column [PACKET_TIME]. >> >> I have a stored procedure to get data for the last x >> seconds, minutes, hours or days. >> >> Since my query's WHERE clause contains packet_time and >> packet_contract, shall I include packet_contract in my >> clustered index also ? >> Thanks. >> >> >> >> Here is the stored procedure: >> CREATE procedure mySP >> @sType varchar(1), >> @sContract varchar(8), >> @iNum smallint >> as >> if (@sType = 's') >> begin >> select packet_time,packet_contract >> from packet where packet_time >= dateadd(second, @iNum * - >> 1, getdate()) and packet_contract = @sContract >> end >> else if (@sType = 'm') >> begin >> select packet_time,packet_contract >> from packet where packet_time >= dateadd(minute, @iNum * - >> 1, getdate()) and packet_contract = @sContract >> end >> else if (@sType = 'h') >> begin >> select packet_time,packet_contract >> from packet where packet_time >= dateadd(hour, @iNum * - >> 1, getdate()) and packet_contract = @sContract >> end >> else if (@sType = 'd') >> begin >> select packet_time,packet_contract >> from packet where packet_time >= >> dateadd(day,@iNum * -1,CONVERT( CHAR(10), getdate(), >> 101)) and packet_contract = @sContract >> end >> GO >> >> .. >> >> > >. >
- Next message: Dan Guzman: "Re: Need to select first record by column"
- Previous message: Kevin_at_test.com: "Re: Table variable question (sql 2000) (follow-up)"
- In reply to: Paul fpvt2: "Re: Clustered Index question"
- Next in thread: Paul fpvt2: "Re: Clustered Index question"
- Reply: Paul fpvt2: "Re: Clustered Index question"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
