non-required parameter

I using BI development studio to create some reports and one of the stored procedures that I have takes in 4 parameters.

ManagerID int =0
OfficeID int =0
StartDate datetime
EndDate datetime

In the report designer I use 2 other sql queries to populate ManagerID fields and OfficeID Fields.

The report will work if the customer selects both ManagerID and OfficeID fields but will not work if Manager OR OfficeID fields are select. They both have to be selected or an error message will be displayed stating that the non-selected field must have a value.

The the parameters properties section I have changed the type is integer and select allowed NULLs, I have even set the default value to 0 this didn't work. Then I tried changing the type from integer to string and selected 'allowed blanks'.

I still get the popup message stating that the non-select item must have a value.

Is there a way I can select 1 and not the other?

why not to try to add those drop down list to the web page not in the report and pass the selected parameters to the report file

Sample code

Private Sub SetReportParameters()
Dim userid As New ReportParameter("userid",
Security.GetUserID().ToString())
Dim p() As ReportParameter = {userid}
ReportViewer1.ServerReport.SetParameters(p)
End Sub

http://www.microsoft.com/technet/prodtechnol/sql/2005/2005ssrs.mspx

http://www.simple-talk.com/sql/learn-sql-server/beginning-sql-server-2005-reporting-services-part-1/

|||

Tthank you for the reply.

One thing I should have mentioned is that we are using ColdFusion 7 with SQL 2005.
I am still learning a few things about it.
If will try passing in those parameters but I am not sure how it will work with this structure.

|||

I don't think there is any problem but i cam through great web cast for this hope it helps you

SQL Server Reporting Services for Cold Fusion Developers
https://www119.livemeeting.com/cc/mseventsbmo/view?id=1032256029&role=attend&pw=7933C65D

|||

Thank you for the link, I have been looking for this video for awhile and all I got was a word document of the webcast.

I will be watching it soon.

I will mark this as answered.

non-identity Primary Key

I need to be able to increment a Primary Key of type int without using
IDENTITY.
The reason is that I need to be able to Archive and restore data to this
table, and maintain the Primary Key.
Anyway, I setup a trigger to get the Max(MyIDfield) which works fine when
inserting single records into Table1.
The problem is when I need to insert new records from a select statement.
The trigger doesn't work for a "set" type insert.
I searched examples, but everything I found only supported single inserts.
Here's an example of the table structure.
CREATE TABLE Table1(
MyIDfield int NOT NULL PRIMARY KEY,
MyText varchar(50)
)
CREATE TABLE Table2(
MyIDfield int NOT NULL PRIMARY KEY,
MyText varchar(50)
)
As I explained, I need to..
[1] have unique Primary Keys [MyIDfield] across both tables
[2] be able to insert thousands of records into Table1 using an
insert/select query [trigger solution preferred]
[3] NOT use IDENTITY, in case I need to restore archived records [move back
from Table2 to Table1]
Thanks for any help,
ChrisYou can use the IDENTITY property, archive and restore data and still
maintain the primary key. Deleting records does not reset the IDENTITY
property (unless you use TRUNCATE).
Ben Nevarez, MCDBA, OCP
Database Administrator
"Chris" wrote:

