Parameterized Expression: varchar

Showing posts with label varchar. 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--

Wednesday, March 28, 2012

parsing varchar fields

InvoiceNumber@.VendorAcronym

There'd be a lot of vendors.

Additional issue: sometimes these values would be referred to in the
description field, and I'd need to distinguish them as referrals rather
than as original recorded instances of the values. For that, I imagined
either:

InvoiceNumber@.@.VendorAcronym

InvoiceNumber&VendorAcronym
InvoiceNumber//VendorAcronym

etc. -- something like that.

I'm just wondering if there's best practice for doing anything this stupid
(hey, I'm stuck with this as our only option just now; hopefully it's only
temporary). How to parse out whatever I end up implementing -- well, it
needs to be tractable.

Thoughts?

ScottScott,

Instead of changing the existing product's tables why not create an
additional table(s) to store these values and then write your queries using
joins.

HTH

Jerry
"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127537539.cb0c208b60e605e1a298926bee12e52f@.t eranews...
> What are some good strategic approaches to using freeform text fields for
> data that needs to be queried? We have a product whose tables we can't
> change, and I need to count on a "description" field for storing a value.
> Two, actually. I'm thinking of adopting this convention:
> InvoiceNumber@.VendorAcronym
> There'd be a lot of vendors.
> Additional issue: sometimes these values would be referred to in the
> description field, and I'd need to distinguish them as referrals rather
> than as original recorded instances of the values. For that, I imagined
> either:
> InvoiceNumber@.@.VendorAcronym
> or
> InvoiceNumber&VendorAcronym
> InvoiceNumber//VendorAcronym
> etc. -- something like that.
> I'm just wondering if there's best practice for doing anything this stupid
> (hey, I'm stuck with this as our only option just now; hopefully it's only
> temporary). How to parse out whatever I end up implementing -- well, it
> needs to be tractable.
> Thoughts?
> --
> Scott|||Hi

It is not clear what you are trying to achieve, but creating a concanenated
field is probably not a good idea. Separate fields and a status will be alot
easier to maintain and understand.

John

"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127537539.cb0c208b60e605e1a298926bee12e52f@.t eranews...
> What are some good strategic approaches to using freeform text fields for
> data that needs to be queried? We have a product whose tables we can't
> change, and I need to count on a "description" field for storing a value.
> Two, actually. I'm thinking of adopting this convention:
> InvoiceNumber@.VendorAcronym
> There'd be a lot of vendors.
> Additional issue: sometimes these values would be referred to in the
> description field, and I'd need to distinguish them as referrals rather
> than as original recorded instances of the values. For that, I imagined
> either:
> InvoiceNumber@.@.VendorAcronym
> or
> InvoiceNumber&VendorAcronym
> InvoiceNumber//VendorAcronym
> etc. -- something like that.
> I'm just wondering if there's best practice for doing anything this stupid
> (hey, I'm stuck with this as our only option just now; hopefully it's only
> temporary). How to parse out whatever I end up implementing -- well, it
> needs to be tractable.
> Thoughts?
> --
> Scott|||Hi

It is not clear what you are trying to do, but creating a concatenated field
is probably not a good idea. Separate fields and a status will be far easier
to maintain and understand.

John

Jerry, John -- sorry for the lack of clarity.

We have a web app from a vendor, and in this particular case I don't want
to hack anything (I've done some hacks where practical, and will be doing
more, but there's some intractable stuff in view of upgrade management and
my available time).

Our people need to reference invoice numbers and vendors in a description
field, so I'm wanting to accommodate them until such time comes along that
the software supports something like that. But they're wanting to be able
to generate reports on the vendor and invoice numbers, so I'm wanting to do
this kludgey thing as elegantly as kludges can be. I figured if I specified
that they enter the two fields in some string format that was simple enough
for them this would be practical from their standpoint, and if I could
parse it it'd be practical from mine. The concatenation John's concerned
about is mostly as a way of avoiding separate ways of identifying the
"fields". That would be a bit onerous for the staff entering the
information, even if it did make parsing easier.

So imagine a lot of records with brief descriptions of invoices, with
vendor and invoice #s embedded in the descriptions. Oh, yeah, almost forgot
-- no chance of their being more than one of these per description (per
record).

Scott|||Hi

I am not sure why you think the users would want to enter some cryptic code
rather than exact values in two separate fields. That is more of a usability
with the UI, for instance if your parts were both numeric you may be slowing
your users down forcing them to use non-keypad characters that require
shifting. You should also look at the business processes, for instance if
you processed everything for one vendor together then retaining the vendor
information would remove the need to type it in again.

If you need to concatenate the values for display purposes then you can do
that in the code for your stored procedure or create a view.

John

