SQL Server Performance Forum – Threads Archive

index help

I have an index we use for Oracle as follows: CREATE UNIQUE INDEX X_AK9_SE30
ON S_ACCOUNT_E30 (to_char(ACCOUNT_NUMBER)) I cannot seem to construct a similar index for sqlServer. Can someone guide me. ACCOUNT_NUMBER is defined as BIGINT NOT NULL.

Do you want to create an index converting from bigint to char?
Luis Martin
Moderator
SQL-Server-Performance.com Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
Leonardo Da Vinci Nunca esperes el reconocimiento de tus hijos, eso ocurrirÃ¡ luego de tu muerte
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

I am now very curious … why not CREATE UNIQUE INDEX X_AK9_SE30
ON S_ACCOUNT_E30 (ACCOUNT_NUMBER) ??

taaSarge wrote the following in wrong post. yes… actually we already have an index on the Account_number as a bigint.
but the customer wants to search to work as if the number is a character (which works a bit differently). Luis Martin
Moderator
SQL-Server-Performance.com Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
Leonardo Da Vinci Nunca esperes el reconocimiento de tus hijos, eso ocurrirÃ¡ luego de tu muerte
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

The only way I know to do that is to make a computed column that does the conversion, then index that. There are some restrictions on connection settings, etc. with indexed computed columns, so it should be done with care and testing.

It’s odd, but it seems to work: CREATE TABLE aTable(
aNumber int NOT NULL,
aNumberAsChar AS (CONVERT(char(10),aNumber,0))
) insert into aTable
select 10028365
union
select 10128365
union
select 25028365
union
select 10254365 create index ix_number_as_char on aTable( aNumberAsChar ) select aNumberAsChar from aTable

Another alternative might be an indexed view. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

creating the additional string column was the way we originally coded to resolve this problem. But then our new Oracle dba reviewed the process and suggested his solution with the conversion of the data via the index. His claim is we have just one less column of data to maintain and store in the database. But of course his solution does not work if sqlServer does not function the same way.

I suppose you would be looking at more overhead to do the conversion at query run time? John

Indeed, it appears as though doing the conversion at run time still results in an index scan as opposed to seek. John

There it lies the difference between SQL & Oracle, and with few tweaks you can get required optimization on the database. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by satya There it lies the difference between SQL & Oracle, and with few tweaks you can get required optimization on the database. <hr noshade size="1">Satya SKJ Microsoft SQL Server MVP Contributing Editor & Forums Moderator http://www.SQL-Server-Performance.Com</a> <center>This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.</center> <hr height="1" noshade id="quote"></blockquote id="quote"> Actually I would rather say, you should always use the appropriate data type. The fact that Oracle allows such things doesn’t give them more sense. [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a> Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

]]>