> I need to be able to increment a Primary Key of type int without using
> IDENTITY.
> The reason is that I need to be able to Archive and restore data to this
> table, and maintain the Primary Key.
> Anyway, I setup a trigger to get the Max(MyIDfield) which works fine when
> inserting single records into Table1.
> The problem is when I need to insert new records from a select statement.
> The trigger doesn't work for a "set" type insert.
> I searched examples, but everything I found only supported single inserts.
> Here's an example of the table structure.
> CREATE TABLE Table1(
> MyIDfield int NOT NULL PRIMARY KEY,
> MyText varchar(50)
> )
> CREATE TABLE Table2(
> MyIDfield int NOT NULL PRIMARY KEY,
> MyText varchar(50)
> )
> As I explained, I need to..
> [1] have unique Primary Keys [MyIDfield] across both tables
> [2] be able to insert thousands of records into Table1 using an
> insert/select query [trigger solution preferred]
> [3] NOT use IDENTITY, in case I need to restore archived records [move ba
ck
> from Table2 to Table1]
> Thanks for any help,
> Chris
>
>|||And forgot to mention that you can restore records maintaining their primary
key using SET IDENTITY_INSERT ON (or DTS/SSIS with 'Enable identity insert'
checked).
Ben Nevarez, MCDBA, OCP
Database Administrator
"Ben Nevarez" wrote:
> You can use the IDENTITY property, archive and restore data and still
> maintain the primary key. Deleting records does not reset the IDENTITY
> property (unless you use TRUNCATE).
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Chris" wrote:
>|||That's the ticket! I've heard of that, but it never came to mind. Never
had a case where I had to use SET IDENTITY_INSERT ON.
Thanks for the help! Perfect solution.
"Ben Nevarez" <bnevarez@.sjm.com> wrote in message
news:217620C1-DAF2-45E5-8660-DD23BE1A8B3C@.microsoft.com...
> And forgot to mention that you can restore records maintaining their
primary
> key using SET IDENTITY_INSERT ON (or DTS/SSIS with 'Enable identity
insert'
> checked).
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Ben Nevarez" wrote:
>
this
when
statement.
inserts.
[move back|||Chris (rooster575@.hotmail.com) writes:
> I need to be able to increment a Primary Key of type int without using
> IDENTITY.
> The reason is that I need to be able to Archive and restore data to this
> table, and maintain the Primary Key.
> Anyway, I setup a trigger to get the Max(MyIDfield) which works fine when
> inserting single records into Table1.
> The problem is when I need to insert new records from a select statement.
> The trigger doesn't work for a "set" type insert.
> I searched examples, but everything I found only supported single inserts.
Bounce the data over a temp table with an IDENTITY column, and the
MAX value to the IDENTITY column.
If you are on SQL 2005, you could use the Row_number() function and be
saved the temp table.
I assume that the trigger is an INSTEAD OF trigger?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Nonclustered index question

I have a table that has around 100,000 records in it, growing at a rate
of 200 records/day:
CREATE TABLE [Main] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Code] [char] (17) NOT NULL ,
[AgentID] [int] NULL ,
[DueDate] [smalldatetime] NOT NULL ,
[Status] [varchar] (18) NOT NULL
)
(This table also has about 30 more fields, but I have left them out for
brevity.)
I am looking to speed up a few specific search queries. I already have
a clustered index on my primary key field [ID], and a non-clustered
index on field [Code]. These indexes are fine performance wise.
I am looking to speed up 2 searches:
1) A combination of [AgentID] and [DueDate]: "... WHERE AgentID = n
AND DueDate BETWEEN (...)
2) [Status]: WHERE [Status]=xxx. Actually, this search is already
fast, and [Status] will really only have about 7 distinct values, so I
didn't index this field, as I figured a table scan would be faster in
this case. IS THIS A CORRECT ASSUMPTION. The result set for this
query is almost aways under 25 rows
My main question is: Should I put nonclustered indexes on [DueDate]
and [AgentID]. AgentID will have maybe 25 distinct values throughout
the table. The result set will usually be under 50 rows.
I just want to make sure I am not overindexing the table, and that if I
index these fields it will be beneficial.
Any help would be appreciated.for query 1, an index on ([AgentID], [DueDate]) could be an option, but
it looks like AgentID is not too sleective:

> AgentID will have maybe 25 distinct values throughout
so I'd also consider indexes on ([DueDate], [AgentID]) and just on
([DueDate])|||> 2) [Status]: WHERE [Status]=xxx. Actually, this search is already
> fast, and [Status] will really only have about 7 distinct values, so I
> didn't index this field, as I figured a table scan would be faster in
> this case. IS THIS A CORRECT ASSUMPTION. The result set for this
> query is almost aways under 25 rows
How exactly are you using this column? (i.e. how many rows per each value)
Searching for individual status values might be improved through the use of
indexed views - one per each status value. But may have a negative impact on
inserts/updates.
ML
http://milambda.blogspot.com/|||With 7 distinct values for Status, 100,000 rows in the table, and
WHERE [Status]=xxx returning under 25 rows, I have to assume that the
values for Status are severely skewed, and the status being tested is
one of the low frequency ones. Even with so few values, a query on a
specific low frequency value may in fact benefit from an index. I
would add an index on Status and see if it was used and if it helped.
With 100,000 rows, 25 distinct values for AgentID, and a result set of
around 50 rows, I have to assume that the DueDate BETWEEN test is
quite restrictive. I would try an index on (AgentID,DueDate).
Roy
On 23 Feb 2006 08:29:24 -0800, kaczmar2@.hotmail.com wrote:

>I have a table that has around 100,000 records in it, growing at a rate
>of 200 records/day:
>CREATE TABLE [Main] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [Code] [char] (17) NOT NULL ,
> [AgentID] [int] NULL ,
> [DueDate] [smalldatetime] NOT NULL ,
> [Status] [varchar] (18) NOT NULL
> )
>(This table also has about 30 more fields, but I have left them out for
>brevity.)
>I am looking to speed up a few specific search queries. I already have
>a clustered index on my primary key field [ID], and a non-clustered
>index on field [Code]. These indexes are fine performance wise.
>I am looking to speed up 2 searches:
>1) A combination of [AgentID] and [DueDate]: "... WHERE AgentID = n
>AND DueDate BETWEEN (...)
>2) [Status]: WHERE [Status]=xxx. Actually, this search is already
>fast, and [Status] will really only have about 7 distinct values, so I
>didn't index this field, as I figured a table scan would be faster in
>this case. IS THIS A CORRECT ASSUMPTION. The result set for this
>query is almost aways under 25 rows
>My main question is: Should I put nonclustered indexes on [DueDate]
>and [AgentID]. AgentID will have maybe 25 distinct values throughout
>the table. The result set will usually be under 50 rows.
>I just want to make sure I am not overindexing the table, and that if I
>index these fields it will be beneficial.
>Any help would be appreciated.|||kaczmar2@.hotmail.com wrote:
> I have a table that has around 100,000 records in it, growing at a
> rate of 200 records/day:
> CREATE TABLE [Main] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [Code] [char] (17) NOT NULL ,
> [AgentID] [int] NULL ,
> [DueDate] [smalldatetime] NOT NULL ,
> [Status] [varchar] (18) NOT NULL
> )
> (This table also has about 30 more fields, but I have left them out
> for brevity.)
> I am looking to speed up a few specific search queries. I already
> have a clustered index on my primary key field [ID], and a
> non-clustered index on field [Code]. These indexes are fine
> performance wise.
> I am looking to speed up 2 searches:
> 1) A combination of [AgentID] and [DueDate]: "... WHERE AgentID = n
> AND DueDate BETWEEN (...)
Depending on the selectiveness of your query criteria and distribuition of
values it might in fact be better to change the PK to non clustered and
have a clustered index on (DueDate, AgentID). If inserted DueDates are
increasing then inserts may also benefit from this index layout. On the
other hand, ID will be increasing, too. But for a range query a clustered
index is usually favourable.