"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127546922.53434faa9cedec41603cbc091e9f06a8@.t eranews...
> Jerry Spivey opined thusly on Sep 24:
>> Scott,
>>
>> Instead of changing the existing product's tables why not create an
>> additional table(s) to store these values and then write your queries
>> using
>> joins.
> Jerry, John -- sorry for the lack of clarity.
> We have a web app from a vendor, and in this particular case I don't want
> to hack anything (I've done some hacks where practical, and will be doing
> more, but there's some intractable stuff in view of upgrade management and
> my available time).
> Our people need to reference invoice numbers and vendors in a description
> field, so I'm wanting to accommodate them until such time comes along that
> the software supports something like that. But they're wanting to be able
> to generate reports on the vendor and invoice numbers, so I'm wanting to
> do
> this kludgey thing as elegantly as kludges can be. I figured if I
> specified
> that they enter the two fields in some string format that was simple
> enough
> for them this would be practical from their standpoint, and if I could
> parse it it'd be practical from mine. The concatenation John's concerned
> about is mostly as a way of avoiding separate ways of identifying the
> "fields". That would be a bit onerous for the staff entering the
> information, even if it did make parsing easier.
> So imagine a lot of records with brief descriptions of invoices, with
> vendor and invoice #s embedded in the descriptions. Oh, yeah, almost
> forgot
> -- no chance of their being more than one of these per description (per
> record).
> --
> Scott|||John Bell opined thusly on Sep 24:
> Hi
> I am not sure why you think the users would want to enter some cryptic code
> rather than exact values in two separate fields.

Developing more fields in this N-tier application whose upgrades are
controlled by a vendor whose dev I'm not privy to, isn't an option. If I
could do that, I would. I'm not posting to inquire about better
alternatives; I'm wanting to wring juice out of an unfortunate turnip.

> That is more of a usability
> with the UI, for instance if your parts were both numeric you may be slowing
> your users down forcing them to use non-keypad characters that require
> shifting. You should also look at the business processes, for instance if
> you processed everything for one vendor together then retaining the vendor
> information would remove the need to type it in again.
> If you need to concatenate the values for display purposes then you can do
> that in the code for your stored procedure or create a view.

Again, beyond my control. I've done a few necessary hacks of the software
(both the asp and some sprocs), but I can't expand the number of such edits
beyond reason. Next vendor upgrade, I'd be flailing about.

The users are more worried about being able to have reports on data they're
unfortunately left to enter in a free-form description field, type varchar.
My posting is to inquire whether a string formatting convention I'm
considering is the best I can do under that circumstance. I'd be glad of
any hints at how to most efficiently query the data as well.

Scott|||Hi

If you are not writing a new interface then you are probably better using a
fixed notation. Such as InvoiceNumber/VendorAcronym/Status. If each part is
a fixed length then you don't need a separator, but it may help the users
differentiate the different parts. I would have expect that you can
determine the fact that an invoice is a referral by a different means.

John
"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127575738.f429cee989a6daeb985244c70f0bb497@.t eranews...
> John Bell opined thusly on Sep 24:
>> Hi
>>
>> I am not sure why you think the users would want to enter some cryptic
>> code
>> rather than exact values in two separate fields.
> Developing more fields in this N-tier application whose upgrades are
> controlled by a vendor whose dev I'm not privy to, isn't an option. If I
> could do that, I would. I'm not posting to inquire about better
> alternatives; I'm wanting to wring juice out of an unfortunate turnip.
>> That is more of a usability
>> with the UI, for instance if your parts were both numeric you may be
>> slowing
>> your users down forcing them to use non-keypad characters that require
>> shifting. You should also look at the business processes, for instance if
>> you processed everything for one vendor together then retaining the
>> vendor
>> information would remove the need to type it in again.
>>
>> If you need to concatenate the values for display purposes then you can
>> do
>> that in the code for your stored procedure or create a view.
> Again, beyond my control. I've done a few necessary hacks of the software
> (both the asp and some sprocs), but I can't expand the number of such
> edits
> beyond reason. Next vendor upgrade, I'd be flailing about.
> The users are more worried about being able to have reports on data
> they're
> unfortunately left to enter in a free-form description field, type
> varchar.
> My posting is to inquire whether a string formatting convention I'm
> considering is the best I can do under that circumstance. I'd be glad of
> any hints at how to most efficiently query the data as well.
>
> --
> Scott|||John Bell opined thusly on Sep 24:
> Hi
> If you are not writing a new interface then you are probably better using a
> fixed notation. Such as InvoiceNumber/VendorAcronym/Status. If each part is
> a fixed length then you don't need a separator, but it may help the users
> differentiate the different parts. I would have expect that you can
> determine the fact that an invoice is a referral by a different means.

Well, we could get into "I would have expected" exchanges, or we can skip
the stuff I already know -- and posted in the original post. With all due
respect, "duh."

Good grief. Outta here.

Scott|||John Bell (jbellnewsposts@.hotmail.com) writes:
> If you are not writing a new interface then you are probably better
> using a fixed notation. Such as InvoiceNumber/VendorAcronym/Status. If
> each part is a fixed length then you don't need a separator, but it may
> help the users differentiate the different parts. I would have expect
> that you can determine the fact that an invoice is a referral by a
> different means.

