Easy way to index a column with URLs

We have a column that stores URL (varchar), which ultimately are unique
within the table (though an identity is the actual primary key). Is it good
practice to put an index on such a wide column? Are there are other
alternatives, possibly different data type to use?If you need to enforce uniqueness, or you are frequently searching the table
by the URL column, then an index is appropriate. Bear in mind that an index
key can be no more than 900 bytes.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Kevin" <none@.none.com> wrote in message
news:4671898C-0CAC-4396-85F1-BA45E527D8E5@.microsoft.com...
We have a column that stores URL (varchar), which ultimately are unique
within the table (though an identity is the actual primary key). Is it good
practice to put an index on such a wide column? Are there are other
alternatives, possibly different data type to use?|||I'm afraid that some of the urls possibly could get over that size after
encoding (the URL column is nvarchar).
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:O0MdobtnHHA.1476@.TK2MSFTNGP03.phx.gbl...
> If you need to enforce uniqueness, or you are frequently searching the
> table
> by the URL column, then an index is appropriate. Bear in mind that an
> index
> key can be no more than 900 bytes.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Kevin" <none@.none.com> wrote in message
> news:4671898C-0CAC-4396-85F1-BA45E527D8E5@.microsoft.com...
> We have a column that stores URL (varchar), which ultimately are unique
> within the table (though an identity is the actual primary key). Is it
> good
> practice to put an index on such a wide column? Are there are other
> alternatives, possibly different data type to use?
>|||"Kevin" <none@.none.com> wrote in message
news:0DCDCCBA-8397-46D2-A860-8432E352277B@.microsoft.com...
> I'm afraid that some of the urls possibly could get over that size after
> encoding (the URL column is nvarchar).
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:O0MdobtnHHA.1476@.TK2MSFTNGP03.phx.gbl...
>> If you need to enforce uniqueness, or you are frequently searching the
>> table
>> by the URL column, then an index is appropriate. Bear in mind that an
>> index
>> key can be no more than 900 bytes.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> https://mvp.support.microsoft.com/profile/Tom.Moreau
>>
>> "Kevin" <none@.none.com> wrote in message
>> news:4671898C-0CAC-4396-85F1-BA45E527D8E5@.microsoft.com...
>> We have a column that stores URL (varchar), which ultimately are unique
>> within the table (though an identity is the actual primary key). Is it
>> good
>> practice to put an index on such a wide column? Are there are other
>> alternatives, possibly different data type to use?
>|||Kevin,
if you badly want this column be indexed (of those values that do not excede
the 900 bites limit), you can do some more work:
create an indexed view that have a pk column and the URL column, with the
URL records no longer than 900 bites, index on the URL column;
create an indexed view that have a pk column and the URL column, with the
URL records longer than 900 bites, index on the pk column;
chances are that you may have a handful of records in the second view so
that you do not care to index scan it. And you have index seek on the
shorter URL values. Then join the views with your base table.
HTH
Quentin
"Kevin" <none@.none.com> wrote in message
news:0DCDCCBA-8397-46D2-A860-8432E352277B@.microsoft.com...
> I'm afraid that some of the urls possibly could get over that size after
> encoding (the URL column is nvarchar).
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:O0MdobtnHHA.1476@.TK2MSFTNGP03.phx.gbl...
>> If you need to enforce uniqueness, or you are frequently searching the
>> table
>> by the URL column, then an index is appropriate. Bear in mind that an
>> index
>> key can be no more than 900 bytes.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> https://mvp.support.microsoft.com/profile/Tom.Moreau
>>
>> "Kevin" <none@.none.com> wrote in message
>> news:4671898C-0CAC-4396-85F1-BA45E527D8E5@.microsoft.com...
>> We have a column that stores URL (varchar), which ultimately are unique
>> within the table (though an identity is the actual primary key). Is it
>> good
>> practice to put an index on such a wide column? Are there are other
>> alternatives, possibly different data type to use?
>sql

Easy way to index a column with URLs