> 2) [Status]: WHERE [Status]=xxx. Actually, this search is already
> fast, and [Status] will really only have about 7 distinct values, so I
> didn't index this field, as I figured a table scan would be faster in
> this case. IS THIS A CORRECT ASSUMPTION. The result set for this
> query is almost aways under 25 rows
Assuming equal distribution of values you'll get only 14% of the rows with
this criterion. I would assume that an index pays off here as this seems
pretty selective. Since you seem to be mostly searching for low freq
values it's highly likely that and index would help, especially as the
table grows. The more often you search for low freq values the more the
index pays off.

> My main question is: Should I put nonclustered indexes on [DueDate]
> and [AgentID]. AgentID will have maybe 25 distinct values throughout
> the table. The result set will usually be under 50 rows.
> I just want to make sure I am not overindexing the table, and that if
> I index these fields it will be beneficial.
You'll have to do some testing to get definitive answers. And you
probably should verify your findings from time to time as the table grows.
You might as well create a test table with the same layout and much more
data (but with typical distribution) if you want to see the behavior for
large tables beforehand.
Kind regards
robert

Non unique Clustered index

I have 1,000,000 records there are unique by member(int),vin(20),stock(18).
there are 1200 unique members all updates are done by the primary key
(member,vin,stock). My customer does not want to add an identity column.
There is only a non clustered unique PK on the table, no clustered index.
I am wondering which would be better
1. Put a unique Clustered PK constraint on the 40 byte fields
member(int),vin(20),stock(18) (indexes would be large)
or
2 Put a non Clustered index on member id (4 bytes)(let sql add the
identifier(4 bytes))and put the Primary Key on member(int),vin(20),stock(18)
as a unique non
clustered constraint.
This table has heavy updates(no Pkey fields updated ) and inserts at night
in batch (15,000 updates 5,000 inserts approx per night).
There is currently no clustered index and there is no way to control
fragmentation.
Thanks,
Jon A
If you have only one index on a table, it's usually best to be clustered.
The downside to a wide clustered index is that the clustered index keys are
stored in non-clustered indexes as well. This is not an issue when you
don't have non-clustered indexes, though.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon A" <JonA@.discussions.microsoft.com> wrote in message
news:E1406DAF-36A7-46AB-9EFD-27F942A59B51@.microsoft.com...
>I have 1,000,000 records there are unique by member(int),vin(20),stock(18).
> there are 1200 unique members all updates are done by the primary key
> (member,vin,stock). My customer does not want to add an identity column.
> There is only a non clustered unique PK on the table, no clustered index.
> I am wondering which would be better
> 1. Put a unique Clustered PK constraint on the 40 byte fields
> member(int),vin(20),stock(18) (indexes would be large)
> or
> 2 Put a non Clustered index on member id (4 bytes)(let sql add the
> identifier(4 bytes))and put the Primary Key on
> member(int),vin(20),stock(18)
> as a unique non
> clustered constraint.
> This table has heavy updates(no Pkey fields updated ) and inserts at night
> in batch (15,000 updates 5,000 inserts approx per night).
> There is currently no clustered index and there is no way to control
> fragmentation.
> --
> Thanks,
> Jon A
|||Jon A wrote:
> I have 1,000,000 records there are unique by
> member(int),vin(20),stock(18). there are 1200 unique members all
> updates are done by the primary key (member,vin,stock). My customer
> does not want to add an identity column. There is only a non
> clustered unique PK on the table, no clustered index.
> I am wondering which would be better
> 1. Put a unique Clustered PK constraint on the 40 byte fields
> member(int),vin(20),stock(18) (indexes would be large)
> or
> 2 Put a non Clustered index on member id (4 bytes)(let sql add the
> identifier(4 bytes))and put the Primary Key on
> member(int),vin(20),stock(18) as a unique non
> clustered constraint.
> This table has heavy updates(no Pkey fields updated ) and inserts at
> night in batch (15,000 updates 5,000 inserts approx per night).
> There is currently no clustered index and there is no way to control
> fragmentation.
I would argue that because you have a natural key as your PK, you're
likely to see page splitting if you use a clustered index. Since this is
a nightly batch, it may not matter. But then again, having a clustered
index on this table may not matter either, depnding on how the SELECTS
and UPDATES look.
I do agree with Dan. That is, it's best for most, if not all, tables to
have a clustered index. But to add one without a careful investigation
of the table and how it's used is necessary. Just as you would consider
what columns would best make use of a clustered index during database
design, you should perform the same due diligence now.
Look at your queries and table access. See how the data is updated. Is
it more than one row at a time? Is it ever changing a column value that
could be in the clustered index? What do the inserts look like? Are they
adding rows with column values that will most likely cause spage
splitting and slower insert performance at night? Look at the SELECTS on
the table. Do you ever return more than one row at a time? If so, what
criteria determine the rows returned? Do you have ORDER BY statements in
your queries? Do they really need to be there?
If you can post more information about how the table is used, we may be
able to offer more advice.
David Gugick
Imceda Software
www.imceda.com
|||Hi Jon,
I am not really sure what problem you are trying to solve. Is there a
problem?
If the potential problem is fragmentation control, then you could simply
add and drop a clustered index (on any column) during a service window.
If you do that periodically, fragmentation should be under control.
The rest depends on the queries you are using. A 40-byte index in itself
doesn't cause problems. If Insert and Delete performance during the day
is not an issue, then you could safely make the Primary Key index
clustered. And even with the proper fillfactors, Insert performance
shouldn't be a problem.
Having a clustered index can help Select performance on ranges a lot
(for example, the range member = <somevalue>). For high selectivity
Selects, a clustered index does not add much value.
HTH,
Gert-Jan
Jon A wrote:
> I have 1,000,000 records there are unique by member(int),vin(20),stock(18).
> there are 1200 unique members all updates are done by the primary key
> (member,vin,stock). My customer does not want to add an identity column.
> There is only a non clustered unique PK on the table, no clustered index.
> I am wondering which would be better
> 1. Put a unique Clustered PK constraint on the 40 byte fields
> member(int),vin(20),stock(18) (indexes would be large)
> or
> 2 Put a non Clustered index on member id (4 bytes)(let sql add the
> identifier(4 bytes))and put the Primary Key on member(int),vin(20),stock(18)
> as a unique non
> clustered constraint.
> This table has heavy updates(no Pkey fields updated ) and inserts at night
> in batch (15,000 updates 5,000 inserts approx per night).
> There is currently no clustered index and there is no way to control
> fragmentation.
> --
> Thanks,
> Jon A
|||An IDENTITY is a lousy candidate for a Clustered Index, almost as horrible
as allowing a table without a Clustered Index at all (a heap).
Heaps are large and, as you've noticed, do not allow you to as easily
control your index rebuild (defragmentaiton) as easily. First of all, the
Clustered Index itself adds no space to the table; its only the use of the
key as a pointer in the other indexes that can grow your non-clustered
indexes. However, consider how large the ROWID is as the alternative to the
clustered index key.
As far as uniqueness, if the Clustered Index is not unique, SQL Server will
make it so by appending a GUID to the key to force it to be unique. Weigh
that against the composite index length, not to mention the size of the heap
alternative.
As to the IDENTITY, if you use one, NEVER make it a clustered index, unless
there are absolutely no other candidates. When will you EVER query an
IDENTITY by range? Also, if you use an IDENTITY, this is normally used as a
surrogate, as in your case, which does not remove the uniqueness requirement
of the business key you would be replacing as the Primary Key. So, better
add an UNIQUE non-Clustered Constraint to the original candidate(s).
Sincerely,
Anthony Thomas

