Parameterized Expression: created

Showing posts with label created. Show all posts

Friday, March 30, 2012

particulat table in Filegroup

quote:

> Dear Friends
> I have a small database of 1 gb i have created some
> Secondary data files for same. in the same database some
> tables are extensively used for the Modification hence i
> want to separte them form other table datafiles can i do
> the same.
> please suggest.
> Best regards
> NiitMalad

|||> If you have an existing table and you want to place it on another
filegroup

quote:

> you can only create a new table with the ON filegroup keywords

Or you could create a clustered index using the ON filegroup clause, and it
will move the data to the filegroup. Of course, if there's already a
clustered index, you'll have to drop it first. Which complicates things if
it's a referenced primary key.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

particulat table in Filegroup

Dear Friends
I have a small database of 1 gb i have created some
Secondary data files for same. in the same database some
tables are extensively used for the Modification hence i
want to separte them form other table datafiles can i do
the same.
please suggest.
Best regards
NiitMaladWhat you can do is create multiple filegroups and assign a datafile to a
filegroup.
You can specify the create table statement with the filegroup it must be
created on
CREATE Table tbl_demo (COL1 INT)
on FILEGROUP
where filegroup is the logical name of the filegroup.
If you have an existing table and you want to place it on another filegroup
you can only create a new table with the ON filegroup keywords, insert from
the first table, delete the first table and rename the new table, what means
: there is no alter table statement to move a table to another filegroup.
You first need to create the filegroups before you create the datafiles and
assign them onto a filegroup
--
Regards,
Dandy Weyn
MCSE, MCSA, MCDBA, MCT
www.dandyman.net
"niitmalad" <niitmalad@.yahoo.co.in> wrote in message
news:006601c3dd18$6132f930$a101280a@.phx.gbl...
> Dear Friends
> I have a small database of 1 gb i have created some
> Secondary data files for same. in the same database some
> tables are extensively used for the Modification hence i
> want to separte them form other table datafiles can i do
> the same.
> please suggest.
> Best regards
> NiitMalad|||> If you have an existing table and you want to place it on another
filegroup
> you can only create a new table with the ON filegroup keywords
Or you could create a clustered index using the ON filegroup clause, and it
will move the data to the filegroup. Of course, if there's already a
clustered index, you'll have to drop it first. Which complicates things if
it's a referenced primary key.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

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--

Wednesday, March 28, 2012

Parse

I have one database backup file that is created daily with directory
structure listed
below what is the easy way to parse or extract that numeric value from the
command listed below?
Thank You,
EXEC xp_cmdshell 'dir \\DALL3\T$\dump\DALLAS\*.BAK'Joe,
Assuming the command is stored in a column (Col1) in a table (TEST1) and is
a single value, the following should work:
SELECT SUBSTRING(Col1,(CHARINDEX('',Col1,CHARI
NDEX('',Col1,1)+ 2)-1),1)
FROM TEST1
HTH
Jerry
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:7771B914-DFFF-473C-881A-82552D7FEF45@.microsoft.com...
> I have one database backup file that is created daily with directory
> structure listed
> below what is the easy way to parse or extract that numeric value from the
> command listed below?
> Thank You,
> EXEC xp_cmdshell 'dir \\DALL3\T$\dump\DALLAS\*.BAK'|||Please, be more specific. At least give a few typical samples, and specify
what exactly you'd like to parse - is it the whole command, the parameter,..
.
what?
ML

Parms

