Creating Unique Indexes on Partition table..

In partition tables, SQL Server forces to include the Partition column as part of unique index/unique constraint or primary key. Is there a way to over-ride this option.

We have a table, which is partitioned on a Calculated Column (generated using custom logic). Also we need to enforce uniqueness based on few other columns. But SQL Server does not allow indexes/contraints on these columns without including the partition column.

Thanks for your help.

Well, you can create a non-aligned unique index on a partitioned table, but doing that will in essence not allow meta-data-only partition switching. Depending on your needs, that may be ok or not. You could also use a DML trigger to enforce uniqueness instead of a unique index. Or, if your environment allows for the use of them, you could always create an indexed view on the table and create a unique index on that view that will in essence enforce the uniqueness on the corresponding table as well.

For more information, see the following topic in SQL 2005 BOL:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/5af648b1-8454-4c17-a47e-f9656572440b.htm

HTH,

Creating Optional Parameter dropdown lists

I have a parameter that acted as optional before I changed it to a dropdown
populated by a query. Now the report forces me to select something in spite
of the fact that I have selected ALLOW BLANK and have tried to use blanks
spaces or other things as a default values. It seems like it won't recognize
a default value when the parameter is a dropdown.
Is this by design, is something acting in error, or am I doing something
wrong?You will need to also select "Allow Null" for the parameter and it should
then default to NULL and auto execute the report.
You may also need to pass in a NULL value from your data set that populates
the optional dropdown list. You can do this by using a UNION statment in the
dataset that will return the NULL value, such as:
select MyID, MyDescrtiption from MyTable
UNION
Select Null,Null
I hope this helps.
--
Rodney Landrum - Author, "Pro SQL Server Reporting Services" (Apress)
http://www.apress.com
"John Marsh" <John Marsh@.discussions.microsoft.com> wrote in message
news:6065AC4F-F402-450B-AFEA-A977CFE152AC@.microsoft.com...
>I have a parameter that acted as optional before I changed it to a dropdown
> populated by a query. Now the report forces me to select something in
> spite
> of the fact that I have selected ALLOW BLANK and have tried to use blanks
> spaces or other things as a default values. It seems like it won't
> recognize
> a default value when the parameter is a dropdown.
> Is this by design, is something acting in error, or am I doing something
> wrong?