Parameterized Expression: available

Showing posts with label available. Show all posts

Friday, March 30, 2012

Partial Indexes

Anyone knows when Partial Indexes (indexes created on the first n characters
of a char or varchar field) will be available in SQL Server?
Does SQL Server 2005 have this feature?
TIA
Eduardo SicouretYou can simulate it by creating an index on a computed column:
ALTER TABLE YourTable
ADD First10Characters AS (LEFT(YourColumn, 10))
CREATE INDEX IX_First10Characters
ON YourTable (First10Characters)
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Eduardo Sicouret" <esicouret> wrote in message
news:OGROGUdfGHA.3652@.TK2MSFTNGP02.phx.gbl...
> Anyone knows when Partial Indexes (indexes created on the first n
> characters of a char or varchar field) will be available in SQL Server?
> Does SQL Server 2005 have this feature?
> TIA
> Eduardo Sicouret
>|||Thanks for answering.
I know that workaround is available, but my specific question is if "partial
indexes" are available in SQL Server 2005?
I don't have disk space to insert another column to my tables.
Regards,
Eduardo Sicouret
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> escribió en el
mensaje news:O2QRGcdfGHA.3488@.TK2MSFTNGP02.phx.gbl...
> You can simulate it by creating an index on a computed column:
> ALTER TABLE YourTable
> ADD First10Characters AS (LEFT(YourColumn, 10))
> CREATE INDEX IX_First10Characters
> ON YourTable (First10Characters)
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Eduardo Sicouret" <esicouret> wrote in message
> news:OGROGUdfGHA.3652@.TK2MSFTNGP02.phx.gbl...
>> Anyone knows when Partial Indexes (indexes created on the first n
>> characters of a char or varchar field) will be available in SQL Server?
>> Does SQL Server 2005 have this feature?
>> TIA
>> Eduardo Sicouret
>|||Hi Eduardo
No, there is no such thing as partial indexes.
However, the disk space requirement for the partial index would be the same
as the disk space required for the index on the computed column. So how do
you figure you have disk space for an index but don't have disk space for
the computed column?
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Eduardo Sicouret" <esicouret> wrote in message
news:urRqyfdfGHA.4464@.TK2MSFTNGP04.phx.gbl...
> Thanks for answering.
> I know that workaround is available, but my specific question is if
> "partial indexes" are available in SQL Server 2005?
> I don't have disk space to insert another column to my tables.
> Regards,
> Eduardo Sicouret
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> escribió en el
> mensaje news:O2QRGcdfGHA.3488@.TK2MSFTNGP02.phx.gbl...
>> You can simulate it by creating an index on a computed column:
>> ALTER TABLE YourTable
>> ADD First10Characters AS (LEFT(YourColumn, 10))
>> CREATE INDEX IX_First10Characters
>> ON YourTable (First10Characters)
>>
>> --
>> Adam Machanic
>> Pro SQL Server 2005, available now
>> http://www.apress.com/book/bookDisplay.html?bID=457
>> --
>>
>> "Eduardo Sicouret" <esicouret> wrote in message
>> news:OGROGUdfGHA.3652@.TK2MSFTNGP02.phx.gbl...
>> Anyone knows when Partial Indexes (indexes created on the first n
>> characters of a char or varchar field) will be available in SQL Server?
>> Does SQL Server 2005 have this feature?
>> TIA
>> Eduardo Sicouret
>>
>|||I will have the space of the computed column + the space of the index on the
computed column...
That will be 2 times the computed column...
Regards,
Eduardo Sicouret
"Kalen Delaney" <replies@.public_newsgroups.com> escribió en el mensaje
news:upIMcqdfGHA.4464@.TK2MSFTNGP04.phx.gbl...
> Hi Eduardo
> No, there is no such thing as partial indexes.
> However, the disk space requirement for the partial index would be the
> same as the disk space required for the index on the computed column. So
> how do you figure you have disk space for an index but don't have disk
> space for the computed column?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Eduardo Sicouret" <esicouret> wrote in message
> news:urRqyfdfGHA.4464@.TK2MSFTNGP04.phx.gbl...
>> Thanks for answering.
>> I know that workaround is available, but my specific question is if
>> "partial indexes" are available in SQL Server 2005?
>> I don't have disk space to insert another column to my tables.
>> Regards,
>> Eduardo Sicouret
>> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> escribió en el
>> mensaje news:O2QRGcdfGHA.3488@.TK2MSFTNGP02.phx.gbl...
>> You can simulate it by creating an index on a computed column:
>> ALTER TABLE YourTable
>> ADD First10Characters AS (LEFT(YourColumn, 10))
>> CREATE INDEX IX_First10Characters
>> ON YourTable (First10Characters)
>>
>> --
>> Adam Machanic
>> Pro SQL Server 2005, available now
>> http://www.apress.com/book/bookDisplay.html?bID=457
>> --
>>
>> "Eduardo Sicouret" <esicouret> wrote in message
>> news:OGROGUdfGHA.3652@.TK2MSFTNGP02.phx.gbl...
>> Anyone knows when Partial Indexes (indexes created on the first n
>> characters of a char or varchar field) will be available in SQL Server?
>> Does SQL Server 2005 have this feature?
>> TIA
>> Eduardo Sicouret
>>
>>
>|||Untrue. Computed columns are not persisted, unless you use SQL Server
2005's "persisted computed column" feature, explicitly (by adding the
keyword PERSISTED after the column definition).
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Eduardo Sicouret" <esicouret> wrote in message
news:umHRwFefGHA.4828@.TK2MSFTNGP05.phx.gbl...
>I will have the space of the computed column + the space of the index on
>the computed column...
> That will be 2 times the computed column...
> Regards,
> Eduardo Sicouret
> "Kalen Delaney" <replies@.public_newsgroups.com> escribió en el mensaje
> news:upIMcqdfGHA.4464@.TK2MSFTNGP04.phx.gbl...
>> Hi Eduardo
>> No, there is no such thing as partial indexes.
>> However, the disk space requirement for the partial index would be the
>> same as the disk space required for the index on the computed column. So
>> how do you figure you have disk space for an index but don't have disk
>> space for the computed column?
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.solidqualitylearning.com
>>
>> "Eduardo Sicouret" <esicouret> wrote in message
>> news:urRqyfdfGHA.4464@.TK2MSFTNGP04.phx.gbl...
>> Thanks for answering.
>> I know that workaround is available, but my specific question is if
>> "partial indexes" are available in SQL Server 2005?
>> I don't have disk space to insert another column to my tables.
>> Regards,
>> Eduardo Sicouret
>> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> escribió en el
>> mensaje news:O2QRGcdfGHA.3488@.TK2MSFTNGP02.phx.gbl...
>> You can simulate it by creating an index on a computed column:
>> ALTER TABLE YourTable
>> ADD First10Characters AS (LEFT(YourColumn, 10))
>> CREATE INDEX IX_First10Characters
>> ON YourTable (First10Characters)
>>
>> --
>> Adam Machanic
>> Pro SQL Server 2005, available now
>> http://www.apress.com/book/bookDisplay.html?bID=457
>> --
>>
>> "Eduardo Sicouret" <esicouret> wrote in message
>> news:OGROGUdfGHA.3652@.TK2MSFTNGP02.phx.gbl...
>> Anyone knows when Partial Indexes (indexes created on the first n
>> characters of a char or varchar field) will be available in SQL
>> Server?
>> Does SQL Server 2005 have this feature?
>> TIA
>> Eduardo Sicouret
>>
>>
>>
>|||The computed column takes no space until you create the index on it.
How big is the partial column you think you need to index and how are
computing the size of the index?
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Eduardo Sicouret" <esicouret> wrote in message
news:umHRwFefGHA.4828@.TK2MSFTNGP05.phx.gbl...
>I will have the space of the computed column + the space of the index on
>the computed column...
> That will be 2 times the computed column...
> Regards,
> Eduardo Sicouret
> "Kalen Delaney" <replies@.public_newsgroups.com> escribió en el mensaje
> news:upIMcqdfGHA.4464@.TK2MSFTNGP04.phx.gbl...
>> Hi Eduardo
>> No, there is no such thing as partial indexes.
>> However, the disk space requirement for the partial index would be the
>> same as the disk space required for the index on the computed column. So
>> how do you figure you have disk space for an index but don't have disk
>> space for the computed column?
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.solidqualitylearning.com
>>
>> "Eduardo Sicouret" <esicouret> wrote in message
>> news:urRqyfdfGHA.4464@.TK2MSFTNGP04.phx.gbl...
>> Thanks for answering.
>> I know that workaround is available, but my specific question is if
>> "partial indexes" are available in SQL Server 2005?
>> I don't have disk space to insert another column to my tables.
>> Regards,
>> Eduardo Sicouret
>> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> escribió en el
>> mensaje news:O2QRGcdfGHA.3488@.TK2MSFTNGP02.phx.gbl...
>> You can simulate it by creating an index on a computed column:
>> ALTER TABLE YourTable
>> ADD First10Characters AS (LEFT(YourColumn, 10))
>> CREATE INDEX IX_First10Characters
>> ON YourTable (First10Characters)
>>
>> --
>> Adam Machanic
>> Pro SQL Server 2005, available now
>> http://www.apress.com/book/bookDisplay.html?bID=457
>> --
>>
>> "Eduardo Sicouret" <esicouret> wrote in message
>> news:OGROGUdfGHA.3652@.TK2MSFTNGP02.phx.gbl...
>> Anyone knows when Partial Indexes (indexes created on the first n
>> characters of a char or varchar field) will be available in SQL
>> Server?
>> Does SQL Server 2005 have this feature?
>> TIA
>> Eduardo Sicouret
>>
>>
>>
>|||didn't know that computed columns takes no space...
I will test this workaround to see if it works. The problem is that my
tables are 2 million records big so increasing a column has a considerably
big impact.
Regards,
Eduardo Sicouret
"Kalen Delaney" <replies@.public_newsgroups.com> escribió en el mensaje
news:u7juBLefGHA.3456@.TK2MSFTNGP05.phx.gbl...
> The computed column takes no space until you create the index on it.
> How big is the partial column you think you need to index and how are
> computing the size of the index?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Eduardo Sicouret" <esicouret> wrote in message
> news:umHRwFefGHA.4828@.TK2MSFTNGP05.phx.gbl...
>>I will have the space of the computed column + the space of the index on
>>the computed column...
>> That will be 2 times the computed column...
>> Regards,
>> Eduardo Sicouret
>> "Kalen Delaney" <replies@.public_newsgroups.com> escribió en el mensaje
>> news:upIMcqdfGHA.4464@.TK2MSFTNGP04.phx.gbl...
>> Hi Eduardo
>> No, there is no such thing as partial indexes.
>> However, the disk space requirement for the partial index would be the
>> same as the disk space required for the index on the computed column. So
>> how do you figure you have disk space for an index but don't have disk
>> space for the computed column?
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.solidqualitylearning.com
>>
>> "Eduardo Sicouret" <esicouret> wrote in message
>> news:urRqyfdfGHA.4464@.TK2MSFTNGP04.phx.gbl...
>> Thanks for answering.
>> I know that workaround is available, but my specific question is if
>> "partial indexes" are available in SQL Server 2005?
>> I don't have disk space to insert another column to my tables.
>> Regards,
>> Eduardo Sicouret
>> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> escribió en el
>> mensaje news:O2QRGcdfGHA.3488@.TK2MSFTNGP02.phx.gbl...
>> You can simulate it by creating an index on a computed column:
>> ALTER TABLE YourTable
>> ADD First10Characters AS (LEFT(YourColumn, 10))
>> CREATE INDEX IX_First10Characters
>> ON YourTable (First10Characters)
>>
>> --
>> Adam Machanic
>> Pro SQL Server 2005, available now
>> http://www.apress.com/book/bookDisplay.html?bID=457
>> --
>>
>> "Eduardo Sicouret" <esicouret> wrote in message
>> news:OGROGUdfGHA.3652@.TK2MSFTNGP02.phx.gbl...
>> Anyone knows when Partial Indexes (indexes created on the first n
>> characters of a char or varchar field) will be available in SQL
>> Server?
>> Does SQL Server 2005 have this feature?
>> TIA
>> Eduardo Sicouret
>>
>>
>>
>>
>|||2 million is not all that may rows at all. I occasionally create tables with
2 million rows just for testing purposes.
How big is the partial column you want to index? How much free space do you
have?
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Eduardo Sicouret" <esicouret> wrote in message
news:uQy0PlefGHA.4276@.TK2MSFTNGP03.phx.gbl...
> didn't know that computed columns takes no space...
> I will test this workaround to see if it works. The problem is that my
> tables are 2 million records big so increasing a column has a considerably
> big impact.
> Regards,
> Eduardo Sicouret
> "Kalen Delaney" <replies@.public_newsgroups.com> escribió en el mensaje
> news:u7juBLefGHA.3456@.TK2MSFTNGP05.phx.gbl...
>> The computed column takes no space until you create the index on it.
>> How big is the partial column you think you need to index and how are
>> computing the size of the index?
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.solidqualitylearning.com
>>
>> "Eduardo Sicouret" <esicouret> wrote in message
>> news:umHRwFefGHA.4828@.TK2MSFTNGP05.phx.gbl...
>>I will have the space of the computed column + the space of the index on
>>the computed column...
>> That will be 2 times the computed column...
>> Regards,
>> Eduardo Sicouret
>> "Kalen Delaney" <replies@.public_newsgroups.com> escribió en el mensaje
>> news:upIMcqdfGHA.4464@.TK2MSFTNGP04.phx.gbl...
>> Hi Eduardo
>> No, there is no such thing as partial indexes.
>> However, the disk space requirement for the partial index would be the
>> same as the disk space required for the index on the computed column.
>> So how do you figure you have disk space for an index but don't have
>> disk space for the computed column?
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.solidqualitylearning.com
>>
>> "Eduardo Sicouret" <esicouret> wrote in message
>> news:urRqyfdfGHA.4464@.TK2MSFTNGP04.phx.gbl...
>> Thanks for answering.
>> I know that workaround is available, but my specific question is if
>> "partial indexes" are available in SQL Server 2005?
>> I don't have disk space to insert another column to my tables.
>> Regards,
>> Eduardo Sicouret
>> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> escribió en el
>> mensaje news:O2QRGcdfGHA.3488@.TK2MSFTNGP02.phx.gbl...
>> You can simulate it by creating an index on a computed column:
>> ALTER TABLE YourTable
>> ADD First10Characters AS (LEFT(YourColumn, 10))
>> CREATE INDEX IX_First10Characters
>> ON YourTable (First10Characters)
>>
>> --
>> Adam Machanic
>> Pro SQL Server 2005, available now
>> http://www.apress.com/book/bookDisplay.html?bID=457
>> --
>>
>> "Eduardo Sicouret" <esicouret> wrote in message
>> news:OGROGUdfGHA.3652@.TK2MSFTNGP02.phx.gbl...
>>> Anyone knows when Partial Indexes (indexes created on the first n
>>> characters of a char or varchar field) will be available in SQL
>>> Server?
>>>
>>> Does SQL Server 2005 have this feature?
>>>
>>> TIA
>>>
>>> Eduardo Sicouret
>>>
>>
>>
>>
>>
>>
>|||Unfortunately I have only one 16GB disk and 700MB free after truncating and
shrinking the log. And this disk is also for the O.S.
The main table is accesed several times a second, so increasing a column is
a difficult task. table is 1.7GB big.
To reduce the indexes size I thought of a solution like partial indexing.
regards...
Eduardo Sicouret
"Kalen Delaney" <replies@.public_newsgroups.com> escribió en el mensaje
news:u0cPCrefGHA.2188@.TK2MSFTNGP04.phx.gbl...
>2 million is not all that may rows at all. I occasionally create tables
>with 2 million rows just for testing purposes.
> How big is the partial column you want to index? How much free space do
> you have?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Eduardo Sicouret" <esicouret> wrote in message
> news:uQy0PlefGHA.4276@.TK2MSFTNGP03.phx.gbl...
>> didn't know that computed columns takes no space...
>> I will test this workaround to see if it works. The problem is that my
>> tables are 2 million records big so increasing a column has a
>> considerably big impact.
>> Regards,
>> Eduardo Sicouret
>> "Kalen Delaney" <replies@.public_newsgroups.com> escribió en el mensaje
>> news:u7juBLefGHA.3456@.TK2MSFTNGP05.phx.gbl...
>> The computed column takes no space until you create the index on it.
>> How big is the partial column you think you need to index and how are
>> computing the size of the index?
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.solidqualitylearning.com
>>
>> "Eduardo Sicouret" <esicouret> wrote in message
>> news:umHRwFefGHA.4828@.TK2MSFTNGP05.phx.gbl...
>>I will have the space of the computed column + the space of the index on
>>the computed column...
>> That will be 2 times the computed column...
>> Regards,
>> Eduardo Sicouret
>> "Kalen Delaney" <replies@.public_newsgroups.com> escribió en el mensaje
>> news:upIMcqdfGHA.4464@.TK2MSFTNGP04.phx.gbl...
>> Hi Eduardo
>> No, there is no such thing as partial indexes.
>> However, the disk space requirement for the partial index would be the
>> same as the disk space required for the index on the computed column.
>> So how do you figure you have disk space for an index but don't have
>> disk space for the computed column?
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.solidqualitylearning.com
>>
>> "Eduardo Sicouret" <esicouret> wrote in message
>> news:urRqyfdfGHA.4464@.TK2MSFTNGP04.phx.gbl...
>> Thanks for answering.
>> I know that workaround is available, but my specific question is if
>> "partial indexes" are available in SQL Server 2005?
>> I don't have disk space to insert another column to my tables.
>> Regards,
>> Eduardo Sicouret
>> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> escribió en
>> el mensaje news:O2QRGcdfGHA.3488@.TK2MSFTNGP02.phx.gbl...
>>> You can simulate it by creating an index on a computed column:
>>>
>>> ALTER TABLE YourTable
>>> ADD First10Characters AS (LEFT(YourColumn, 10))
>>>
>>> CREATE INDEX IX_First10Characters
>>> ON YourTable (First10Characters)
>>>
>>>
>>> --
>>> Adam Machanic
>>> Pro SQL Server 2005, available now
>>> http://www.apress.com/book/bookDisplay.html?bID=457
>>> --
>>>
>>>
>>> "Eduardo Sicouret" <esicouret> wrote in message
>>> news:OGROGUdfGHA.3652@.TK2MSFTNGP02.phx.gbl...
>>> Anyone knows when Partial Indexes (indexes created on the first n
>>> characters of a char or varchar field) will be available in SQL
>>> Server?
>>>
>>> Does SQL Server 2005 have this feature?
>>>
>>> TIA
>>>
>>> Eduardo Sicouret
>>>
>>>
>>>
>>
>>
>>
>>
>>
>
>|||This is a multi-part message in MIME format.
--050308010900010509020903
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
I'd be looking to add more disk to your server. I was going to suggest
considering increasing the fillfactor on your clustered index(es) in
order to compact the tables a bit (and hence save some space) but this
would most likely result in more index fragmentation and in any case to
recreate an index you need about double the disk space used by the index
(which you don't have by the sounds of it).
This disk space issue is a bit of a problem. How would you rebuild your
indexes if you don't have enough disk space to do so? If I were you I'd
look at clearing some stuff off that disk to give more disk space,
checking fragmentation of your indexes (DBCC SHOWCONTIG (SQL 2000) or
sys.dm_db_index_physical_stats (SQL 2005)), possibly defragmenting your
indexes (DBCC INDEXDEFRAG (SQL 2000) or ALTER INDEX ... REORGANIZE (SQL
2005)) or rebuilding your indexes (DBCC DBREINDEX (SQL 2000) or ALTER
INDEX ... REBUILD (SQL 2005)). Is there any old data that you can purge
from your DB (after doing a full DB backup of course)?
Basically, not even having enough disk space to rebuild your largest
index is an issue. Disk space is dirt cheap these days - you ought to
be able to pick up a couple hundred gig (SATA) or at least 70G (SCSI)
for peanuts. Have a brief 1 hour outage to add your new disk, bring the
SQL instance back up, and add another file (on the new disk) to your
filegroup(s) to allow more room for this stuff. You might even consider
shuffling your data around or moving the entire DB to the new disk (with
sp_detach_db/sp_attach_db) thereby leaving your original disk to be OS &
SQL binaries only. I'd make acquiring more hardware a priority...that's
my 2c.
--
*mike hodgson*
http://sqlnerd.blogspot.com
Eduardo Sicouret wrote:
>Unfortunately I have only one 16GB disk and 700MB free after truncating and
>shrinking the log. And this disk is also for the O.S.
>The main table is accesed several times a second, so increasing a column is
>a difficult task. table is 1.7GB big.
>To reduce the indexes size I thought of a solution like partial indexing.
>regards...
>Eduardo Sicouret
>"Kalen Delaney" <replies@.public_newsgroups.com> escribió en el mensaje
>news:u0cPCrefGHA.2188@.TK2MSFTNGP04.phx.gbl...
>
>>2 million is not all that may rows at all. I occasionally create tables
>>with 2 million rows just for testing purposes.
>>How big is the partial column you want to index? How much free space do
>>you have?
>>--
>>HTH
>>Kalen Delaney, SQL Server MVP
>>www.solidqualitylearning.com
>>
>>"Eduardo Sicouret" <esicouret> wrote in message
>>news:uQy0PlefGHA.4276@.TK2MSFTNGP03.phx.gbl...
>>
>>didn't know that computed columns takes no space...
>>I will test this workaround to see if it works. The problem is that my
>>tables are 2 million records big so increasing a column has a
>>considerably big impact.
>>Regards,
>>Eduardo Sicouret
>>"Kalen Delaney" <replies@.public_newsgroups.com> escribió en el mensaje
>>news:u7juBLefGHA.3456@.TK2MSFTNGP05.phx.gbl...
>>
>>The computed column takes no space until you create the index on it.
>>How big is the partial column you think you need to index and how are
>>computing the size of the index?
>>--
>>HTH
>>Kalen Delaney, SQL Server MVP
>>www.solidqualitylearning.com
>>
>>"Eduardo Sicouret" <esicouret> wrote in message
>>news:umHRwFefGHA.4828@.TK2MSFTNGP05.phx.gbl...
>>
>>I will have the space of the computed column + the space of the index on
>>the computed column...
>>That will be 2 times the computed column...
>>Regards,
>>Eduardo Sicouret
>>"Kalen Delaney" <replies@.public_newsgroups.com> escribió en el mensaje
>>news:upIMcqdfGHA.4464@.TK2MSFTNGP04.phx.gbl...
>>
>>Hi Eduardo
>>No, there is no such thing as partial indexes.
>>However, the disk space requirement for the partial index would be the
>>same as the disk space required for the index on the computed column.
>>So how do you figure you have disk space for an index but don't have
>>disk space for the computed column?
>>--
>>HTH
>>Kalen Delaney, SQL Server MVP
>>www.solidqualitylearning.com
>>
>>"Eduardo Sicouret" <esicouret> wrote in message
>>news:urRqyfdfGHA.4464@.TK2MSFTNGP04.phx.gbl...
>>
>>>Thanks for answering.
>>>
>>>I know that workaround is available, but my specific question is if
>>>"partial indexes" are available in SQL Server 2005?
>>>
>>>I don't have disk space to insert another column to my tables.
>>>
>>>Regards,
>>>
>>>Eduardo Sicouret
>>>"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> escribió en
>>>el mensaje news:O2QRGcdfGHA.3488@.TK2MSFTNGP02.phx.gbl...
>>>
>>>
>>>You can simulate it by creating an index on a computed column:
>>>
>>>ALTER TABLE YourTable
>>>ADD First10Characters AS (LEFT(YourColumn, 10))
>>>
>>>CREATE INDEX IX_First10Characters
>>>ON YourTable (First10Characters)
>>>
>>>
>>>--
>>>Adam Machanic
>>>Pro SQL Server 2005, available now
>>>http://www.apress.com/book/bookDisplay.html?bID=457
>>>--
>>>
>>>
>>>"Eduardo Sicouret" <esicouret> wrote in message
>>>news:OGROGUdfGHA.3652@.TK2MSFTNGP02.phx.gbl...
>>>
>>>
>>>Anyone knows when Partial Indexes (indexes created on the first n
>>>characters of a char or varchar field) will be available in SQL
>>>Server?
>>>
>>>Does SQL Server 2005 have this feature?
>>>
>>>TIA
>>>
>>>Eduardo Sicouret
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>>
>
>
--050308010900010509020903
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>I'd be looking to add more disk to your server. I was going to
suggest considering increasing the fillfactor on your clustered
index(es) in order to compact the tables a bit (and hence save some
space) but this would most likely result in more index fragmentation
and in any case to recreate an index you need about double the disk
space used by the index (which you don't have by the sounds of it). 
 
This disk space issue is a bit of a problem. How would you rebuild
your indexes if you don't have enough disk space to do so? If I were
you I'd look at clearing some stuff off that disk to give more disk
space, checking fragmentation of your indexes (DBCC SHOWCONTIG (SQL
2000) or sys.dm_db_index_physical_stats (SQL 2005)), possibly
defragmenting your indexes (DBCC INDEXDEFRAG (SQL 2000) or ALTER INDEX
... REORGANIZE (SQL 2005)) or rebuilding your indexes (DBCC DBREINDEX
(SQL 2000) or ALTER INDEX ... REBUILD (SQL 2005)). Is there any old
data that you can purge from your DB (after doing a full DB backup of
course)? 
 
Basically, not even having enough disk space to rebuild your largest
index is an issue. Disk space is dirt cheap these days - you ought to
be able to pick up a couple hundred gig (SATA) or at least 70G (SCSI)
for peanuts. Have a brief 1 hour outage to add your new disk, bring
the SQL instance back up, and add another file (on the new disk) to
your filegroup(s) to allow more room for this stuff. You might even
consider shuffling your data around or moving the entire DB to the new
disk (with sp_detach_db/sp_attach_db) thereby leaving your original
disk to be OS & SQL binaries only. I'd make acquiring more
hardware a priority...that's my 2c. 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson 
<a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
Eduardo Sicouret wrote:
<blockquote cite="miduwkNFBffGHA.4568@.TK2MSFTNGP03.phx.gbl" type="cite">
<pre wrap="">Unfortunately I have only one 16GB disk and 700MB free after truncating and
shrinking the log. And this disk is also for the O.S.
The main table is accesed several times a second, so increasing a column is
a difficult task. table is 1.7GB big.
To reduce the indexes size I thought of a solution like partial indexing.
regards...
Eduardo Sicouret
"Kalen Delaney" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:replies@.public_newsgroups.com"><replies@.public_newsgroups.com></a> escribió en el mensaje
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:u0cPCrefGHA.2188@.TK2MSFTNGP04.phx.gbl">news:u0cPCrefGHA.2188@.TK2MSFTNGP04.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">2 million is not all that may rows at all. I occasionally create tables
with 2 million rows just for testing purposes.
How big is the partial column you want to index? How much free space do
you have?
--
HTH
Kalen Delaney, SQL Server MVP
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=www.solidqualitylearning.com</a>">http://www.solidqualitylearning.com">www.solidqualitylearning.com</a>
"Eduardo Sicouret" <esicouret> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:uQy0PlefGHA.4276@.TK2MSFTNGP03.phx.gbl">news:uQy0PlefGHA.4276@.TK2MSFTNGP03.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">didn't know that computed columns takes no space...
I will test this workaround to see if it works. The problem is that my
tables are 2 million records big so increasing a column has a
considerably big impact.
Regards,
Eduardo Sicouret
"Kalen Delaney" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:replies@.public_newsgroups.com"><replies@.public_newsgroups.com></a> escribió en el mensaje
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:u7juBLefGHA.3456@.TK2MSFTNGP05.phx.gbl">news:u7juBLefGHA.3456@.TK2MSFTNGP05.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">The computed column takes no space until you create the index on it.
How big is the partial column you think you need to index and how are
computing the size of the index?
--
HTH
Kalen Delaney, SQL Server MVP
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=www.solidqualitylearning.com</a>">http://www.solidqualitylearning.com">www.solidqualitylearning.com</a>
"Eduardo Sicouret" <esicouret> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:umHRwFefGHA.4828@.TK2MSFTNGP05.phx.gbl">news:umHRwFefGHA.4828@.TK2MSFTNGP05.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">I will have the space of the computed column + the space of the index on
the computed column...
That will be 2 times the computed column...
Regards,
Eduardo Sicouret
"Kalen Delaney" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:replies@.public_newsgroups.com"><replies@.public_newsgroups.com></a> escribió en el mensaje
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:upIMcqdfGHA.4464@.TK2MSFTNGP04.phx.gbl">news:upIMcqdfGHA.4464@.TK2MSFTNGP04.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">Hi Eduardo
No, there is no such thing as partial indexes.
However, the disk space requirement for the partial index would be the
same as the disk space required for the index on the computed column.
So how do you figure you have disk space for an index but don't have
disk space for the computed column?
--
HTH
Kalen Delaney, SQL Server MVP
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=www.solidqualitylearning.com</a>">http://www.solidqualitylearning.com">www.solidqualitylearning.com</a>
"Eduardo Sicouret" <esicouret> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:urRqyfdfGHA.4464@.TK2MSFTNGP04.phx.gbl">news:urRqyfdfGHA.4464@.TK2MSFTNGP04.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">Thanks for answering.
I know that workaround is available, but my specific question is if
"partial indexes" are available in SQL Server 2005?
I don't have disk space to insert another column to my tables.
Regards,
Eduardo Sicouret
"Adam Machanic" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:amachanic@.hotmail._removetoemail_.com"><amachanic@.hotmail._removetoemail_.com></a> escribió en
el mensaje <a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:O2QRGcdfGHA.3488@.TK2MSFTNGP02.phx.gbl">news:O2QRGcdfGHA.3488@.TK2MSFTNGP02.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">You can simulate it by creating an index on a computed column:
ALTER TABLE YourTable
ADD First10Characters AS (LEFT(YourColumn, 10))
CREATE INDEX IX_First10Characters
ON YourTable (First10Characters)
Adam Machanic
Pro SQL Server 2005, available now
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://www.apress.com/book/bookDisplay.html?bID=457</a>">http://www.apress.com/book/bookDisplay.html?bID=457">http://www.apress.com/book/bookDisplay.html?bID=457</a>
--
"Eduardo Sicouret" <esicouret> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:OGROGUdfGHA.3652@.TK2MSFTNGP02.phx.gbl">news:OGROGUdfGHA.3652@.TK2MSFTNGP02.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">Anyone knows when Partial Indexes (indexes created on the first n
characters of a char or varchar field) will be available in SQL
Server?
Does SQL Server 2005 have this feature?
TIA
Eduardo Sicouret
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
<pre wrap=""><!-->
</pre>
</blockquote>
</body>
</html>
--050308010900010509020903--

Friday, March 23, 2012

Partitioning Questions

I'm looking at using table partitioning in SQL 2005 and have a few
questions.
a.. Is partitioning really only available in Enterprise Edition? I have
Std Edition and when I tried to create a partitioning function, it told me
"Partition function can only be created in Enterprise edition of SQL Server.
Only Enterprise edition of SQL Server supports partitioning." Yet I don't
see this same statement anywhere in the documentation.
b.. All of the articles I'm reading on partitioning talk about how to
create a new table and use partitioning. How do I partition an existing
table?
c.. Does partitioning work equally well with datetime and int columns?
d.. Will partitioning give me better performance than say moving my
indexes to another filegroup?
Thanks, AndréHi André
Take a look in BOL at
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/81f3e917-884a-4cc8-aca2-0a5fea89f355.htm
"Features supported by the Editions of SQL Server 2005
--
HTH
Kalen Delaney, SQL Server MVP
"Andre" <no@.spam.com> wrote in message
news:%23be3$PZhGHA.3496@.TK2MSFTNGP04.phx.gbl...
> I'm looking at using table partitioning in SQL 2005 and have a few
> questions.
> a.. Is partitioning really only available in Enterprise Edition? I have
> Std Edition and when I tried to create a partitioning function, it told me
> "Partition function can only be created in Enterprise edition of SQL
> Server. Only Enterprise edition of SQL Server supports partitioning." Yet
> I don't see this same statement anywhere in the documentation.
> b.. All of the articles I'm reading on partitioning talk about how to
> create a new table and use partitioning. How do I partition an existing
> table?
> c.. Does partitioning work equally well with datetime and int columns?
> d.. Will partitioning give me better performance than say moving my
> indexes to another filegroup?
>
> Thanks, André
>
>|||Please check the topic "Modifying Partitioned Tables and Indexes"
in BOL at
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/1b85f670-74aa-4a4b-9a88-3352f1d274d1.htm
It gives details for each of the following:
You can modify partitioned tables and indexes in the following ways:
a.. Modify a partition function to re-partition any tables or indexes that
participate in it.
b.. Modify a partition scheme to designate a filegroup to hold a
newly-added partition.
c.. Convert a nonpartitioned table to a partitioned table.
<===============
d.. Convert a partitioned table to a nonpartitioned table.
e.. Transfer data by adding, moving, or removing partitions
--
HTH
Kalen Delaney, SQL Server MVP
"Andre" <no@.spam.com> wrote in message
news:etHWW3ahGHA.4776@.TK2MSFTNGP05.phx.gbl...
> Thanks Kalen. Do you by chance have any good links that talk about how to
> partition an existing table?
> Andre
>|||Thanks Kalen. Do you by chance have any good links that talk about how to
partition an existing table?
Andre|||Thanks Kalen - I'll check this out.
Andre

Partitioning Questions

I'm looking at using table partitioning in SQL 2005 and have a few
questions.
a.. Is partitioning really only available in Enterprise Edition? I have
Std Edition and when I tried to create a partitioning function, it told me
"Partition function can only be created in Enterprise edition of SQL Server.
Only Enterprise edition of SQL Server supports partitioning." Yet I don't
see this same statement anywhere in the documentation.
b.. All of the articles I'm reading on partitioning talk about how to
create a new table and use partitioning. How do I partition an existing
table?
c.. Does partitioning work equally well with datetime and int columns?
d.. Will partitioning give me better performance than say moving my
indexes to another filegroup?
Thanks, AndrHi Andr
Take a look in BOL at
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/81f3e917-884a-4cc8-aca2-
0a5fea89f355.htm
"Features supported by the Editions of SQL Server 2005
HTH
Kalen Delaney, SQL Server MVP
"Andre" <no@.spam.com> wrote in message
news:%23be3$PZhGHA.3496@.TK2MSFTNGP04.phx.gbl...
> I'm looking at using table partitioning in SQL 2005 and have a few
> questions.
> a.. Is partitioning really only available in Enterprise Edition? I have
> Std Edition and when I tried to create a partitioning function, it told me
> "Partition function can only be created in Enterprise edition of SQL
> Server. Only Enterprise edition of SQL Server supports partitioning." Yet
> I don't see this same statement anywhere in the documentation.
> b.. All of the articles I'm reading on partitioning talk about how to
> create a new table and use partitioning. How do I partition an existing
> table?
> c.. Does partitioning work equally well with datetime and int columns?
> d.. Will partitioning give me better performance than say moving my
> indexes to another filegroup?
>
> Thanks, Andr
>
>|||Thanks Kalen. Do you by chance have any good links that talk about how to
partition an existing table?
Andre|||Please check the topic "Modifying Partitioned Tables and Indexes"
in BOL at
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/1b85f670-74aa-4a4b-9a88-3352
f1d274d1.htm
It gives details for each of the following:
You can modify partitioned tables and indexes in the following ways:
a.. Modify a partition function to re-partition any tables or indexes that
participate in it.
b.. Modify a partition scheme to designate a filegroup to hold a
newly-added partition.
c.. Convert a nonpartitioned table to a partitioned table.
<===============
d.. Convert a partitioned table to a nonpartitioned table.
e.. Transfer data by adding, moving, or removing partitions
--
HTH
Kalen Delaney, SQL Server MVP
"Andre" <no@.spam.com> wrote in message
news:etHWW3ahGHA.4776@.TK2MSFTNGP05.phx.gbl...
> Thanks Kalen. Do you by chance have any good links that talk about how to
> partition an existing table?
> Andre
>|||Thanks Kalen - I'll check this out.
Andre

Tuesday, March 20, 2012

Paramter posting back?

I have a report with four paramters.
A "Start Year" and "End Year" that are strings with values from a query, so
available valuse are 2003,2004. 2005, and 2006.
Two datetime for a "Start Transaction Date" and "End Transaction Date".
When I set the "Start Year" parameter, the report seems to post back and I
get the error:
The value provided for the report parameter 'TransStart' is not valid
for its type. (rsReportParameterTypeMismatch) Get Online Help
The paramter was empty, so of course it is not valid. How do I stop this
behavior? The paramters are not dependant on each other and do not use
dynamic SQL.
Any help would be appreciated.
Thanks Shannondid you set allow blank for the parameter that you allow it to be empty?

Parameters...Null as an available value?!?!?

I have a hierarchy of organizations that I need to be able to filter by
in the reports...
For example, an Agency can have many Applications associated with it.
I have three stored procedures, one that gets a list of Agencies, the
other that gets a list of Applications based on the Agency that was
selected, and the third takes a bunch of other parameters and gets all
the matching orders (or whatever) associated with the Agency and
Application (Agency and Application are both input params to the third
stored proc).
What I really want is for the user to be able to not select an Agency,
basically setting it to Null, and letting the stored procedure that
does the query for the report ignore that parameter and get results for
all agencies.
If I set the parameter in the report to null, then I can't have a drop
down list with the Agency names if the user doesn't want to set the
Agency to null. But I can't have both!!!
What is the best procedure for doing this? Returning a -1 record in
the list of Agencies and using that to indicate null within the stored
proc? Any other ideas? I can't return a null record in the stored
proc because the report throws an exception. Does this make sense?
Any help would be appreciated!!! Thanks, BrianI had a similiar problem, I solved it by using 'All' as below:
Select * from MainTable
Where (Agency_Name = @.AgencyName OR @.AgencyName = 'All') AND .....
@.AgencyName is a parameter, you can set the parameter's default value
to All( you can make one dataset for this parameter as below:
Select ID, Name from AgencyTable UNION Select 0,'All'. Then In the
Report Parameters dialogue,Choose available value from query,make All
as default value), then the first part of where clause will always
automatically satisfied unless you choose a different value.
This works great for me, Hope this helps.
Good luck.
Henry
Brian wrote:
> I have a hierarchy of organizations that I need to be able to filter
by
> in the reports...
> For example, an Agency can have many Applications associated with it.
> I have three stored procedures, one that gets a list of Agencies, the
> other that gets a list of Applications based on the Agency that was
> selected, and the third takes a bunch of other parameters and gets
all
> the matching orders (or whatever) associated with the Agency and
> Application (Agency and Application are both input params to the
third
> stored proc).
> What I really want is for the user to be able to not select an
Agency,
> basically setting it to Null, and letting the stored procedure that
> does the query for the report ignore that parameter and get results
for
> all agencies.
> If I set the parameter in the report to null, then I can't have a
drop
> down list with the Agency names if the user doesn't want to set the
> Agency to null. But I can't have both!!!
> What is the best procedure for doing this? Returning a -1 record in
> the list of Agencies and using that to indicate null within the
stored
> proc? Any other ideas? I can't return a null record in the stored
> proc because the report throws an exception. Does this make sense?
> Any help would be appreciated!!! Thanks, Brian|||That's exactly what I ended up doing this morning. Works great so far!
Thanks!
Brian
fanh@.tycoelectronics.com wrote:
> I had a similiar problem, I solved it by using 'All' as below:
> Select * from MainTable
> Where (Agency_Name = @.AgencyName OR @.AgencyName = 'All') AND .....
> @.AgencyName is a parameter, you can set the parameter's default value
> to All( you can make one dataset for this parameter as below:
> Select ID, Name from AgencyTable UNION Select 0,'All'. Then In the
> Report Parameters dialogue,Choose available value from query,make All
> as default value), then the first part of where clause will always
> automatically satisfied unless you choose a different value.
> This works great for me, Hope this helps.
> Good luck.
> Henry
>
> Brian wrote:
> > I have a hierarchy of organizations that I need to be able to
filter
> by
> > in the reports...
> >
> > For example, an Agency can have many Applications associated with
it.
> >
> > I have three stored procedures, one that gets a list of Agencies,
the
> > other that gets a list of Applications based on the Agency that was
> > selected, and the third takes a bunch of other parameters and gets
> all
> > the matching orders (or whatever) associated with the Agency and
> > Application (Agency and Application are both input params to the
> third
> > stored proc).
> >
> > What I really want is for the user to be able to not select an
> Agency,
> > basically setting it to Null, and letting the stored procedure that
> > does the query for the report ignore that parameter and get results
> for
> > all agencies.
> >
> > If I set the parameter in the report to null, then I can't have a
> drop
> > down list with the Agency names if the user doesn't want to set the
> > Agency to null. But I can't have both!!!
> >
> > What is the best procedure for doing this? Returning a -1 record
in
> > the list of Agencies and using that to indicate null within the
> stored
> > proc? Any other ideas? I can't return a null record in the stored
> > proc because the report throws an exception. Does this make sense?
> > Any help would be appreciated!!! Thanks, Brian

Monday, March 12, 2012

Parameters will not Default

Greetings

I am using SQL 2005 Reporting Services and I have a problem with parameters. If I leave 3 of the 4 values as non queried in the Available values section and mark them as a Default Values: of NULL, then the report works fine. However if I set the Available values to a query I have written and leave the Default values as NULL, when I view the report in either preview mode or on the reports server, it wants me to specify a value from the boxes and the tick box for NULL has disappeared. Allow NULL value is ticked inthe first section, Properties.

Anybody got any suggestions?

If you have available values, then the allowable values of the parameter are limited to those returned by your query. If you want to allow the parameter to be null, then null must be one of the available values returned by your query.

-Albert

Parameters Select one or all

I have 2 datasets. One contains the information for the report and the other
contains a list of available companies.
In the report parameter area I have the company as a parameter and 'from
query' selected.
When the report runs, the user is prompted to 'Select a Company' from a drop
down list.
I want the user to be able to select one company or all companies. How do I
make this happen?On Mar 2, 2:49 pm, DONNA <D...@.discussions.microsoft.com> wrote:
> I have 2 datasets. One contains the information for the report and the other
> contains a list of available companies.
> In the report parameter area I have the company as a parameter and 'from
> query' selected.
> When the report runs, the user is prompted to 'Select a Company' from a drop
> down list.
> I want the user to be able to select one company or all companies. How do I
> make this happen?
Hi donna...what version of SSRS are you using? In SSRS 2005 you can
make the drop down a multi-value select by checking the 'multi-value'
box inside the Report Parameters window. This also would require that
your stored procedure be able to process multiple values inside that
parameter. If this is an option I would suggest following the steps
layed out by Bruce Loehle-Cogner here:
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/e832f21f9400af08/5b6ea54b15e7fb3f?lnk=gst&q=2005+multi-value+select+list&rnum=5&hl=en#5b6ea54b15e7fb3f
Otherwise if you are using SSRS 2000, or don't want to allow a multi-
select, you could make the query returning the values for the drop
down include a NULL. So something like...
/***/
select NULL as 'value', ' - All - ' as 'label'
union
/* Normal select for list of companies here */
select ... from ...
/***/
then in your stored proc that returns the main set of data have
something like the following in your where clause
/***/
select
...
from
...
where
...
(@.company = company_column or @.company is null)
/***/
This should then return results for all companies if @.company is
null.
Hope this helps!
--
Ben Sullins|||Thanks Ben,
I'm on RS 2000. Your comments were helpfull. I successfully used the UNION
statement on varchar fields. I now have this issue. On a int datatype field
I am receiving the following message: Syntax error converting the varchar
value 'ALL PROJECTS' to a column of data type int. Following is my code.
SELECT PROJECT_ID AS PROJECT_ID_KEY, PROJECT_ID
FROM ProjectActualCost
UNION
SELECT - 1, 'ALL PROJECTS'
Any ideas?
Thanks,
Donna
"sullins602" wrote:
> On Mar 2, 2:49 pm, DONNA <D...@.discussions.microsoft.com> wrote:
> > I have 2 datasets. One contains the information for the report and the other
> > contains a list of available companies.
> >
> > In the report parameter area I have the company as a parameter and 'from
> > query' selected.
> >
> > When the report runs, the user is prompted to 'Select a Company' from a drop
> > down list.
> >
> > I want the user to be able to select one company or all companies. How do I
> > make this happen?
> Hi donna...what version of SSRS are you using? In SSRS 2005 you can
> make the drop down a multi-value select by checking the 'multi-value'
> box inside the Report Parameters window. This also would require that
> your stored procedure be able to process multiple values inside that
> parameter. If this is an option I would suggest following the steps
> layed out by Bruce Loehle-Cogner here:
> http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/e832f21f9400af08/5b6ea54b15e7fb3f?lnk=gst&q=2005+multi-value+select+list&rnum=5&hl=en#5b6ea54b15e7fb3f
>
> Otherwise if you are using SSRS 2000, or don't want to allow a multi-
> select, you could make the query returning the values for the drop
> down include a NULL. So something like...
> /***/
> select NULL as 'value', ' - All - ' as 'label'
> union
> /* Normal select for list of companies here */
> select ... from ...
> /***/
> then in your stored proc that returns the main set of data have
> something like the following in your where clause
> /***/
> select
> ...
> from
> ...
> where
> ...
> (@.company = company_column or @.company is null)
> /***/
> This should then return results for all companies if @.company is
> null.
> Hope this helps!
> --
> Ben Sullins
>|||SELECT PROJECT_ID AS value, convert(varchar(20),PROJECT_ID) as label
FROM ProjectActualCost
UNION
SELECT - 1 as value, 'ALL PROJECTS' as label
Also, I like to use value and label when creating this, it makes it real
obvious which one you are using when you are filling in the properties for
the parameter.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"DONNA" <DONNA@.discussions.microsoft.com> wrote in message
news:417636EC-544E-40AF-A495-3BD6A582E498@.microsoft.com...
> Thanks Ben,
> I'm on RS 2000. Your comments were helpfull. I successfully used the
> UNION
> statement on varchar fields. I now have this issue. On a int datatype
> field
> I am receiving the following message: Syntax error converting the varchar
> value 'ALL PROJECTS' to a column of data type int. Following is my code.
> SELECT PROJECT_ID AS PROJECT_ID_KEY, PROJECT_ID
> FROM ProjectActualCost
> UNION
> SELECT - 1, 'ALL PROJECTS'
> Any ideas?
> Thanks,
> Donna
>
> "sullins602" wrote:
>> On Mar 2, 2:49 pm, DONNA <D...@.discussions.microsoft.com> wrote:
>> > I have 2 datasets. One contains the information for the report and the
>> > other
>> > contains a list of available companies.
>> >
>> > In the report parameter area I have the company as a parameter and
>> > 'from
>> > query' selected.
>> >
>> > When the report runs, the user is prompted to 'Select a Company' from a
>> > drop
>> > down list.
>> >
>> > I want the user to be able to select one company or all companies. How
>> > do I
>> > make this happen?
>> Hi donna...what version of SSRS are you using? In SSRS 2005 you can
>> make the drop down a multi-value select by checking the 'multi-value'
>> box inside the Report Parameters window. This also would require that
>> your stored procedure be able to process multiple values inside that
>> parameter. If this is an option I would suggest following the steps
>> layed out by Bruce Loehle-Cogner here:
>> http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/e832f21f9400af08/5b6ea54b15e7fb3f?lnk=gst&q=2005+multi-value+select+list&rnum=5&hl=en#5b6ea54b15e7fb3f
>>
>> Otherwise if you are using SSRS 2000, or don't want to allow a multi-
>> select, you could make the query returning the values for the drop
>> down include a NULL. So something like...
>> /***/
>> select NULL as 'value', ' - All - ' as 'label'
>> union
>> /* Normal select for list of companies here */
>> select ... from ...
>> /***/
>> then in your stored proc that returns the main set of data have
>> something like the following in your where clause
>> /***/
>> select
>> ...
>> from
>> ...
>> where
>> ...
>> (@.company = company_column or @.company is null)
>> /***/
>> This should then return results for all companies if @.company is
>> null.
>> Hope this helps!
>> --
>> Ben Sullins
>>|||How do I manage this same concept with a datetime value? I'm having some
problems with an error related to converting to datetime from string.
Query parameter:
SELECT DISTINCT sent_date as value, convert(varchar(30),sent_date) as
sent_date
FROM o_dpl_deployment
WHERE sent_date IS NOT NULL
UNION
SELECT '1753-01-01' as value, 'All Deployment Dates' as sent_date
Main Query:
...
((D.sent_date = @.sent_date) OR (@.sent_date ='1753-01-01')) AND
...
"Bruce L-C [MVP]" wrote:
> SELECT PROJECT_ID AS value, convert(varchar(20),PROJECT_ID) as label
> FROM ProjectActualCost
> UNION
> SELECT - 1 as value, 'ALL PROJECTS' as label
> Also, I like to use value and label when creating this, it makes it real
> obvious which one you are using when you are filling in the properties for
> the parameter.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "DONNA" <DONNA@.discussions.microsoft.com> wrote in message
> news:417636EC-544E-40AF-A495-3BD6A582E498@.microsoft.com...
> > Thanks Ben,
> >
> > I'm on RS 2000. Your comments were helpfull. I successfully used the
> > UNION
> > statement on varchar fields. I now have this issue. On a int datatype
> > field
> > I am receiving the following message: Syntax error converting the varchar
> > value 'ALL PROJECTS' to a column of data type int. Following is my code.
> >
> > SELECT PROJECT_ID AS PROJECT_ID_KEY, PROJECT_ID
> > FROM ProjectActualCost
> > UNION
> > SELECT - 1, 'ALL PROJECTS'
> >
> > Any ideas?
> >
> > Thanks,
> >
> > Donna
> >
> >
> >
> > "sullins602" wrote:
> >
> >> On Mar 2, 2:49 pm, DONNA <D...@.discussions.microsoft.com> wrote:
> >> > I have 2 datasets. One contains the information for the report and the
> >> > other
> >> > contains a list of available companies.
> >> >
> >> > In the report parameter area I have the company as a parameter and
> >> > 'from
> >> > query' selected.
> >> >
> >> > When the report runs, the user is prompted to 'Select a Company' from a
> >> > drop
> >> > down list.
> >> >
> >> > I want the user to be able to select one company or all companies. How
> >> > do I
> >> > make this happen?
> >>
> >> Hi donna...what version of SSRS are you using? In SSRS 2005 you can
> >> make the drop down a multi-value select by checking the 'multi-value'
> >> box inside the Report Parameters window. This also would require that
> >> your stored procedure be able to process multiple values inside that
> >> parameter. If this is an option I would suggest following the steps
> >> layed out by Bruce Loehle-Cogner here:
> >>
> >> http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/e832f21f9400af08/5b6ea54b15e7fb3f?lnk=gst&q=2005+multi-value+select+list&rnum=5&hl=en#5b6ea54b15e7fb3f
> >>
> >>
> >> Otherwise if you are using SSRS 2000, or don't want to allow a multi-
> >> select, you could make the query returning the values for the drop
> >> down include a NULL. So something like...
> >>
> >> /***/
> >> select NULL as 'value', ' - All - ' as 'label'
> >> union
> >> /* Normal select for list of companies here */
> >> select ... from ...
> >> /***/
> >>
> >> then in your stored proc that returns the main set of data have
> >> something like the following in your where clause
> >>
> >> /***/
> >> select
> >> ...
> >> from
> >> ...
> >> where
> >> ...
> >> (@.company = company_column or @.company is null)
> >> /***/
> >>
> >> This should then return results for all companies if @.company is
> >> null.
> >>
> >> Hope this helps!
> >> --
> >> Ben Sullins
> >>
> >>
>
>|||SELECT convert(datetime,'1753-01-01') as value, 'All Deployment Dates' as
sent_date
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
news:49D8B8E1-7657-4D1D-92D5-38D3ED874751@.microsoft.com...
> How do I manage this same concept with a datetime value? I'm having some
> problems with an error related to converting to datetime from string.
> Query parameter:
> SELECT DISTINCT sent_date as value, convert(varchar(30),sent_date) as
> sent_date
> FROM o_dpl_deployment
> WHERE sent_date IS NOT NULL
> UNION
> SELECT '1753-01-01' as value, 'All Deployment Dates' as sent_date
> Main Query:
> ...
> ((D.sent_date = @.sent_date) OR (@.sent_date => '1753-01-01')) AND
> ...
>
> "Bruce L-C [MVP]" wrote:
>> SELECT PROJECT_ID AS value, convert(varchar(20),PROJECT_ID) as label
>> FROM ProjectActualCost
>> UNION
>> SELECT - 1 as value, 'ALL PROJECTS' as label
>> Also, I like to use value and label when creating this, it makes it real
>> obvious which one you are using when you are filling in the properties
>> for
>> the parameter.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "DONNA" <DONNA@.discussions.microsoft.com> wrote in message
>> news:417636EC-544E-40AF-A495-3BD6A582E498@.microsoft.com...
>> > Thanks Ben,
>> >
>> > I'm on RS 2000. Your comments were helpfull. I successfully used the
>> > UNION
>> > statement on varchar fields. I now have this issue. On a int datatype
>> > field
>> > I am receiving the following message: Syntax error converting the
>> > varchar
>> > value 'ALL PROJECTS' to a column of data type int. Following is my
>> > code.
>> >
>> > SELECT PROJECT_ID AS PROJECT_ID_KEY, PROJECT_ID
>> > FROM ProjectActualCost
>> > UNION
>> > SELECT - 1, 'ALL PROJECTS'
>> >
>> > Any ideas?
>> >
>> > Thanks,
>> >
>> > Donna
>> >
>> >
>> >
>> > "sullins602" wrote:
>> >
>> >> On Mar 2, 2:49 pm, DONNA <D...@.discussions.microsoft.com> wrote:
>> >> > I have 2 datasets. One contains the information for the report and
>> >> > the
>> >> > other
>> >> > contains a list of available companies.
>> >> >
>> >> > In the report parameter area I have the company as a parameter and
>> >> > 'from
>> >> > query' selected.
>> >> >
>> >> > When the report runs, the user is prompted to 'Select a Company'
>> >> > from a
>> >> > drop
>> >> > down list.
>> >> >
>> >> > I want the user to be able to select one company or all companies.
>> >> > How
>> >> > do I
>> >> > make this happen?
>> >>
>> >> Hi donna...what version of SSRS are you using? In SSRS 2005 you can
>> >> make the drop down a multi-value select by checking the 'multi-value'
>> >> box inside the Report Parameters window. This also would require that
>> >> your stored procedure be able to process multiple values inside that
>> >> parameter. If this is an option I would suggest following the steps
>> >> layed out by Bruce Loehle-Cogner here:
>> >>
>> >> http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/e832f21f9400af08/5b6ea54b15e7fb3f?lnk=gst&q=2005+multi-value+select+list&rnum=5&hl=en#5b6ea54b15e7fb3f
>> >>
>> >>
>> >> Otherwise if you are using SSRS 2000, or don't want to allow a multi-
>> >> select, you could make the query returning the values for the drop
>> >> down include a NULL. So something like...
>> >>
>> >> /***/
>> >> select NULL as 'value', ' - All - ' as 'label'
>> >> union
>> >> /* Normal select for list of companies here */
>> >> select ... from ...
>> >> /***/
>> >>
>> >> then in your stored proc that returns the main set of data have
>> >> something like the following in your where clause
>> >>
>> >> /***/
>> >> select
>> >> ...
>> >> from
>> >> ...
>> >> where
>> >> ...
>> >> (@.company = company_column or @.company is null)
>> >> /***/
>> >>
>> >> This should then return results for all companies if @.company is
>> >> null.
>> >>
>> >> Hope this helps!
>> >> --
>> >> Ben Sullins
>> >>
>> >>
>>

Friday, March 30, 2012

Partial Indexes

Partial Indexes

Friday, March 23, 2012

Partitioning Questions

Partitioning Questions

Tuesday, March 20, 2012

Paramter posting back?

Parameters...Null as an available value?!?!?

Monday, March 12, 2012

Parameters will not Default

Parameters Select one or all

Parameterized Expression

Blog Archive

About Me