I was wondering how to default an input parm to nulls.
I created a proc which looked liked the following:
ALTER proc dbo.Proc_QuerySkaters
@.ClubID uniqueidentifier =null,
@.msg nvarchar(220) out
as
declare @.cnt int
if @.ClubID = null
begin
select ClubID, FirstName, MiddleName, LastName
from dbo.Skater
set @.cnt = @.@.ROWCOUNT
end
else
begin
select ClubID, FirstName, MiddleName, LastName
from dbo.Skater
where @.clubID = ClubID
set @.cnt = @.@.ROWCOUNT
end
if @.@.ERROR > 0
begin
set @.msg = 'Unexpected error occurred executing Proc_QuerySkaters
- Error code: ' + cast( @.@.ERROR as char(4) )
return -1
end
set @.msg=''
return 0
This compiles, but it does not run correctly. If I change the
declaration of @.ClubID and removed the '=null' - then it compiles, but
it requires the @.ClubID to be populated (will not accept nulls).
How do I define an input parm which is not required to be populated
(accepts nulls and is defaulted to null)?Hi Jim.
The parameter is defined correctly - you should check for null by using IS
NULL rather then = null in the body of the proc:
ALTER proc dbo.Proc_QuerySkaters
@.ClubID uniqueidentifier =null,
@.msg nvarchar(220) out
as
declare @.cnt int
if @.ClubID is null
begin
select ClubID, FirstName, MiddleName, LastName
from dbo.Skater
set @.cnt = @.@.ROWCOUNT
end
else
begin
select ClubID, FirstName, MiddleName, LastName
from dbo.Skater
where @.clubID = ClubID
set @.cnt = @.@.ROWCOUNT
end
if @.@.ERROR > 0
begin
set @.msg = 'Unexpected error occurred executing Proc_QuerySkaters
- Error code: ' + cast( @.@.ERROR as char(4) )
return -1
end
set @.msg=''
return 0
Regards,
Greg Linwood
SQL Server MVP
"Jim Heavey" <JimHeavey@.nospam.com> wrote in message
news:Xns9458D9954D842JimHeaveyhotmailcom@.207.46.248.16...
> I was wondering how to default an input parm to nulls.
> I created a proc which looked liked the following:
> ALTER proc dbo.Proc_QuerySkaters
> @.ClubID uniqueidentifier =null,
> @.msg nvarchar(220) out
> as
> declare @.cnt int
> if @.ClubID = null
> begin
> select ClubID, FirstName, MiddleName, LastName
> from dbo.Skater
> set @.cnt = @.@.ROWCOUNT
> end
> else
> begin
> select ClubID, FirstName, MiddleName, LastName
> from dbo.Skater
> where @.clubID = ClubID
> set @.cnt = @.@.ROWCOUNT
> end
> if @.@.ERROR > 0
> begin
> set @.msg = 'Unexpected error occurred executing Proc_QuerySkaters
> - Error code: ' + cast( @.@.ERROR as char(4) )
> return -1
> end
> set @.msg=''
> return 0
> This compiles, but it does not run correctly. If I change the
> declaration of @.ClubID and removed the '=null' - then it compiles, but
> it requires the @.ClubID to be populated (will not accept nulls).
> How do I define an input parm which is not required to be populated
> (accepts nulls and is defaulted to null)?

Monday, March 26, 2012

Parent Package Variable visibility when designing packages

I've created an SSIS package that uses parent package variables at several steps in the data flow. However, those parent package variables are only visible during runtime, making debugging the package practically impossible. Let me give you a simplified example:

In the parent package, I have a string variable named "sqlLookup" that looks like this:

"SELECT * from tblTest WHERE city = " + @.city

Also in the parent package is a variable named "city" which I can set to the name of the city that I want to query on. This dynamically updates the sqlLookup variable, which is being evaluated as an expression.

In the child package, I have an OLE DB Source control which is using the "sqlLookup" variable for its query. I have set up my parent package variable configuration, and it works when I run the package.

The problem is this...when I open the child package, I get an error on the OLE DB Source control using the parent package variable, "Command text was not set for the command object". Presumably, this is because the parent package variable is only available during run-time, and not at design time. And, if metadata changes (which it has), I can't get into the OLE DB Source control to edit it, because it throws the error.

So, my question is this: are there any workarounds for this problem? Is there a better way to do this? This seems like such an obvious problem that I'm wondering if I either missed a step somewhere, or if I'm just doing it the wrong way.

Thanks!

Try setting DelayValidation=TRUE on the data-flow task in the child package.

If that fails, set Work Offline on the SSIS menu.

-Jamie

|||

Jamie,

Thanks for the reply, however I'm not having any luck with either of your suggestions. I created a new Project from scratch to simplify the problem.

My Project contains two files, parent.dtsx and child.dtsx.

parent.dtsx contains only an Execute Package task pointing to child.dtsx, and one string variable called sqlGetNames, which is set to " SELECT * from tblCustomers WHERE city = 'Denver' "

child.dtsx contains a single Data Flow, with one OLE DB Source control. It contains one variable, also called "sqlGetNames". I have enabled Package Configurations, and created a Parent Package Variable that should get the value of sqlGetNames from the parent.

However, when I go into my OLE DB Source control in the child package, I get the following error when I tell it to use sqlGetNames as the variable for the SQL command:

Error at Data Flow Task [OLE DB Source[1]]: An OLE DB error has occurred. Error code: 0x80040E0C. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E0C Description: "Command text was not set for the command object.".

I get this error whether or not I have DelayValidation set to False. If I set SSIS to work offline, then I can't set up the OLE DB Source control because there is no connection.

Any other ideas?