We have a column that stores URL (varchar), which ultimately are unique
within the table (though an identity is the actual primary key). Is it good
practice to put an index on such a wide column? Are there are other
alternatives, possibly different data type to use?
If you need to enforce uniqueness, or you are frequently searching the table
by the URL column, then an index is appropriate. Bear in mind that an index
key can be no more than 900 bytes.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Kevin" <none@.none.com> wrote in message
news:4671898C-0CAC-4396-85F1-BA45E527D8E5@.microsoft.com...
We have a column that stores URL (varchar), which ultimately are unique
within the table (though an identity is the actual primary key). Is it good
practice to put an index on such a wide column? Are there are other
alternatives, possibly different data type to use?
|||I'm afraid that some of the urls possibly could get over that size after
encoding (the URL column is nvarchar).
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:O0MdobtnHHA.1476@.TK2MSFTNGP03.phx.gbl...
> If you need to enforce uniqueness, or you are frequently searching the
> table
> by the URL column, then an index is appropriate. Bear in mind that an
> index
> key can be no more than 900 bytes.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Kevin" <none@.none.com> wrote in message
> news:4671898C-0CAC-4396-85F1-BA45E527D8E5@.microsoft.com...
> We have a column that stores URL (varchar), which ultimately are unique
> within the table (though an identity is the actual primary key). Is it
> good
> practice to put an index on such a wide column? Are there are other
> alternatives, possibly different data type to use?
>
|||"Kevin" <none@.none.com> wrote in message
news:0DCDCCBA-8397-46D2-A860-8432E352277B@.microsoft.com...
> I'm afraid that some of the urls possibly could get over that size after
> encoding (the URL column is nvarchar).
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:O0MdobtnHHA.1476@.TK2MSFTNGP03.phx.gbl...
>
|||Kevin,
if you badly want this column be indexed (of those values that do not excede
the 900 bites limit), you can do some more work:
create an indexed view that have a pk column and the URL column, with the
URL records no longer than 900 bites, index on the URL column;
create an indexed view that have a pk column and the URL column, with the
URL records longer than 900 bites, index on the pk column;
chances are that you may have a handful of records in the second view so
that you do not care to index scan it. And you have index seek on the
shorter URL values. Then join the views with your base table.
HTH
Quentin
"Kevin" <none@.none.com> wrote in message
news:0DCDCCBA-8397-46D2-A860-8432E352277B@.microsoft.com...
> I'm afraid that some of the urls possibly could get over that size after
> encoding (the URL column is nvarchar).
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:O0MdobtnHHA.1476@.TK2MSFTNGP03.phx.gbl...
>

Easy way to index a column with URLs

We have a column that stores URL (varchar), which ultimately are unique
within the table (though an identity is the actual primary key). Is it good
practice to put an index on such a wide column? Are there are other
alternatives, possibly different data type to use?If you need to enforce uniqueness, or you are frequently searching the table
by the URL column, then an index is appropriate. Bear in mind that an index
key can be no more than 900 bytes.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Kevin" <none@.none.com> wrote in message
news:4671898C-0CAC-4396-85F1-BA45E527D8E5@.microsoft.com...
We have a column that stores URL (varchar), which ultimately are unique
within the table (though an identity is the actual primary key). Is it good
practice to put an index on such a wide column? Are there are other
alternatives, possibly different data type to use?|||I'm afraid that some of the urls possibly could get over that size after
encoding (the URL column is nvarchar).
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:O0MdobtnHHA.1476@.TK2MSFTNGP03.phx.gbl...
> If you need to enforce uniqueness, or you are frequently searching the
> table
> by the URL column, then an index is appropriate. Bear in mind that an
> index
> key can be no more than 900 bytes.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Kevin" <none@.none.com> wrote in message
> news:4671898C-0CAC-4396-85F1-BA45E527D8E5@.microsoft.com...
> We have a column that stores URL (varchar), which ultimately are unique
> within the table (though an identity is the actual primary key). Is it
> good
> practice to put an index on such a wide column? Are there are other
> alternatives, possibly different data type to use?
>|||"Kevin" <none@.none.com> wrote in message
news:0DCDCCBA-8397-46D2-A860-8432E352277B@.microsoft.com...
> I'm afraid that some of the urls possibly could get over that size after
> encoding (the URL column is nvarchar).
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:O0MdobtnHHA.1476@.TK2MSFTNGP03.phx.gbl...
>|||Kevin,
if you badly want this column be indexed (of those values that do not excede
the 900 bites limit), you can do some more work:
create an indexed view that have a pk column and the URL column, with the
URL records no longer than 900 bites, index on the URL column;
create an indexed view that have a pk column and the URL column, with the
URL records longer than 900 bites, index on the pk column;
chances are that you may have a handful of records in the second view so
that you do not care to index scan it. And you have index seek on the
shorter URL values. Then join the views with your base table.
HTH
Quentin
"Kevin" <none@.none.com> wrote in message
news:0DCDCCBA-8397-46D2-A860-8432E352277B@.microsoft.com...
> I'm afraid that some of the urls possibly could get over that size after
> encoding (the URL column is nvarchar).
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:O0MdobtnHHA.1476@.TK2MSFTNGP03.phx.gbl...
>

Easy Question

I've read some post that highly recommend not putting a clustered pk on identity columns. Rather use a non-clustered pk for identity pk columns. Does anyone have opinions about this? I have several reference tables that have the following structure. Since joins are made on the pk column I would guess that it would be beneficial to make the pk the clustered index, then again maybe it's better to make one of my unique indexes clustered. Any thoughts?

CREATE TABLE [company] (
[company_id] [int] IDENTITY (1, 1) NOT NULL ,
[co_abbrev] [char(10)] NOT NULL ,
[co_name] [varchar(50)] NOT NULL ,
CONSTRAINT [pk_company] PRIMARY KEY CLUSTERED
(
[company_id]
) WITH FILLFACTOR = 90
)

CREATE UNIQUE INDEX [ux_company_01] ON [dbo].[company]([co_name]) WITH FILLFACTOR = 90

CREATE UNIQUE INDEX [ux_company_02] ON [dbo].[company]([co_abbrev]) WITH FILLFACTOR = 90If table includes field datetime and you are going to select data from this table by date - create clustered index on it.

BOL:Consider using a clustered index for:

-Columns that contain a large number of distinct values.
-Queries that return a range of values using operators such as BETWEEN, >, >=, <, and <=.
-Columns that are accessed sequentially.
-Queries that return large result sets, etc.sql