Now, how is that for a user interface? This is free text, and users will
mangle it anyway, but they have better odds with a separator than fixed
length.

I have not intervened into this thread before, because I don't have
much advice to offer Steve in this dire situation.

Hm, possibly could have a trigger on the table that loaded a new table
with the fields split in pieces. Still iffy with regards to the vendor,
but at least no existing object is changed. (Then again, being on the
vendor side of the fence, I know what I would think if I found that one
of our customers had added a trigger to one of our tables.)

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Scott Marquardt opined thusly on Sep 24:
> John Bell opined thusly on Sep 24:
>> Hi
>>
>> If you are not writing a new interface then you are probably better using a
>> fixed notation. Such as InvoiceNumber/VendorAcronym/Status. If each part is
>> a fixed length then you don't need a separator, but it may help the users
>> differentiate the different parts. I would have expect that you can
>> determine the fact that an invoice is a referral by a different means.
> Well, we could get into "I would have expected" exchanges, or we can skip
> the stuff I already know -- and posted in the original post. With all due
> respect, "duh."
> Good grief. Outta here.

John., I'm going to apologize for a short temper there. It was born of
impatience.

My principal interest was in hearing how anyone else might PARSE the field
to recover the data. Erland's trigger idea isn't bad for WHAT I do with it.

"Invoice for 1/2 inch puce widgets and a can of beans, 100-3123@.WidgetMart"

"Refund for can of beans, 100-3123&WidgetMart"

The second one would need to be tagged as a referral to the first one. But
if that's my basic technique, they're parse out the same way.

Scott|||>> My principal interest was in hearing how anyone else might PARSE the field to recover the data. Erland's trigger idea isn't bad for WHAT I do with it. "Invoice for 1/2 inch puce widgets and a can of beans, 100-3123@.WidgetMart" <<

Since free text requires human intelligence, we get people to encode
it. If the text is short and styled the same way, you can find AI
programs that are pretty good these days. Of course this has nothing
to do with RDBMS and nobody in their right mind would try to do it in
SQL.|||Scott,

Have you considered an ActiveX script in a DTS package to shred the
data periodically (you can use the SQL Scheduler to run it once a
minute). We do this to parse out syslog tables; it's kludgy, but its
consistent. While not as immediate as a trigger to populate a table,
it does keep you from modifying the vendor database (which, if your
vendor has the ability to update their database, could be a big
benefit).

Obviously, your users would have to be trained on how to enter data
consistently, and you'd probably want some sort of validation to pick
up on coded entries that don't meant your
data-followed-by-a-comma-followed-by-more-data rules. You may even
look at Regular Expressions inside your DTS package to help with that.

Just thinking aloud,
Stu|||Hi Erland/Steve

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96DBF14D14D5AYazorman@.127.0.0.1...
> John Bell (jbellnewsposts@.hotmail.com) writes:
>> If you are not writing a new interface then you are probably better
>> using a fixed notation. Such as InvoiceNumber/VendorAcronym/Status. If
>> each part is a fixed length then you don't need a separator, but it may
>> help the users differentiate the different parts. I would have expect
>> that you can determine the fact that an invoice is a referral by a
>> different means.
>
> Now, how is that for a user interface? This is free text, and users will
> mangle it anyway, but they have better odds with a separator than fixed
> length.

That was my point, guess I should have said it that way! The users will
(probably) not like having to enter a coded string, even if you keep it as
simple! They may even deliberately mangle it if there is no value in it for
themselves.

> I have not intervened into this thread before, because I don't have
> much advice to offer Steve in this dire situation.
It seems to me, that if this an off the shelf package the benefits has been
lost some time ago, if this is a bespoke development then it has not been
specified thoroughly enough.

> Hm, possibly could have a trigger on the table that loaded a new table
> with the fields split in pieces. Still iffy with regards to the vendor,
> but at least no existing object is changed. (Then again, being on the
> vendor side of the fence, I know what I would think if I found that one
> of our customers had added a trigger to one of our tables.)

Writing a trigger may still invalidate the contract as the transactions will
be effected, the vendor may have the right to charge improportate fees to
fix a problem even if a problem is not related to this table. But if Steve
has already tweeked things that could already be the case! A trigger would
be useful for validation even if the replication route is not taken. Then
again Steve was worried about upgrades breaking things.

Dire may be an understatement!

Steve has my sympathy.

John

> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Nothing seems to be going right in this thread!

Who is Steve?

Sorry Scott!