"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:422B03AC.344C5898@.toomuchspamalready.nl...
Hi Jon,
I am not really sure what problem you are trying to solve. Is there a
problem?
If the potential problem is fragmentation control, then you could simply
add and drop a clustered index (on any column) during a service window.
If you do that periodically, fragmentation should be under control.
The rest depends on the queries you are using. A 40-byte index in itself
doesn't cause problems. If Insert and Delete performance during the day
is not an issue, then you could safely make the Primary Key index
clustered. And even with the proper fillfactors, Insert performance
shouldn't be a problem.
Having a clustered index can help Select performance on ranges a lot
(for example, the range member = <somevalue>). For high selectivity
Selects, a clustered index does not add much value.
HTH,
Gert-Jan
Jon A wrote:
> I have 1,000,000 records there are unique by
member(int),vin(20),stock(18).
> there are 1200 unique members all updates are done by the primary key
> (member,vin,stock). My customer does not want to add an identity column.
> There is only a non clustered unique PK on the table, no clustered index.
> I am wondering which would be better
> 1. Put a unique Clustered PK constraint on the 40 byte fields
> member(int),vin(20),stock(18) (indexes would be large)
> or
> 2 Put a non Clustered index on member id (4 bytes)(let sql add the
> identifier(4 bytes))and put the Primary Key on
member(int),vin(20),stock(18)
> as a unique non
> clustered constraint.
> This table has heavy updates(no Pkey fields updated ) and inserts at night
> in batch (15,000 updates 5,000 inserts approx per night).
> There is currently no clustered index and there is no way to control
> fragmentation.
> --
> Thanks,
> Jon A
|||I added the clustered PK index as (member(int),vin(20),stock(18)). And with
adjustment the page splitting is minimal. But the Table is now 3x the size it
was previously.
My question is this in general terms. What is the problems / overhead of a
non unique clustered index? Is this a bad thing? I have never had a case
where I would do that. But as a result of this problem I am now curious.
"David Gugick" wrote:
|||I wouldn't expect changing the PK from non-clustered to clustered to
increase space requirements. In fact, I would think the space would
decrease. Are you certain there are no non-clustered indexes on the table?
You can double check with sp_helpindex.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon A" <JonA@.discussions.microsoft.com> wrote in message
news:E551392D-6A3D-4176-B1EB-7847AB685B51@.microsoft.com...
>I added the clustered PK index as (member(int),vin(20),stock(18)). And with
> adjustment the page splitting is minimal. But the Table is now 3x the size
> it
> was previously.
> My question is this in general terms. What is the problems / overhead of a
> non unique clustered index? Is this a bad thing? I have never had a case
> where I would do that. But as a result of this problem I am now curious.
> "David Gugick" wrote:
>
|||My sources: http://www.sql-server-performance.co...ed_indexes.asp
say that the uniqueifier used in a non-unique clustered index is a 4 byte value, as opposed to a (16 byte) GUID. Is there any other support either way? I can't find any in BOL.
jg