|||It may work if you set the child package's variable to have an initial value that works at design time, ie child.sqlGetNames = "SELECT * from tblCustomers"|||

Thanks. That's not exactly what I was hoping for, but it's a useful workaround. I appreciate the suggestion.

Parent Expression

I have created a Report that has many Datasets and they each link to an individual table in the Report. I need the tables to only show the Data that relates to a Parent field on the Report.

E.G.

John Smith (parent)
Tasks 3
Task1
Task2
Task3
Appointments 10
... etc

Joe Bloggs (parent field)
Tasks 2
Task1
Task2
Appointments 5
... etc

I dont know how to make all the Datasets for the individual tables look at the name field. I can only do this if I use one Dataset or use a Parameter. I have tried using Lists but they dont seem to do what I need to do.

Thanks
NJA

What you are describing is a cross-dataset join. This is not natively supported in Reporting Services. You will either need to do the join in your query or use subreports (which are less efficient).

Friday, March 23, 2012

Parent child reports problems

Hi,
I have a report which I have created 3 or 4 child reports from.
If I make a change to the format of an object within the parent report
(e.g. the colour of a text box) then the change appears on the child
reports as I would expect.
The problem is that if I make a change to the properties of the report
(e.g. the margins or the page size) then the change is NOT picked up
in the child reports.
Does anybody have any solution to this annoying problem.
Regards,
JamesOn Nov 6, 4:33 am, joli...@.googlemail.com wrote:
> Hi,
> I have a report which I have created 3 or 4 child reports from.
> If I make a change to the format of an object within the parent report
> (e.g. the colour of a text box) then the change appears on the child
> reports as I would expect.
> The problem is that if I make a change to the properties of the report
> (e.g. the margins or the page size) then the change is NOT picked up
> in the child reports.
> Does anybody have any solution to this annoying problem.
> Regards,
> James
This might not be exactly what you're looking for, but you could use a
comparison tool to compare the RDL files side-by-side and merge the
changes pretty quickly. There's a trial version of a tool I like to
use here: http://www.araxis.com/merge/
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Parent Child Relationship

I created a parent child relationship in SA 2005. It works great, except when adding it to the cube and browsing it. It shows the ID instead of the Name attribute.

How do I make it show the name attribute?

Thanks in advance,

MardoSuppose you have the following columns in your table:
ID - Integer. Primary key column of your table
ParentID - Integer. The id of the parent object.
Name - String. The name of the object identified by ID.
Since things already work for you then you already have the key attribute and parent attribute set in your dimension. To resolve your problem select the parent attribute oin Dimension Structure page of the dimension editor and press F4 to activate the property grid displaying the properties of the selected attribute. Locate NameColumn property. Edit that property and make it binding to the Name column in your table.
|||When I do that, I get the following error after entering it...

Another 'DimensionAttribute' object has the 'Name' name.|||Most probably this is because you changed Name property in the property grid. You should edit *NameColumn* property.|||

It still shows the ID, not the name. Any other ideas? I did use the NameColumn.

Mardo

|||Can you send me your project to andrewgaATnetzeroDotcom ?|||
Its in your inbox.|||Yes, i have received it. I will look into it soon.|||

If you change NameColumn property for your *Organization* attribute to be Name (like in Parent Organization Id attribute) instead of "Organization Id" it will work.

This is most probably our bug and we will triage it.

Thank you.

|||Andrew,

Thank you. Ill give it a shot.

Mardo|||

Is this a bug in a version of BIDS? I have been struggling with this all day and I finally have found the issue. I am running what I think is a hotfixed version Microsoft SQL Server Analysis Services Designer Version 9.00.2047.00 of the AS Tools.

Every time I set the name column to be the descriptive column it changes back!

However, going into SSMS after building from the tool, scripting the dimension as alter, I see:

<NameColumn>
<NullProcessing>ZeroOrBlank</NullProcessing>
<DataType>WChar</DataType>
<Source xsi:type="ColumnBinding">
<TableID>dbo_membership_dim</TableID>
<ColumnID>membership_dim_key</ColumnID>
</Source>
</NameColumn>

I have set this in the tool to simply: membership.

So I change it here to membership and execute it and it says:

Which I am assuming is its "well structured" way of say "Atta boy" then it works like advertised. (Boy, this has been driving me batty for like 8 hours!

|||

I actually got it working by changing all of the attributes of the Hierarchy set to use the membership attribute, not just the Parent attribute/hierarchy. This stuff is interesting to say the least :)

|||

Hello Louis,

I was not able to reproduce on RTM version, but i will try on SP1 and our current bits to see if some regression was intoriduced.