John

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:43364302$0$14057$da0feed9@.news.zen.co.uk...
> Hi Erland/Steve
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns96DBF14D14D5AYazorman@.127.0.0.1...
>> John Bell (jbellnewsposts@.hotmail.com) writes:
>>> If you are not writing a new interface then you are probably better
>>> using a fixed notation. Such as InvoiceNumber/VendorAcronym/Status. If
>>> each part is a fixed length then you don't need a separator, but it may
>>> help the users differentiate the different parts. I would have expect
>>> that you can determine the fact that an invoice is a referral by a
>>> different means.
>>
>>
>> Now, how is that for a user interface? This is free text, and users will
>> mangle it anyway, but they have better odds with a separator than fixed
>> length.
> That was my point, guess I should have said it that way! The users will
> (probably) not like having to enter a coded string, even if you keep it as
> simple! They may even deliberately mangle it if there is no value in it
> for themselves.
>>
>> I have not intervened into this thread before, because I don't have
>> much advice to offer Steve in this dire situation.
>>
> It seems to me, that if this an off the shelf package the benefits has
> been lost some time ago, if this is a bespoke development then it has not
> been specified thoroughly enough.
>> Hm, possibly could have a trigger on the table that loaded a new table
>> with the fields split in pieces. Still iffy with regards to the vendor,
>> but at least no existing object is changed. (Then again, being on the
>> vendor side of the fence, I know what I would think if I found that one
>> of our customers had added a trigger to one of our tables.)
> Writing a trigger may still invalidate the contract as the transactions
> will be effected, the vendor may have the right to charge improportate
> fees to fix a problem even if a problem is not related to this table. But
> if Steve has already tweeked things that could already be the case! A
> trigger would be useful for validation even if the replication route is
> not taken. Then again Steve was worried about upgrades breaking things.
> Dire may be an understatement!
> Steve has my sympathy.
> John
>>
>> --
>> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>>
>> Books Online for SQL Server SP3 at
>> http://www.microsoft.com/sql/techin.../2000/books.asp
>>|||Hi Scott

Non taken!

You can't always give good advice, sometimes the best you can do is throw
ideas into the pot.

Using a scheduled job may be less of an preblem when considering
invalidating the contract! Although running it once a minute may cause
issues such as blocking.

I have assumed that the information you are entering is not available on
this system, therefore it is not possible to pick it up at report time from
other fields.

John

"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127610853.841b341db0737c3d0f8b5850b289ecae@.t eranews...
> Scott Marquardt opined thusly on Sep 24:
>> John Bell opined thusly on Sep 24:
>>> Hi
>>>
>>> If you are not writing a new interface then you are probably better
>>> using a
>>> fixed notation. Such as InvoiceNumber/VendorAcronym/Status. If each part
>>> is
>>> a fixed length then you don't need a separator, but it may help the
>>> users
>>> differentiate the different parts. I would have expect that you can
>>> determine the fact that an invoice is a referral by a different means.
>>
>> Well, we could get into "I would have expected" exchanges, or we can skip
>> the stuff I already know -- and posted in the original post. With all due
>> respect, "duh."
>>
>> Good grief. Outta here.
> John., I'm going to apologize for a short temper there. It was born of
> impatience.
> My principal interest was in hearing how anyone else might PARSE the field
> to recover the data. Erland's trigger idea isn't bad for WHAT I do with
> it.
> "Invoice for 1/2 inch puce widgets and a can of beans,
> 100-3123@.WidgetMart"
> "Refund for can of beans, 100-3123&WidgetMart"
> The second one would need to be tagged as a referral to the first one. But
> if that's my basic technique, they're parse out the same way.
> --
> Scott|||--CELKO-- opined thusly on Sep 24:
>>> My principal interest was in hearing how anyone else might PARSE the field to recover the data. Erland's trigger idea isn't bad for WHAT I do with it. "Invoice for 1/2 inch puce widgets and a can of beans, 100-3123@.WidgetMart" <<
> Since free text requires human intelligence, we get people to encode
> it. If the text is short and styled the same way, you can find AI
> programs that are pretty good these days. Of course this has nothing
> to do with RDBMS and nobody in their right mind would try to do it in
> SQL.

Hey! How'd you find out about my condition?

;-)

Scott|||Stu opined thusly on Sep 24:
> Scott,
> Have you considered an ActiveX script in a DTS package to shred the
> data periodically (you can use the SQL Scheduler to run it once a
> minute). We do this to parse out syslog tables; it's kludgy, but its
> consistent. While not as immediate as a trigger to populate a table,
> it does keep you from modifying the vendor database (which, if your
> vendor has the ability to update their database, could be a big
> benefit).
> Obviously, your users would have to be trained on how to enter data
> consistently, and you'd probably want some sort of validation to pick
> up on coded entries that don't meant your
> data-followed-by-a-comma-followed-by-more-data rules. You may even
> look at Regular Expressions inside your DTS package to help with that.
> Just thinking aloud,
> Stu

Less intrusive, yeah. I'll be needing to roll my own shredder, though --
unless you'd care to name some good controls. Haven't tried any, ever,
really. It's been nice to have never needed to!

I anticipate we'll see now more than 3000 such records a year, so this is
probably something that could be done at runtime. Users won't be running
anything that queries a result of this, so parsing it all out when running
reports (not sure where to implement that, yet, but it won't be in the
application itself) is an inelegant but tenable option.

Scott|||John Bell opined thusly on Sep 25:

> That was my point, guess I should have said it that way! The users will
> (probably) not like having to enter a coded string, even if you keep it as
> simple! They may even deliberately mangle it if there is no value in it for
> themselves.

In this case, the only users needing to enter these have the only stake in
these particular data points. This isn't something they'll grumble about;
in this case, they're the ones demanding it. ;-)

The vendor's likely to be cool with this. I'm documenting things pretty
well, and a few of my other hacks to the system may end up as features.
They have an aggressive development cycle and are responsive. Best of
worlds for trying stuff.

Steve ;-)|||Scott Marquardt at wit's-end:

> Less intrusive, yeah. I'll be needing to roll my own shredder, though --
> unless you'd care to name some good controls. Haven't tried any, ever,
> really. It's been nice to have never needed to!

> I anticipate we'll see now more than 3000 such records a year, so this is
> probably something that could be done at runtime. Users won't be running
> anything that queries a result of this, so parsing it all out when running
> reports (not sure where to implement that, yet, but it won't be in the
> application itself) is an inelegant but tenable option.

More stuff to drive you even crazier - Log Parser from Microsoft:
http://www.microsoft.com/technet/sc...er/default.mspx

It'll either drive you over the edge, or restore your sanity.

GeoSynch|||GeoSynch opined thusly on Sep 27:
> Scott Marquardt at wit's-end:
>> Less intrusive, yeah. I'll be needing to roll my own shredder, though --
>> unless you'd care to name some good controls. Haven't tried any, ever,
>> really. It's been nice to have never needed to!
>> I anticipate we'll see now more than 3000 such records a year, so this is
>> probably something that could be done at runtime. Users won't be running
>> anything that queries a result of this, so parsing it all out when running
>> reports (not sure where to implement that, yet, but it won't be in the
>> application itself) is an inelegant but tenable option.
> More stuff to drive you even crazier - Log Parser from Microsoft:
> http://www.microsoft.com/technet/sc...er/default.mspx
> It'll either drive you over the edge, or restore your sanity.

No. You don't understand. We're so understaffed and overbooked that I could
have this operating right now somewhere, in a scheduled task or a script,
and I might not even know about it. I can't begin to count the number of
things we've been grateful for and implemented without documenting it.

That's the world of education, folks! ;-)

Hey! Download to the usual folder asks whether to overwrite. Guess I've got
it already! ;-)

Scott|||Scott Marquardt opined thusly on Sep 25:
> John Bell opined thusly on Sep 25:
>> That was my point, guess I should have said it that way! The users will
>> (probably) not like having to enter a coded string, even if you keep it as
>> simple! They may even deliberately mangle it if there is no value in it for
>> themselves.
> In this case, the only users needing to enter these have the only stake in
> these particular data points. This isn't something they'll grumble about;
> in this case, they're the ones demanding it. ;-)

OK, this is maybe (maybe) the kludgiest thing I've done in a while. If
anyone has any ideas for cleaning it up, I'm all ears. It works, though.

| select
| case
| when charindex(' ',ltrim(rtrim(comment))) < charindex('@.',ltrim(rtrim(comment))) and charindex(' ',ltrim(rtrim(comment))) <> 0 then
| right(left(comment, charindex('@.',ltrim(rtrim(comment)))-1) ,charindex(' ', reverse(left( ltrim(rtrim(comment)), charindex('@.',ltrim(rtrim(comment)))-2))))
| else
| left( ltrim(rtrim(comment)), charindex('@.',ltrim(rtrim(comment)))-1)
| end
| as InvoiceNumber,
| case
| when charindex(' ',ltrim(rtrim(reverse(comment)))) < charindex('@.',ltrim(rtrim(reverse(comment)))) and charindex(' ',ltrim(rtrim(comment))) <> 0 then
| left(right(comment, charindex('@.',ltrim(rtrim(reverse(comment))))-1),charindex(' ',ltrim(rtrim(reverse(comment)))))
| else
| reverse(left( ltrim(rtrim(reverse(comment))), charindex('@.',ltrim(rtrim(reverse(comment))))-1))
| end
| as VendorID, case when charindex('@.@.',comment) > 0 then 'True' else 'False' end as Reference, comment
| from bill_lineitems
| where comment like '%@.%'

That translates into this:

This allows for putting an invoice@.vendor (or invoice@.@.vendor) anywhere in
a description field, so long as it's set off on either side by spaces or
appears at the very beginning or end of the string. You can't, for example,
use it at the end of a grammatical sentence and append a period.

These work:

invoice@.vendor yada yada yada
yada yada yada invoice@.vendor
yada yada yada invoice@.vendor yada yada yada
yada yada yada invoice@.vendor yada yada yada
invoice@.vendor
invoice@.vendor [with spaces at the end as well]

These will not work:

There are problems with this invoice@.vendor!
Use the invoice that was not paid (invoice@.vendor)

Also, you can't use more than one such thing in a single record's
description.

Scott

parsing varchar fields