Quote:

...Originally posted by Anthony Thomas
As far as uniqueness, if the Clustered Index is not unique, SQL Server will
make it so by appending a GUID to the key to force it to be unique...

Non unique Clustered index

I have 1,000,000 records there are unique by member(int),vin(20),stock(18).
there are 1200 unique members all updates are done by the primary key
(member,vin,stock). My customer does not want to add an identity column.
There is only a non clustered unique PK on the table, no clustered index.
I am wondering which would be better
1. Put a unique Clustered PK constraint on the 40 byte fields
member(int),vin(20),stock(18) (indexes would be large)
or
2 Put a non Clustered index on member id (4 bytes)(let sql add the
identifier(4 bytes))and put the Primary Key on member(int),vin(20),stock(18)
as a unique non
clustered constraint.
This table has heavy updates(no Pkey fields updated ) and inserts at night
in batch (15,000 updates 5,000 inserts approx per night).
There is currently no clustered index and there is no way to control
fragmentation.
--
Thanks,
Jon AIf you have only one index on a table, it's usually best to be clustered.
The downside to a wide clustered index is that the clustered index keys are
stored in non-clustered indexes as well. This is not an issue when you
don't have non-clustered indexes, though.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon A" <JonA@.discussions.microsoft.com> wrote in message
news:E1406DAF-36A7-46AB-9EFD-27F942A59B51@.microsoft.com...
>I have 1,000,000 records there are unique by member(int),vin(20),stock(18).
> there are 1200 unique members all updates are done by the primary key
> (member,vin,stock). My customer does not want to add an identity column.
> There is only a non clustered unique PK on the table, no clustered index.
> I am wondering which would be better
> 1. Put a unique Clustered PK constraint on the 40 byte fields
> member(int),vin(20),stock(18) (indexes would be large)
> or
> 2 Put a non Clustered index on member id (4 bytes)(let sql add the
> identifier(4 bytes))and put the Primary Key on
> member(int),vin(20),stock(18)
> as a unique non
> clustered constraint.
> This table has heavy updates(no Pkey fields updated ) and inserts at night
> in batch (15,000 updates 5,000 inserts approx per night).
> There is currently no clustered index and there is no way to control
> fragmentation.
> --
> Thanks,
> Jon A|||Jon A wrote:
> I have 1,000,000 records there are unique by
> member(int),vin(20),stock(18). there are 1200 unique members all
> updates are done by the primary key (member,vin,stock). My customer
> does not want to add an identity column. There is only a non
> clustered unique PK on the table, no clustered index.
> I am wondering which would be better
> 1. Put a unique Clustered PK constraint on the 40 byte fields
> member(int),vin(20),stock(18) (indexes would be large)
> or
> 2 Put a non Clustered index on member id (4 bytes)(let sql add the
> identifier(4 bytes))and put the Primary Key on
> member(int),vin(20),stock(18) as a unique non
> clustered constraint.
> This table has heavy updates(no Pkey fields updated ) and inserts at
> night in batch (15,000 updates 5,000 inserts approx per night).
> There is currently no clustered index and there is no way to control
> fragmentation.
I would argue that because you have a natural key as your PK, you're
likely to see page splitting if you use a clustered index. Since this is
a nightly batch, it may not matter. But then again, having a clustered
index on this table may not matter either, depnding on how the SELECTS
and UPDATES look.
I do agree with Dan. That is, it's best for most, if not all, tables to
have a clustered index. But to add one without a careful investigation
of the table and how it's used is necessary. Just as you would consider
what columns would best make use of a clustered index during database
design, you should perform the same due diligence now.
Look at your queries and table access. See how the data is updated. Is
it more than one row at a time? Is it ever changing a column value that
could be in the clustered index? What do the inserts look like? Are they
adding rows with column values that will most likely cause spage
splitting and slower insert performance at night? Look at the SELECTS on
the table. Do you ever return more than one row at a time? If so, what
criteria determine the rows returned? Do you have ORDER BY statements in
your queries? Do they really need to be there?
If you can post more information about how the table is used, we may be
able to offer more advice.
--
David Gugick
Imceda Software
www.imceda.com|||Hi Jon,
I am not really sure what problem you are trying to solve. Is there a
problem?
If the potential problem is fragmentation control, then you could simply
add and drop a clustered index (on any column) during a service window.
If you do that periodically, fragmentation should be under control.
The rest depends on the queries you are using. A 40-byte index in itself
doesn't cause problems. If Insert and Delete performance during the day
is not an issue, then you could safely make the Primary Key index
clustered. And even with the proper fillfactors, Insert performance
shouldn't be a problem.
Having a clustered index can help Select performance on ranges a lot
(for example, the range member = <somevalue>). For high selectivity
Selects, a clustered index does not add much value.
HTH,
Gert-Jan
Jon A wrote:
> I have 1,000,000 records there are unique by member(int),vin(20),stock(18).
> there are 1200 unique members all updates are done by the primary key
> (member,vin,stock). My customer does not want to add an identity column.
> There is only a non clustered unique PK on the table, no clustered index.
> I am wondering which would be better
> 1. Put a unique Clustered PK constraint on the 40 byte fields
> member(int),vin(20),stock(18) (indexes would be large)
> or
> 2 Put a non Clustered index on member id (4 bytes)(let sql add the
> identifier(4 bytes))and put the Primary Key on member(int),vin(20),stock(18)
> as a unique non
> clustered constraint.
> This table has heavy updates(no Pkey fields updated ) and inserts at night
> in batch (15,000 updates 5,000 inserts approx per night).
> There is currently no clustered index and there is no way to control
> fragmentation.
> --
> Thanks,
> Jon A|||An IDENTITY is a lousy candidate for a Clustered Index, almost as horrible
as allowing a table without a Clustered Index at all (a heap).
Heaps are large and, as you've noticed, do not allow you to as easily
control your index rebuild (defragmentaiton) as easily. First of all, the
Clustered Index itself adds no space to the table; its only the use of the
key as a pointer in the other indexes that can grow your non-clustered
indexes. However, consider how large the ROWID is as the alternative to the
clustered index key.
As far as uniqueness, if the Clustered Index is not unique, SQL Server will
make it so by appending a GUID to the key to force it to be unique. Weigh
that against the composite index length, not to mention the size of the heap
alternative.
As to the IDENTITY, if you use one, NEVER make it a clustered index, unless
there are absolutely no other candidates. When will you EVER query an
IDENTITY by range? Also, if you use an IDENTITY, this is normally used as a
surrogate, as in your case, which does not remove the uniqueness requirement
of the business key you would be replacing as the Primary Key. So, better
add an UNIQUE non-Clustered Constraint to the original candidate(s).
Sincerely,
Anthony Thomas
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:422B03AC.344C5898@.toomuchspamalready.nl...
Hi Jon,
I am not really sure what problem you are trying to solve. Is there a
problem?
If the potential problem is fragmentation control, then you could simply
add and drop a clustered index (on any column) during a service window.
If you do that periodically, fragmentation should be under control.
The rest depends on the queries you are using. A 40-byte index in itself
doesn't cause problems. If Insert and Delete performance during the day
is not an issue, then you could safely make the Primary Key index
clustered. And even with the proper fillfactors, Insert performance
shouldn't be a problem.
Having a clustered index can help Select performance on ranges a lot
(for example, the range member = <somevalue>). For high selectivity
Selects, a clustered index does not add much value.
HTH,
Gert-Jan
Jon A wrote:
> I have 1,000,000 records there are unique by
member(int),vin(20),stock(18).
> there are 1200 unique members all updates are done by the primary key
> (member,vin,stock). My customer does not want to add an identity column.
> There is only a non clustered unique PK on the table, no clustered index.
> I am wondering which would be better
> 1. Put a unique Clustered PK constraint on the 40 byte fields
> member(int),vin(20),stock(18) (indexes would be large)
> or
> 2 Put a non Clustered index on member id (4 bytes)(let sql add the
> identifier(4 bytes))and put the Primary Key on
member(int),vin(20),stock(18)
> as a unique non
> clustered constraint.
> This table has heavy updates(no Pkey fields updated ) and inserts at night
> in batch (15,000 updates 5,000 inserts approx per night).
> There is currently no clustered index and there is no way to control
> fragmentation.
> --
> Thanks,
> Jon A|||I added the clustered PK index as (member(int),vin(20),stock(18)). And with
adjustment the page splitting is minimal. But the Table is now 3x the size it
was previously.
My question is this in general terms. What is the problems / overhead of a
non unique clustered index? Is this a bad thing? I have never had a case
where I would do that. But as a result of this problem I am now curious.
"David Gugick" wrote:|||I wouldn't expect changing the PK from non-clustered to clustered to
increase space requirements. In fact, I would think the space would
decrease. Are you certain there are no non-clustered indexes on the table?
You can double check with sp_helpindex.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon A" <JonA@.discussions.microsoft.com> wrote in message
news:E551392D-6A3D-4176-B1EB-7847AB685B51@.microsoft.com...
>I added the clustered PK index as (member(int),vin(20),stock(18)). And with
> adjustment the page splitting is minimal. But the Table is now 3x the size
> it
> was previously.
> My question is this in general terms. What is the problems / overhead of a
> non unique clustered index? Is this a bad thing? I have never had a case
> where I would do that. But as a result of this problem I am now curious.
> "David Gugick" wrote:
>