When you say it changes back, what exactly do you mean? Do you mean:

1. Once you close the dialog box, where you picked the column you still see the previous column in the property grid.

2. Once you save the dimension (assuming you are connected directly to the server) and open it again you see the previous value.

3. Being in project mode (you edit files on the disk) you deploy and then still see the previous column binding when connected to the server and examined the deployed contents.

|||

I actually saw the proper looking text values for a moment, but after building/processing the cube, the numeric values showed up.

The table structures for the related tables are. I am doing the membership_dim related through the account_dim to the sales_fact. I built these tables using select...into from adventureWorksDw to try to approximate our structures and was wanting to try out the logical keys in the DSV, hence the lack of relationships, pkeys, etc). I am trying to build a demonstration cube to demonstrate all of the different constructs we need (this all got started as I tried to figure out what I was doing here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=522521&SiteID=1&mode=1)

I am thinking that this might have something to do with the name of the attribute being just membership, but I don't know.

Edit: The workaround (or it might be normal) was when I set the Membership Dim - Dimension Attribute's NameColumn to the membership column's value, not the Parent Membership Dim Key. Is that right?

Thanks for the help!

CREATE TABLE [dbo].[membership_dim](
[membership_dim_key] [int] NOT NULL,
[parent_membership_dim_key] [int] NULL,
[membership] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
PRIMARY KEY CLUSTERED ( [membership_dim_key] ASC )
)

ALTER TABLE [dbo].[membership_dim] WITH CHECK ADD FOREIGN KEY([parent_membership_dim_key])
REFERENCES [dbo].[membership_dim] ([membership_dim_key])

CREATE TABLE [dbo].[account_dim](
[account_dim_key] [int] IDENTITY(1,1) NOT NULL,
[account_number] [nvarchar](15) COLLATE Latin1_General_CI_AS NOT NULL,
[geography_dim_key] [int] NULL,
[marital_status] [nchar](1) COLLATE Latin1_General_CI_AS NULL,
[yearly_income] [money] NULL,
[total_children] [tinyint] NULL,
[education_level] [nvarchar](40) COLLATE Latin1_General_CI_AS NULL,
[occupation_type] [nvarchar](100) COLLATE Latin1_General_CI_AS NULL,
[commute_distance] [nvarchar](15) COLLATE Latin1_General_CI_AS NULL,
[customer_first_purchase_date_dim_key] [int] NULL,
[membership_dim_key] [int] NULL
)

CREATE TABLE [dbo].[sales_fact](
[sales_fact_key] [bigint] NULL,
[product_dim_key] [int] NOT NULL,
[ship_date_dim_key] [int] NULL,
[order_date_dim_key] [int] NULL,
[account_dim_key] [int] NOT NULL,
[discountAmount] [float] NULL,
[unit_price] [money] NULL,
[sales_order_number] [nvarchar](20) COLLATE Latin1_General_CI_AS NOT NULL,
[sales_order_line_number] [tinyint] NOT NULL,
[sales_territory_dim_key] [int] NULL
) ON [PRIMARY]

Parent Child Relationship

I created a parent child relationship in SA 2005. It works great, except when adding it to the cube and browsing it. It shows the ID instead of the Name attribute.

How do I make it show the name attribute?

Thanks in advance,

MardoSuppose you have the following columns in your table:

ID - Integer. Primary key column of your table

ParentID - Integer. The id of the parent object.

Name - String. The name of the object identified by ID.

Since things already work for you then you already have the key

attribute and parent attribute set in your dimension. To resolve your

problem select the parent attribute oin Dimension Structure page of the dimension editor and press F4 to

activate the property grid displaying the properties of the selected

attribute. Locate NameColumn property. Edit that property and make it

binding to the Name column in your table.|||When I do that, I get the following error after entering it...

Another 'DimensionAttribute' object has the 'Name' name.|||Most probably this is because you changed Name property in the property grid. You should edit *NameColumn* property.|||

It still shows the ID, not the name. Any other ideas? I did use the NameColumn.

Mardo

|||Can you send me your project to andrewgaATnetzeroDotcom ?|||
Its in your inbox.|||Yes, i have received it. I will look into it soon.|||

If you change NameColumn property for your *Organization* attribute to be Name (like in Parent Organization Id attribute) instead of "Organization Id" it will work.

This is most probably our bug and we will triage it.

Thank you.

|||Andrew,

Thank you. Ill give it a shot.

Mardo|||

Every time I set the name column to be the descriptive column it changes back!

However, going into SSMS after building from the tool, scripting the dimension as alter, I see:

I have set this in the tool to simply: membership.

So I change it here to membership and execute it and it says:

Which I am assuming is its "well structured" way of say "Atta boy" then it works like advertised. (Boy, this has been driving me batty for like 8 hours!

|||

Hello Louis,

I was not able to reproduce on RTM version, but i will try on SP1 and our current bits to see if some regression was intoriduced.

When you say it changes back, what exactly do you mean? Do you mean:

1. Once you close the dialog box, where you picked the column you still see the previous column in the property grid.

2. Once you save the dimension (assuming you are connected directly to the server) and open it again you see the previous value.

3. Being in project mode (you edit files on the disk) you deploy and then still see the previous column binding when connected to the server and examined the deployed contents.

|||

I actually saw the proper looking text values for a moment, but after building/processing the cube, the numeric values showed up.

I am thinking that this might have something to do with the name of the attribute being just membership, but I don't know.

Edit: The workaround (or it might be normal) was when I set the Membership Dim - Dimension Attribute's NameColumn to the membership column's value, not the Parent Membership Dim Key. Is that right?

Thanks for the help!

ALTER TABLE [dbo].[membership_dim] WITH CHECK ADD FOREIGN KEY([parent_membership_dim_key])
REFERENCES [dbo].[membership_dim] ([membership_dim_key])

Parent Child Relationship

I created a parent child relationship in SA 2005. It works great, except when adding it to the cube and browsing it. It shows the ID instead of the Name attribute.

How do I make it show the name attribute?

Thanks in advance,

Another 'DimensionAttribute' object has the 'Name' name.|||Most probably this is because you changed Name property in the property grid. You should edit *NameColumn* property.|||

It still shows the ID, not the name. Any other ideas? I did use the NameColumn.

Mardo

|||Can you send me your project to andrewgaATnetzeroDotcom ?|||
Its in your inbox.|||Yes, i have received it. I will look into it soon.|||

If you change NameColumn property for your *Organization* attribute to be Name (like in Parent Organization Id attribute) instead of "Organization Id" it will work.

This is most probably our bug and we will triage it.

Thank you.

|||Andrew,

Thank you. Ill give it a shot.

Mardo|||

Every time I set the name column to be the descriptive column it changes back!

However, going into SSMS after building from the tool, scripting the dimension as alter, I see:

I have set this in the tool to simply: membership.

So I change it here to membership and execute it and it says:

Which I am assuming is its "well structured" way of say "Atta boy" then it works like advertised. (Boy, this has been driving me batty for like 8 hours!

|||

Hello Louis,

I was not able to reproduce on RTM version, but i will try on SP1 and our current bits to see if some regression was intoriduced.

When you say it changes back, what exactly do you mean? Do you mean:

1. Once you close the dialog box, where you picked the column you still see the previous column in the property grid.

2. Once you save the dimension (assuming you are connected directly to the server) and open it again you see the previous value.

3. Being in project mode (you edit files on the disk) you deploy and then still see the previous column binding when connected to the server and examined the deployed contents.

|||

I actually saw the proper looking text values for a moment, but after building/processing the cube, the numeric values showed up.

I am thinking that this might have something to do with the name of the attribute being just membership, but I don't know.

Edit: The workaround (or it might be normal) was when I set the Membership Dim - Dimension Attribute's NameColumn to the membership column's value, not the Parent Membership Dim Key. Is that right?

Thanks for the help!

ALTER TABLE [dbo].[membership_dim] WITH CHECK ADD FOREIGN KEY([parent_membership_dim_key])
REFERENCES [dbo].[membership_dim] ([membership_dim_key])

Wednesday, March 21, 2012

partitioned views Explain Plan

Hello I created a partitioned view on my database db1 and It comes from the UNION ALL of 2 tables (I split 1 very big table in 2 smaller tables... Horizontal partition!!) that are on the same db1. When I query them I receive in the EXECUTION PLAN an Item called CONCATENATION of both input tables, This seems to be more expensive than using only one big table so partitioning the big one gets none sense.. I would like to know if this is normal and If I'm doing well with partitioning.

Please see the script below:

CREATE TABLE [dbo].[TREP_NOVEDADES_2006] (
[NMSEC_NOVEDAD] [numeric](12, 0) NOT NULL ,
[CDCONCEPTO] [varchar] (12) NOT NULL ,
[CDTIPO_VALOR] [char] (1),
[CDPRECIO] [varchar] (12),
[CDTIP_HECT_DTO_PAG] [varchar] (2),
[FEGENERACION] [datetime] NOT NULL ,
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TREP_NOVEDADES_2006] ADD
CONSTRAINT [TREP_NOVEDADES2006_PK] PRIMARY KEY CLUSTERED
(
[NMSEC_NOVEDAD]
) ON [PRIMARY] ,
CONSTRAINT [TREP_NOVEDADES_FEGENERACION2006_CK] CHECK ([FEGENERACION] < '2007-01-01 00:00:00.000')
GO

CREATE TABLE [dbo].[TREP_NOVEDADES_2007] (

[NMSEC_NOVEDAD] [numeric](12, 0) NOT NULL ,

[CDCONCEPTO] [varchar] (12) NOT NULL ,

[CDTIPO_VALOR] [char] (1),

[CDPRECIO] [varchar] (12),

[CDTIP_HECT_DTO_PAG] [varchar] (2),

[FEGENERACION] [datetime] NOT NULL ,

) ON [PRIMARY]

ALTER TABLE [dbo].[TREP_NOVEDADES_2007] ADD

CONSTRAINT [TREP_NOVEDADES2007_PK] PRIMARY KEY CLUSTERED

(

[NMSEC_NOVEDAD]

) ON [PRIMARY] ,
CONSTRAINT [TREP_NOVEDADES_FEGENERACION2007_CK] CHECK ([FEGENERACION] > '2006-12-31 00:00:00.000'),
GO
CREATE VIEW TREP_NOVEDADES AS
SELECT * FROM TREP_NOVEDADES_2006
UNION ALL
SELECT * FROM TREP_NOVEDADES_2007
GO

-- Here is where I query the view...
SELECT * FROM TREP_NOVEDADES_2006 WHERE FEGENERACION < '2006-05-23 00:00:00.000'

Am I doing anything wrong ?

Initially I'd try creating a NONCLUSTERED Index on the FEGENERACION column in each table.

Chris

|||

I would tend to dissent in this case; it looks to me like this is a case in which you are going to have plan crossover and the optimizer will abandon the index in favor of some scan -- either a clustered index scan or a table scan.

Julian:

When have a great deal of data and you perform the search:

SELECT * FROM TREP_NOVEDADES_2006 WHERE FEGENERACION < '2006-05-23 00:00:00.000'

You are invariably going to get stuck with a long-running execution plan. In this case you are going to get somewhere around half of the records in your 2006 table. You cannot benefit from a simple index because the potential number bookmark lookups would make for a worse execution plan than a table scan. The only index that MIGHT be of some benefit would be a cover index that had its first column be (as Chris suggested) the FEGENERACION column. However, since your SELECT statement is for ALL columns I doubt that I would build such a cover index.

To me, the moral of the story is that if you are truely going to read something on the order of half of the data in a table you need to face the reality that you are going to do a table scan. Avoid such queries as much as you can.

|||

I guess I should have explained my reasoning... ;)

My thinking was that the table containing the more recent data would be eliminated quickly during execution of the query that was provided. As long as the statistics are up to date then surely this would result in an index seek in the more recent table - bookmark lookups would not be required as there would be no rows that met the criteria.

I would imagine that a table scan would be performed on the other table(s) rather than a seek and subsequent lookup, for the reasons that you mentioned, and that similar performance to the non-partitioned table would be seen.

Chris

|||

Please forgive me, Chris, I have messed up a little bit.

I walked over to the next building to fetch a liter of water and realized that although the index would probably not apply in this particular instance that Chris' suggested index was probably still a good suggestion. It will help in many instances in which you are filtering by date/time -- it just is not likely to help when you are selecting something like half the table.

Sorry, I messed up.

Kent

( You beat me; I tried to make amends before you felt compelled to respond; you are right )

|||

You've not messed anything up - you made some valid points in your post!

It's good to have an answer challenged and then to feel compelled to justify it. Anyway, my suggestion was only my opinion - it may or may not help the OP.

Chris

|||Agreed. :-)|||Thank you so much guys for your point of view and your help...|||God Damn't ... I did what Chris said (those NONCLUSTERED indexes) ... and now subtree cost is sooooo low.... I hope It boost my queries...