What are some good strategic approaches to using freeform text fields for
data that needs to be queried? We have a product whose tables we can't
change, and I need to count on a "description" field for storing a value.
Two, actually. I'm thinking of adopting this convention:
InvoiceNumber@.VendorAcronym
There'd be a lot of vendors.
Additional issue: sometimes these values would be referred to in the
description field, and I'd need to distinguish them as referrals rather
than as original recorded instances of the values. For that, I imagined
either:
InvoiceNumber@.@.VendorAcronym
or
InvoiceNumber&VendorAcronym
InvoiceNumber//VendorAcronym
etc. -- something like that.
I'm just wondering if there's best practice for doing anything this stupid
(hey, I'm stuck with this as our only option just now; hopefully it's only
temporary). How to parse out whatever I end up implementing -- well, it
needs to be tractable.
Thoughts?
ScottScott,
Instead of changing the existing product's tables why not create an
additional table(s) to store these values and then write your queries using
joins.
HTH
Jerry
"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127537539. cb0c208b60e605e1a298926bee12e52f@.teranew
s...
> What are some good strategic approaches to using freeform text fields for
> data that needs to be queried? We have a product whose tables we can't
> change, and I need to count on a "description" field for storing a value.
> Two, actually. I'm thinking of adopting this convention:
> InvoiceNumber@.VendorAcronym
> There'd be a lot of vendors.
> Additional issue: sometimes these values would be referred to in the
> description field, and I'd need to distinguish them as referrals rather
> than as original recorded instances of the values. For that, I imagined
> either:
> InvoiceNumber@.@.VendorAcronym
> or
> InvoiceNumber&VendorAcronym
> InvoiceNumber//VendorAcronym
> etc. -- something like that.
> I'm just wondering if there's best practice for doing anything this stupid
> (hey, I'm stuck with this as our only option just now; hopefully it's only
> temporary). How to parse out whatever I end up implementing -- well, it
> needs to be tractable.
> Thoughts?
> --
> Scott|||Hi
It is not clear what you are trying to do, but creating a concatenated field
is probably not a good idea. Separate fields and a status will be far easier
to maintain and understand.
John
"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127537539. cb0c208b60e605e1a298926bee12e52f@.teranew
s...
> What are some good strategic approaches to using freeform text fields for
> data that needs to be queried? We have a product whose tables we can't
> change, and I need to count on a "description" field for storing a value.
> Two, actually. I'm thinking of adopting this convention:
> InvoiceNumber@.VendorAcronym
> There'd be a lot of vendors.
> Additional issue: sometimes these values would be referred to in the
> description field, and I'd need to distinguish them as referrals rather
> than as original recorded instances of the values. For that, I imagined
> either:
> InvoiceNumber@.@.VendorAcronym
> or
> InvoiceNumber&VendorAcronym
> InvoiceNumber//VendorAcronym
> etc. -- something like that.
> I'm just wondering if there's best practice for doing anything this stupid
> (hey, I'm stuck with this as our only option just now; hopefully it's only
> temporary). How to parse out whatever I end up implementing -- well, it
> needs to be tractable.
> Thoughts?
> --
> Scott|||Hi
It is not clear what you are trying to achieve, but creating a concanenated
field is probably not a good idea. Separate fields and a status will be alot
easier to maintain and understand.
John
"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127537539. cb0c208b60e605e1a298926bee12e52f@.teranew
s...
> What are some good strategic approaches to using freeform text fields for
> data that needs to be queried? We have a product whose tables we can't
> change, and I need to count on a "description" field for storing a value.
> Two, actually. I'm thinking of adopting this convention:
> InvoiceNumber@.VendorAcronym
> There'd be a lot of vendors.
> Additional issue: sometimes these values would be referred to in the
> description field, and I'd need to distinguish them as referrals rather
> than as original recorded instances of the values. For that, I imagined
> either:
> InvoiceNumber@.@.VendorAcronym
> or
> InvoiceNumber&VendorAcronym
> InvoiceNumber//VendorAcronym
> etc. -- something like that.
> I'm just wondering if there's best practice for doing anything this stupid
> (hey, I'm stuck with this as our only option just now; hopefully it's only
> temporary). How to parse out whatever I end up implementing -- well, it
> needs to be tractable.
> Thoughts?
> --
> Scott|||Jerry Spivey opined thusly on Sep 24:
> Scott,
> Instead of changing the existing product's tables why not create an
> additional table(s) to store these values and then write your queries usin
g
> joins.
Jerry, John -- sorry for the lack of clarity.
We have a web app from a vendor, and in this particular case I don't want
to hack anything (I've done some hacks where practical, and will be doing
more, but there's some intractable stuff in view of upgrade management and
my available time).
Our people need to reference invoice numbers and vendors in a description
field, so I'm wanting to accommodate them until such time comes along that
the software supports something like that. But they're wanting to be able
to generate reports on the vendor and invoice numbers, so I'm wanting to do
this kludgey thing as elegantly as kludges can be. I figured if I specified
that they enter the two fields in some string format that was simple enough
for them this would be practical from their standpoint, and if I could
parse it it'd be practical from mine. The concatenation John's concerned
about is mostly as a way of avoiding separate ways of identifying the
"fields". That would be a bit onerous for the staff entering the
information, even if it did make parsing easier.
So imagine a lot of records with brief descriptions of invoices, with
vendor and invoice #s embedded in the descriptions. Oh, yeah, almost forgot
-- no chance of their being more than one of these per description (per
record).
Scott|||Hi
I am not sure why you think the users would want to enter some cryptic code
rather than exact values in two separate fields. That is more of a usability
with the UI, for instance if your parts were both numeric you may be slowing
your users down forcing them to use non-keypad characters that require
shifting. You should also look at the business processes, for instance if
you processed everything for one vendor together then retaining the vendor
information would remove the need to type it in again.
If you need to concatenate the values for display purposes then you can do
that in the code for your stored procedure or create a view.
John
"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127546922. 53434faa9cedec41603cbc091e9f06a8@.teranew
s...
> Jerry Spivey opined thusly on Sep 24:
> Jerry, John -- sorry for the lack of clarity.
> We have a web app from a vendor, and in this particular case I don't want
> to hack anything (I've done some hacks where practical, and will be doing
> more, but there's some intractable stuff in view of upgrade management and
> my available time).
> Our people need to reference invoice numbers and vendors in a description
> field, so I'm wanting to accommodate them until such time comes along that
> the software supports something like that. But they're wanting to be able
> to generate reports on the vendor and invoice numbers, so I'm wanting to
> do
> this kludgey thing as elegantly as kludges can be. I figured if I
> specified
> that they enter the two fields in some string format that was simple
> enough
> for them this would be practical from their standpoint, and if I could
> parse it it'd be practical from mine. The concatenation John's concerned
> about is mostly as a way of avoiding separate ways of identifying the
> "fields". That would be a bit onerous for the staff entering the
> information, even if it did make parsing easier.
> So imagine a lot of records with brief descriptions of invoices, with
> vendor and invoice #s embedded in the descriptions. Oh, yeah, almost
> forgot
> -- no chance of their being more than one of these per description (per
> record).
> --
> Scott|||John Bell opined thusly on Sep 24:
> Hi
> I am not sure why you think the users would want to enter some cryptic cod
e
> rather than exact values in two separate fields.
Developing more fields in this N-tier application whose upgrades are
controlled by a vendor whose dev I'm not privy to, isn't an option. If I
could do that, I would. I'm not posting to inquire about better
alternatives; I'm wanting to wring juice out of an unfortunate turnip.