Non unique Clustered index

I have 1,000,000 records there are unique by member(int),vin(20),stock(18).
there are 1200 unique members all updates are done by the primary key
(member,vin,stock). My customer does not want to add an identity column.
There is only a non clustered unique PK on the table, no clustered index.
I am wondering which would be better
1. Put a unique Clustered PK constraint on the 40 byte fields
member(int),vin(20),stock(18) (indexes would be large)
or
2 Put a non Clustered index on member id (4 bytes)(let sql add the
identifier(4 bytes))and put the Primary Key on member(int),vin(20),stock(18)
as a unique non
clustered constraint.
This table has heavy updates(no Pkey fields updated ) and inserts at night
in batch (15,000 updates 5,000 inserts approx per night).
There is currently no clustered index and there is no way to control
fragmentation.
--
Thanks,
Jon AIf you have only one index on a table, it's usually best to be clustered.
The downside to a wide clustered index is that the clustered index keys are
stored in non-clustered indexes as well. This is not an issue when you
don't have non-clustered indexes, though.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon A" <JonA@.discussions.microsoft.com> wrote in message
news:E1406DAF-36A7-46AB-9EFD-27F942A59B51@.microsoft.com...
>I have 1,000,000 records there are unique by member(int),vin(20),stock(18).
> there are 1200 unique members all updates are done by the primary key
> (member,vin,stock). My customer does not want to add an identity column.
> There is only a non clustered unique PK on the table, no clustered index.
> I am wondering which would be better
> 1. Put a unique Clustered PK constraint on the 40 byte fields
> member(int),vin(20),stock(18) (indexes would be large)
> or
> 2 Put a non Clustered index on member id (4 bytes)(let sql add the
> identifier(4 bytes))and put the Primary Key on
> member(int),vin(20),stock(18)
> as a unique non
> clustered constraint.
> This table has heavy updates(no Pkey fields updated ) and inserts at night
> in batch (15,000 updates 5,000 inserts approx per night).
> There is currently no clustered index and there is no way to control
> fragmentation.
> --
> Thanks,
> Jon A|||Jon A wrote:
> I have 1,000,000 records there are unique by
> member(int),vin(20),stock(18). there are 1200 unique members all
> updates are done by the primary key (member,vin,stock). My customer
> does not want to add an identity column. There is only a non
> clustered unique PK on the table, no clustered index.
> I am wondering which would be better
> 1. Put a unique Clustered PK constraint on the 40 byte fields
> member(int),vin(20),stock(18) (indexes would be large)
> or
> 2 Put a non Clustered index on member id (4 bytes)(let sql add the
> identifier(4 bytes))and put the Primary Key on
> member(int),vin(20),stock(18) as a unique non
> clustered constraint.
> This table has heavy updates(no Pkey fields updated ) and inserts at
> night in batch (15,000 updates 5,000 inserts approx per night).
> There is currently no clustered index and there is no way to control
> fragmentation.
I would argue that because you have a natural key as your PK, you're
likely to see page splitting if you use a clustered index. Since this is
a nightly batch, it may not matter. But then again, having a clustered
index on this table may not matter either, depnding on how the SELECTS
and UPDATES look.
I do agree with Dan. That is, it's best for most, if not all, tables to
have a clustered index. But to add one without a careful investigation
of the table and how it's used is necessary. Just as you would consider
what columns would best make use of a clustered index during database
design, you should perform the same due diligence now.
Look at your queries and table access. See how the data is updated. Is
it more than one row at a time? Is it ever changing a column value that
could be in the clustered index? What do the inserts look like? Are they
adding rows with column values that will most likely cause spage
splitting and slower insert performance at night? Look at the SELECTS on
the table. Do you ever return more than one row at a time? If so, what
criteria determine the rows returned? Do you have ORDER BY statements in
your queries? Do they really need to be there?
If you can post more information about how the table is used, we may be
able to offer more advice.
David Gugick
Imceda Software
www.imceda.com|||Hi Jon,
I am not really sure what problem you are trying to solve. Is there a
problem?
If the potential problem is fragmentation control, then you could simply
add and drop a clustered index (on any column) during a service window.
If you do that periodically, fragmentation should be under control.
The rest depends on the queries you are using. A 40-byte index in itself
doesn't cause problems. If Insert and Delete performance during the day
is not an issue, then you could safely make the Primary Key index
clustered. And even with the proper fillfactors, Insert performance
shouldn't be a problem.
Having a clustered index can help Select performance on ranges a lot
(for example, the range member = <somevalue> ). For high selectivity
Selects, a clustered index does not add much value.
HTH,
Gert-Jan
Jon A wrote:
> I have 1,000,000 records there are unique by member(int),vin(20),stock(18)
.
> there are 1200 unique members all updates are done by the primary key
> (member,vin,stock). My customer does not want to add an identity column.
> There is only a non clustered unique PK on the table, no clustered index.
> I am wondering which would be better
> 1. Put a unique Clustered PK constraint on the 40 byte fields
> member(int),vin(20),stock(18) (indexes would be large)
> or
> 2 Put a non Clustered index on member id (4 bytes)(let sql add the
> identifier(4 bytes))and put the Primary Key on member(int),vin(20),stock(1
8)
> as a unique non
> clustered constraint.
> This table has heavy updates(no Pkey fields updated ) and inserts at night
> in batch (15,000 updates 5,000 inserts approx per night).
> There is currently no clustered index and there is no way to control
> fragmentation.
> --
> Thanks,
> Jon A|||An IDENTITY is a lousy candidate for a Clustered Index, almost as horrible
as allowing a table without a Clustered Index at all (a heap).
Heaps are large and, as you've noticed, do not allow you to as easily
control your index rebuild (defragmentaiton) as easily. First of all, the
Clustered Index itself adds no space to the table; its only the use of the
key as a pointer in the other indexes that can grow your non-clustered
indexes. However, consider how large the ROWID is as the alternative to the
clustered index key.
As far as uniqueness, if the Clustered Index is not unique, SQL Server will
make it so by appending a GUID to the key to force it to be unique. Weigh
that against the composite index length, not to mention the size of the heap
alternative.
As to the IDENTITY, if you use one, NEVER make it a clustered index, unless
there are absolutely no other candidates. When will you EVER query an
IDENTITY by range? Also, if you use an IDENTITY, this is normally used as a
surrogate, as in your case, which does not remove the uniqueness requirement
of the business key you would be replacing as the Primary Key. So, better
add an UNIQUE non-Clustered Constraint to the original candidate(s).
Sincerely,
Anthony Thomas
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:422B03AC.344C5898@.toomuchspamalready.nl...
Hi Jon,
I am not really sure what problem you are trying to solve. Is there a
problem?
If the potential problem is fragmentation control, then you could simply
add and drop a clustered index (on any column) during a service window.
If you do that periodically, fragmentation should be under control.
The rest depends on the queries you are using. A 40-byte index in itself
doesn't cause problems. If Insert and Delete performance during the day
is not an issue, then you could safely make the Primary Key index
clustered. And even with the proper fillfactors, Insert performance
shouldn't be a problem.
Having a clustered index can help Select performance on ranges a lot
(for example, the range member = <somevalue> ). For high selectivity
Selects, a clustered index does not add much value.
HTH,
Gert-Jan
Jon A wrote:
> I have 1,000,000 records there are unique by
member(int),vin(20),stock(18).
> there are 1200 unique members all updates are done by the primary key
> (member,vin,stock). My customer does not want to add an identity column.
> There is only a non clustered unique PK on the table, no clustered index.
> I am wondering which would be better
> 1. Put a unique Clustered PK constraint on the 40 byte fields
> member(int),vin(20),stock(18) (indexes would be large)
> or
> 2 Put a non Clustered index on member id (4 bytes)(let sql add the
> identifier(4 bytes))and put the Primary Key on
member(int),vin(20),stock(18)reen">
> as a unique non
> clustered constraint.
> This table has heavy updates(no Pkey fields updated ) and inserts at night
> in batch (15,000 updates 5,000 inserts approx per night).
> There is currently no clustered index and there is no way to control
> fragmentation.
> --
> Thanks,
> Jon A|||I added the clustered PK index as (member(int),vin(20),stock(18)). And with
adjustment the page splitting is minimal. But the Table is now 3x the size i
t
was previously.
My question is this in general terms. What is the problems / overhead of a
non unique clustered index? Is this a bad thing? I have never had a case
where I would do that. But as a result of this problem I am now curious.
"David Gugick" wrote:|||I wouldn't expect changing the PK from non-clustered to clustered to
increase space requirements. In fact, I would think the space would
decrease. Are you certain there are no non-clustered indexes on the table?
You can double check with sp_helpindex.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon A" <JonA@.discussions.microsoft.com> wrote in message
news:E551392D-6A3D-4176-B1EB-7847AB685B51@.microsoft.com...
>I added the clustered PK index as (member(int),vin(20),stock(18)). And with
> adjustment the page splitting is minimal. But the Table is now 3x the size
> it
> was previously.
> My question is this in general terms. What is the problems / overhead of a
> non unique clustered index? Is this a bad thing? I have never had a case
> where I would do that. But as a result of this problem I am now curious.
> "David Gugick" wrote:
>

NOCACHE equivalent ?

Oracle query :
create table test(sno int, sno1 int) NOCACHE
Is there any equivalent in SQL Server for the above (specifically for the NOCAHE syntax)
Please advice,
Thanks,
SamNOCACHE is the default in SQLServer.

More interesting would be the CACHE statement in a create.
In SQLServer it is not possible directly afaik (correct me if I am wrong)
You can however use DBCC PINTABLE(database_id, table_id)
Look in the documentation for the use and working.|||Hai..

If u really want a table to be placed in cache then u can make the table as pintable so that in remains in the cache itslef. Surely this will increase the performance of that particular table provided the table is small in size. If the size of the table is more, then it will require so much memory, more over it will adversely affect the performace of other tables.

So analyse ur requirement and if u really want to place the table in cache then u can use the syntax as specfied by Woosterom

with regards
Sudar