Thank you so much

Partitioned view doesn't allow to query itself while insertion

I created 2 similar tables for different accounts within one database and
partitioned view on them.
create table t
(
[ID] int identity(1,1),
[Name] varchar(15),
[Account] varchar(20) default 'Account1' check(Team = 'Account1'),
PRIMARY KEY CLUSTERED
(
[ID], [Account]
)
)
create view v
as
select * from Account1.t
union all
select * from Account2.t
I run query like this:
"select top 100 * from v where Account = 'Account1'"
and massive insertion from staging table like this at the same time:
"insert into t ( name ) select name from tt under different account"
my query stops until insertion is completed.
FYI: each tables reside in it's own filegroup. Each filgroup has its files
on physically different HDD.
There are 2 multithreaded processors there and parallelism works.
Execution plpan shows DIFFERENT CLUSTER KEYS ARE USED for any s

s and so o
n.
Isolation level is Read Commited (when I set read uncommited it works
without locks, of course).
I don't know why my query wait for end of insertion. Query and insertion
don't use common resources. They MUST works independently...Can you cut and paste the exact SQL you're running, or provide
a repro without typos? What you posted here includes a CHECK
constraint that refers to a non-existent column named [Team].
You might try reversing the order of the primary key columns.
Steve Kass
Drew University
OSA wrote:

>I created 2 similar tables for different accounts within one database and
>partitioned view on them.
>create table t
>(
> [ID] int identity(1,1),
> [Name] varchar(15),
> [Account] varchar(20) default 'Account1' check(Team = 'Account1'),
> PRIMARY KEY CLUSTERED
> (
> [ID], [Account]
> )
> )
>create view v
>as
> select * from Account1.t
> union all
> select * from Account2.t
>I run query like this:
>"select top 100 * from v where Account = 'Account1'"
> and massive insertion from staging table like this at the same time:
>"insert into t ( name ) select name from tt under different account"
>my query stops until insertion is completed.
>FYI: each tables reside in it's own filegroup. Each filgroup has its files
>on physically different HDD.
>There are 2 multithreaded processors there and parallelism works.
>Execution plpan shows DIFFERENT CLUSTER KEYS ARE USED for any s

s and so
on.
>Isolation level is Read Commited (when I set read uncommited it works
>without locks, of course).
>I don't know why my query wait for end of insertion. Query and insertion
>don't use common resources. They MUST works independently...
>

Partitioned view broken after moving table to new filegroup

I am using SQL Server 2000, SP3.

I created an updatable partitioned view awhile ago and it has been
running smoothly for some time. The partition is on a DATETIME column
and it is partitioned by month. Each month a stored procedure is
scheduled that creates the new month's table, and alters the view to
include it. Again... working like a charm for quite some time.

This past weekend I moved some of the first tables onto a new file
group. I did this through Enterprise Manager, by going into design
mode for the table, then going into the properties for the table and
changing the file group there as well as in all of the indexes. Now
the partitioned view is no longer updatable. It gives the error
message: "UNION ALL view '<view name>' is not updatable because a
partitioning column was not found."

I have extracted the DDL for all of the partition tables and compared
them and they all look the same. I checked and then double-checked the
CHECK constraints to make sure that they were all valid and they are.
If I remove the tables that I moved to the new file group from the
view, then it is once again updatable, but when I put them back in it
fails again.

Any ideas? If you would like samples of the code then I can send it
along, but it's rather large, so I have not included it here.

Thanks!
Thomas R. HummelThomas R. Hummel (tom_hummel@.hotmail.com) writes:
> I created an updatable partitioned view awhile ago and it has been
> running smoothly for some time. The partition is on a DATETIME column
> and it is partitioned by month. Each month a stored procedure is
> scheduled that creates the new month's table, and alters the view to
> include it. Again... working like a charm for quite some time.
> This past weekend I moved some of the first tables onto a new file
> group. I did this through Enterprise Manager, by going into design
> mode for the table, then going into the properties for the table and
> changing the file group there as well as in all of the indexes. Now
> the partitioned view is no longer updatable. It gives the error
> message: "UNION ALL view '<view name>' is not updatable because a
> partitioning column was not found."
> I have extracted the DDL for all of the partition tables and compared
> them and they all look the same. I checked and then double-checked the
> CHECK constraints to make sure that they were all valid and they are.
> If I remove the tables that I moved to the new file group from the
> view, then it is once again updatable, but when I put them back in it
> fails again.
> Any ideas? If you would like samples of the code then I can send it
> along, but it's rather large, so I have not included it here.

Without sample code, only wild guesses are possible.

But one possbility is that as part of the process the CHECK constraints
were disabled and then enabled without WITH CHECK. Then SQL Server does not
trust those constraints.

Try do:

ALTER TABLE tbl NOCHECK CONSTRAINT your_constraint
ALTER TABLE tbl WITH CHECK CHECK CONSTRAINT your_constraint

Moral: don't use Enterprise Manager, if you don't know what it does
behind your back.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland! That was exactly the problem.