> That is more of a usability
> with the UI, for instance if your parts were both numeric you may be slowi
ng
> your users down forcing them to use non-keypad characters that require
> shifting. You should also look at the business processes, for instance if
> you processed everything for one vendor together then retaining the vendor
> information would remove the need to type it in again.
> If you need to concatenate the values for display purposes then you can do
> that in the code for your stored procedure or create a view.
Again, beyond my control. I've done a few necessary hacks of the software
(both the asp and some sprocs), but I can't expand the number of such edits
beyond reason. Next vendor upgrade, I'd be flailing about.
The users are more worried about being able to have reports on data they're
unfortunately left to enter in a free-form description field, type varchar.
My posting is to inquire whether a string formatting convention I'm
considering is the best I can do under that circumstance. I'd be glad of
any hints at how to most efficiently query the data as well.
Scott|||Hi
If you are not writing a new interface then you are probably better using a
fixed notation. Such as InvoiceNumber/VendorAcronym/Status. If each part is
a fixed length then you don't need a separator, but it may help the users
differentiate the different parts. I would have expect that you can
determine the fact that an invoice is a referral by a different means.
John
"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127575738. f429cee989a6daeb985244c70f0bb497@.teranew
s...
> John Bell opined thusly on Sep 24:
> Developing more fields in this N-tier application whose upgrades are
> controlled by a vendor whose dev I'm not privy to, isn't an option. If I
> could do that, I would. I'm not posting to inquire about better
> alternatives; I'm wanting to wring juice out of an unfortunate turnip.
>
> Again, beyond my control. I've done a few necessary hacks of the software
> (both the asp and some sprocs), but I can't expand the number of such
> edits
> beyond reason. Next vendor upgrade, I'd be flailing about.
> The users are more worried about being able to have reports on data
> they're
> unfortunately left to enter in a free-form description field, type
> varchar.
> My posting is to inquire whether a string formatting convention I'm
> considering is the best I can do under that circumstance. I'd be glad of
> any hints at how to most efficiently query the data as well.
>
> --
> Scott|||John Bell opined thusly on Sep 24:
> Hi
> If you are not writing a new interface then you are probably better using
a
> fixed notation. Such as InvoiceNumber/VendorAcronym/Status. If each part i
s
> a fixed length then you don't need a separator, but it may help the users
> differentiate the different parts. I would have expect that you can
> determine the fact that an invoice is a referral by a different means.
Well, we could get into "I would have expected" exchanges, or we can skip
the stuff I already know -- and posted in the original post. With all due
respect, "duh."
Good grief. Outta here.
Scott|||John Bell (jbellnewsposts@.hotmail.com) writes:
> If you are not writing a new interface then you are probably better
> using a fixed notation. Such as InvoiceNumber/VendorAcronym/Status. If
> each part is a fixed length then you don't need a separator, but it may
> help the users differentiate the different parts. I would have expect
> that you can determine the fact that an invoice is a referral by a
> different means.
Now, how is that for a user interface? This is free text, and users will
mangle it anyway, but they have better odds with a separator than fixed
length.
I have not intervened into this thread before, because I don't have
much advice to offer Steve in this dire situation.
Hm, possibly could have a trigger on the table that loaded a new table
with the fields split in pieces. Still iffy with regards to the vendor,
but at least no existing object is changed. (Then again, being on the
vendor side of the fence, I know what I would think if I found that one
of our customers had added a trigger to one of our tables.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Parse to first non-zero

Hello,
If I have a field that contains varchar type data which looks like '00312'
How can I parse the field to just display '312' ?
Thanks!
Patricedeclare @.i varchar(5)
select @.i ='00123'
select convert(int,@.i)
Convert it to an int
http://sqlservercode.blogspot.com/
"Patrice" wrote:

> Hello,
> If I have a field that contains varchar type data which looks like '00312'
> How can I parse the field to just display '312' ?
> Thanks!
> Patrice|||Patrice,
One way...try:
SELECT CAST(<COLUMN> AS INT) FROM <TABLE>
HTH
Jerry
"Patrice" <Patrice@.discussions.microsoft.com> wrote in message
news:4B70A8AF-6828-4C30-9AE0-7B2DD3E8FF37@.microsoft.com...
> Hello,
> If I have a field that contains varchar type data which looks like '00312'
> How can I parse the field to just display '312' ?
> Thanks!
> Patrice|||Another way, which is a bit safer, though not tremendously:
declare @.char10 char(10)
set @.char10 = '0000001234'
select substring(@.char10, patindex('%[^0a-z]%',@.char10), len(@.char10))
I just look for the first non zero (and I tossed in letter just for kicks)
character and substring from there. No conversion needed, so if you need to
look for
'00000012BC'
it will return:
12BC
instead of crashing out.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Patrice" <Patrice@.discussions.microsoft.com> wrote in message
news:4B70A8AF-6828-4C30-9AE0-7B2DD3E8FF37@.microsoft.com...
> Hello,
> If I have a field that contains varchar type data which looks like '00312'
> How can I parse the field to just display '312' ?
> Thanks!
> Patrice|||In a tiered architecture, display is always done in the front end and
NEVER in the database. This more fundamental that just SQL; don't you
remember this from your first Software Engineering course?
The stinking dirty kludge is to CAST() the string to INTEGER. But
since you did not know that a column is not anything like field, you
probably have serious design problems in many place where you have

string and other data types.|||So to quote Bobby Boucher's mamma in the Waterboy, "CAST IS THE DEVIL!!!"
:-)
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1129761421.858642.3380@.g47g2000cwa.googlegroups.com...
> In a tiered architecture, display is always done in the front end and
> NEVER in the database. This more fundamental that just SQL; don't you
> remember this from your first Software Engineering course?
> The stinking dirty kludge is to CAST() the string to INTEGER. But
> since you did not know that a column is not anything like field, you
> probably have serious design problems in many place where you have
>