As a related followup... if I know that the data in the table
satisfies the constraint, can I make a change to sysobjects to set the
status for the check constraint object to "2"? I understand all of the
usual warnings about making changes to system tables. Just wondering
if it tracks the check constraint information anywhere else.

Since the column was part of the clustered primary key doing the check
on the constraint took less than 1 minute for a table of over 20M
rows, but it might be a useful trick if the check would take longer
than that.

Thanks again,
-Tom.

Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns9590ED2AA1AC6Yazorman@.127.0.0.1>...

> But one possbility is that as part of the process the CHECK constraints
> were disabled and then enabled without WITH CHECK. Then SQL Server does not
> trust those constraints.
> Try do:
> ALTER TABLE tbl NOCHECK CONSTRAINT your_constraint
> ALTER TABLE tbl WITH CHECK CHECK CONSTRAINT your_constraint
> Moral: don't use Enterprise Manager, if you don't know what it does
> behind your back.|||Thomas R. Hummel (tom_hummel@.hotmail.com) writes:
> As a related followup... if I know that the data in the table
> satisfies the constraint, can I make a change to sysobjects to set the
> status for the check constraint object to "2"? I understand all of the
> usual warnings about making changes to system tables. Just wondering
> if it tracks the check constraint information anywhere else.

I for one would never try to do such a thing. It might work, but I don't
like unpleasant surprises later on.

> Since the column was part of the clustered primary key doing the check
> on the constraint took less than 1 minute for a table of over 20M
> rows, but it might be a useful trick if the check would take longer
> than that.

The clustered index does not have much to do it, but the non-clustered
indexes on the table were. Since the clustered index columns act as
row locator in the NC indexes, SQL Server could take a shortcut and scan
the smallest NC index, rather than the whole table.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Tuesday, March 20, 2012

Parent / Child Package - Connections, Variables Etc.,

In respect to Parent / Child packages, can some one correct me if I'm wrong.

Even if connection managers are created in Parent package, the same needs to be created in child packages if they need to connect to database. On the other hand I can just create connection strings in variable in parent package(instead of connection managers itself) and use parent package variable configuration to configure the connection string of child package with the variable value.

Sorry If I'm confusing

The same with variables, the parent variable needs to be mapped to a child variable(using parent package variable config) to be used in child package, it cannot be used as it is.

Thanks

I think everything you've stated is correct.|||

Thanks for the confirmation Phil. Let me see how my experiment with Parent / Child packages goes.

Just a thought, It would be good to just specify the parent package in the child package and if it can pick up the variables, connections from the parent package.

Thanks

Parent - Multiple Child Package Execution

I have a parent package "A", I also have 4 child packages "B","B1","B2",B3"

In BIDS, I created a file connection in the package "A" to connect to the child packages. So whenever I want to run B1 or B2, I change the path in the file connection to point to B1 or B2 and so on. Since the developement environment is File system this works perfectly fine.

But in the Test / Production environment all packages are stored in the Sql server. How can I paramaterize the child package connection so that I can use configuration / variables to select which child package to execute.

Thanks

Set up four execute package tasks that point to each child package. Then use a variable precedence constraint to determine which execute package task to run. You could make the variable an integer and then use something like: @.ChildPackage == 1, @.ChildPackage == 2, etc...|||

One problem I see with this approach is that, If I develop another child package then I have to modify the master package to include this new child package and re-deploy. as the number of packages increases, it will be difficult to maintain. Basically I just want one execute package task which should be configurable ( say which package to execute or path of the package to execute)

Thanks

|||Could you use two Execute Package tasks, one configured for executing on the file system, the other for the database? Then use precedence constraints to control which one executes. So you'd pass in two variables - one being the connection to the package, the other setting a variable that controls which package executes.

Parent - Multiple Child Package Execution

I have a parent package "A", I also have 4 child packages "B","B1","B2",B3"

Thanks

Friday, March 30, 2012

particulat table in Filegroup

particulat table in Filegroup

Partial Indexes

Partial Indexes

Wednesday, March 28, 2012

Parse

Parms

Monday, March 26, 2012

Parent Package Variable visibility when designing packages

Parent Expression

Friday, March 23, 2012

Parent child reports problems

Parent Child Relationship

Parent Child Relationship

Parent Child Relationship

Wednesday, March 21, 2012

partitioned views Explain Plan

Partitioned view doesn't allow to query itself while insertion

Partitioned view broken after moving table to new filegroup

Tuesday, March 20, 2012

Parent / Child Package - Connections, Variables Etc.,

Parent - Multiple Child Package Execution

Parent - Multiple Child Package Execution

Parameterized Expression

Blog Archive

About Me