string and other data types.
>|||That was Kathy Bates! I had forgotten that Movie!
But I do remember my wife and I going to some movie years ago (forgot
the title) where George Cluney as an Army Officier and Nicole Kidman as
an atomic scientist trying to find an atomic bomb. When Kidman came on
the screen we both looked at each other and said "That [atomic bomb
expert] should have been Kathy Bates!"
But getting back from the flashback, the problem with CAST() is that
host languages do not all agree on how to handle SQL data types. Doing
the CAST() in SQL to feed it to an unknown host language conversion
adds overhead and makes results lesspredictable when someone looks at
the schema to figure out what happened. A version of the copy of a
copy of a copy.. problem.|||Yeah, I don't know why didn't think to say this. I got more interested in
just doing something other than cast. The word "display" should have jogged
something in my brain.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1129761421.858642.3380@.g47g2000cwa.googlegroups.com...
> In a tiered architecture, display is always done in the front end and
> NEVER in the database. This more fundamental that just SQL; don't you
> remember this from your first Software Engineering course?
> The stinking dirty kludge is to CAST() the string to INTEGER. But
> since you did not know that a column is not anything like field, you
> probably have serious design problems in many place where you have
>

string and other data types.
>

Friday, March 30, 2012

Partial Indexes

Partial Indexes

Wednesday, March 28, 2012

parsing varchar fields

parsing varchar fields

Parse to first non-zero

Parameterized Expression

Blog Archive

About Me