columns in indexes

How are these columns stored? If I am learning correctly, the added non-key columns in the index are not part of the index limit? If I have a large amount of data that I cannot fit onto an index page, what's being done. Just a pointer to the data that's isolated on it's own page? I guess this would be less overheard than performing an index scan with a pointer to the non-key column (I don't know, I'm asking) and no pointer would be better insert performane.

I looked in the BOL and couldn't find how this stuff is stored. Can you help - does my question even make sense?

Just wanted to add what I found.

According to MS documents are a form of Oracle's Index Organized Tables. Does this mean then that the entire table is an index?

Can you help me understand IOTs?

From http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/sqlorcle.mspx:

Table and Index Storage Parameters

With Microsoft SQL Server, using RAID usually simplifies the placement of database objects. A SQL Server clustered index is integrated into the structure of the table, like an Oracle index-organized table.

|||Does this mean then that the entire table is an index?

Yes. When you create a clustered index on a table, all the data in the table is placed in the data pages of the index (at the leaf level). You can read more about this in SQL Server 2005 Books Online in the topic Clustered Index Structures.

You can also create nonclustered indexes. These indexes contain the index key values and row locators that point to the storage location of the table data. See the topic Nonclustered Index Structures in Books Online.

Regards,|||I believe you are referring to composite index key. Here is from BOL

"Up to 16 columns can be combined into a single composite index key. All the columns in a composite index key must be in the same table or view. The maximum allowable size of the combined index values is 900 bytes. For more information about variable type columns in composite indexes, see the Remarks section.

Columns that are of the large object (LOB) data types ntext, text, varchar(max), nvarchar(max), varbinary(max), xml, or image cannot be specified as key columns for an index"

So your data will fit into index page.

thanks

Column limit in Sql Server 2000 replication?

I am being told that the colid in syscolumns may not exceed 255 if the table is replicated. Is that true? Where in BOL or elsewhere can I read-up on this? This is a shocking development!!!

Ok guys, I found my answer in BOL!

"Be aware of maximum column and row sizes. A table used in snapshot replication or transactional replication can have a maximum of 255 columns and a maximum row size of 8,000 bytes."

It appears that Replication uses syscolumns.colid as the number of columns in a table, even if, as in my case, there are only 152 columns. This is the kind of arbitrary "gotcha" that makes a person sick. Our developers ignore these kinds of limitations and expect someone else to correct the problem.

|||As a follow-on, it is a good idea to have a job which checks the max(colid) on syscolumns if you add columns to your replicated tables.

Column Limit = 1024?

I am adding columns to one of my tables since I noticed the column
limit to SQL Server tables is now 1024. However, I am using the
Enterprise Manager to add the columns, and it stopped allowing me to
enter more columns once I got up to about 292 columns. Is the
Enterprise Manager not capable of handling 1024 columns? Is there
another method I should use to add columns? Or is the column limit
not really 1024?
Thanks in advance for your help,
Dominic Isaia
disaia@.spacecraft.comyou won't get this problem if you use Query Analyzer.
"Dominic Isaia" <disaia@.spacecraft.com> wrote in message
news:d8ecad62.0311201503.567f217a@.posting.google.com...
> I am adding columns to one of my tables since I noticed the column
> limit to SQL Server tables is now 1024. However, I am using the
> Enterprise Manager to add the columns, and it stopped allowing me to
> enter more columns once I got up to about 292 columns. Is the
> Enterprise Manager not capable of handling 1024 columns? Is there
> another method I should use to add columns? Or is the column limit
> not really 1024?
>
> Thanks in advance for your help,
> Dominic Isaia
> disaia@.spacecraft.com|||Thanks, it works fine by using 'alter table' in the Query Analyzer.
However, it did generate an error message which might be why the
Enterprise Manager stopped allowing me to add columns. Even though
the column limit is 1024, there is a limit on the bytes per row, which
is 8060. I am using varchar as my type giving everything a 50
character length, knowing that if the data is only 2 characters it
will only take up 2 bytes of storage space and discard the 48
remaining. But I think it has to allow for 50 characters just in
case, which is why I went over that 8060 limit. Problem solved by
lowering the character length of most of my columns.
Dominic Isaia
disaia@.spacecraft.com

Column limit

This could belong in the CRM community but I thought I'd come here first.
I am trying to add for custom fields to the CRM Schema manager. It creates
the database portion. As I understand it, SQL can have 1024 columns per base
table. I just hit 256 and stopped cold. I find it odd that it stopped at the
8 bit mark. I have been using Picklists, Memo fields and boolean fields for
the most part.
Any insight is greatly appreciated.
~Graham
Graham,
The maximum row size for a data page is 8060 bytes. Perhaps this is the
limit you're bumping up against.
HTH
Jerry
"GDaxon" <GDaxon@.discussions.microsoft.com> wrote in message
news:CCC35A02-7936-4538-AE89-338C4E47ED3A@.microsoft.com...
> This could belong in the CRM community but I thought I'd come here first.
> I am trying to add for custom fields to the CRM Schema manager. It creates
> the database portion. As I understand it, SQL can have 1024 columns per
> base
> table. I just hit 256 and stopped cold. I find it odd that it stopped at
> the
> 8 bit mark. I have been using Picklists, Memo fields and boolean fields
> for
> the most part.
> Any insight is greatly appreciated.
> ~Graham
|||In addition to Jerry's post:
"stopped cold" doesn't give us much to go on. Try adding the column (ALTER TABLE ... ADD ...) or
creating the table with that many column (whichever applies to you) using Query Analyzer. If you get
an error, the problem is at the SQL Server side, and you can post the error message. If not, the
problem is outside SQL Server. Also, you can backup and restore your CRM database on another
machine, or another database name if you don't want to mess about with your production database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"GDaxon" <GDaxon@.discussions.microsoft.com> wrote in message
news:CCC35A02-7936-4538-AE89-338C4E47ED3A@.microsoft.com...
> This could belong in the CRM community but I thought I'd come here first.
> I am trying to add for custom fields to the CRM Schema manager. It creates
> the database portion. As I understand it, SQL can have 1024 columns per base
> table. I just hit 256 and stopped cold. I find it odd that it stopped at the
> 8 bit mark. I have been using Picklists, Memo fields and boolean fields for
> the most part.
> Any insight is greatly appreciated.
> ~Graham
|||Its not the 8000 limit.. I learned that one the hard way already. The error
in the event viewer has no error code. Basically the errors say " didnt
create the column" and "can't create the column" no explaination or clues as
to why.
Im starting to think its more about how CRM controls SQL and that it's a CRM
limitation.
"Tibor Karaszi" wrote:

> In addition to Jerry's post:
> "stopped cold" doesn't give us much to go on. Try adding the column (ALTER TABLE ... ADD ...) or
> creating the table with that many column (whichever applies to you) using Query Analyzer. If you get
> an error, the problem is at the SQL Server side, and you can post the error message. If not, the
> problem is outside SQL Server. Also, you can backup and restore your CRM database on another
> machine, or another database name if you don't want to mess about with your production database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "GDaxon" <GDaxon@.discussions.microsoft.com> wrote in message
> news:CCC35A02-7936-4538-AE89-338C4E47ED3A@.microsoft.com...
>
|||> Im starting to think its more about how CRM controls SQL and that it's a CRM
> limitation.
As I said, try using Query Analyzer to do the change/create and see if you get an error from SQL
Server. Then you know. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"GDaxon" <GDaxon@.discussions.microsoft.com> wrote in message
news:4182B61F-3F37-43D7-BB2E-A575A3C154E0@.microsoft.com...[vbcol=seagreen]
> Its not the 8000 limit.. I learned that one the hard way already. The error
> in the event viewer has no error code. Basically the errors say " didnt
> create the column" and "can't create the column" no explaination or clues as
> to why.
> Im starting to think its more about how CRM controls SQL and that it's a CRM
> limitation.
> "Tibor Karaszi" wrote:

Column limit

This could belong in the CRM community but I thought I'd come here first.
I am trying to add for custom fields to the CRM Schema manager. It creates
the database portion. As I understand it, SQL can have 1024 columns per base
table. I just hit 256 and stopped cold. I find it odd that it stopped at the
8 bit mark. I have been using Picklists, Memo fields and boolean fields for
the most part.
Any insight is greatly appreciated.
~GrahamGraham,
The maximum row size for a data page is 8060 bytes. Perhaps this is the
limit you're bumping up against.
HTH
Jerry
"GDaxon" <GDaxon@.discussions.microsoft.com> wrote in message
news:CCC35A02-7936-4538-AE89-338C4E47ED3A@.microsoft.com...
> This could belong in the CRM community but I thought I'd come here first.
> I am trying to add for custom fields to the CRM Schema manager. It creates
> the database portion. As I understand it, SQL can have 1024 columns per
> base
> table. I just hit 256 and stopped cold. I find it odd that it stopped at
> the
> 8 bit mark. I have been using Picklists, Memo fields and boolean fields
> for
> the most part.
> Any insight is greatly appreciated.
> ~Graham|||In addition to Jerry's post:
"stopped cold" doesn't give us much to go on. Try adding the column (ALTER T
ABLE ... ADD ...) or
creating the table with that many column (whichever applies to you) using Qu
ery Analyzer. If you get
an error, the problem is at the SQL Server side, and you can post the error
message. If not, the
problem is outside SQL Server. Also, you can backup and restore your CRM dat
abase on another
machine, or another database name if you don't want to mess about with your
production database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"GDaxon" <GDaxon@.discussions.microsoft.com> wrote in message
news:CCC35A02-7936-4538-AE89-338C4E47ED3A@.microsoft.com...
> This could belong in the CRM community but I thought I'd come here first.
> I am trying to add for custom fields to the CRM Schema manager. It creates
> the database portion. As I understand it, SQL can have 1024 columns per ba
se
> table. I just hit 256 and stopped cold. I find it odd that it stopped at t
he
> 8 bit mark. I have been using Picklists, Memo fields and boolean fields fo
r
> the most part.
> Any insight is greatly appreciated.
> ~Graham|||Its not the 8000 limit.. I learned that one the hard way already. The error
in the event viewer has no error code. Basically the errors say " didnt
create the column" and "can't create the column" no explaination or clues as
to why.
Im starting to think its more about how CRM controls SQL and that it's a CRM
limitation.
"Tibor Karaszi" wrote:

> In addition to Jerry's post:
> "stopped cold" doesn't give us much to go on. Try adding the column (ALTER
TABLE ... ADD ...) or
> creating the table with that many column (whichever applies to you) using
Query Analyzer. If you get
> an error, the problem is at the SQL Server side, and you can post the erro
r message. If not, the
> problem is outside SQL Server. Also, you can backup and restore your CRM d
atabase on another
> machine, or another database name if you don't want to mess about with you
r production database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "GDaxon" <GDaxon@.discussions.microsoft.com> wrote in message
> news:CCC35A02-7936-4538-AE89-338C4E47ED3A@.microsoft.com...
>|||> Im starting to think its more about how CRM controls SQL and that it's a CRMn">
> limitation.
As I said, try using Query Analyzer to do the change/create and see if you g
et an error from SQL
Server. Then you know. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"GDaxon" <GDaxon@.discussions.microsoft.com> wrote in message
news:4182B61F-3F37-43D7-BB2E-A575A3C154E0@.microsoft.com...[vbcol=seagreen]
> Its not the 8000 limit.. I learned that one the hard way already. The erro
r
> in the event viewer has no error code. Basically the errors say " didnt
> create the column" and "can't create the column" no explaination or clues
as
> to why.
> Im starting to think its more about how CRM controls SQL and that it's a C
RM
> limitation.
> "Tibor Karaszi" wrote:
>

Column limit

Is there a limit on the number of columns you can create in SQL Server?
I keep hearing 250 but I can't find anything definate.
KenBOL: Maximum Capacity Specifications
Columns per base table: 1024
KenH wrote:
> Is there a limit on the number of columns you can create in SQL Server?
> I keep hearing 250 but I can't find anything definate.
> Ken
>|||On 9 Sep 2005 13:10:51 -0700, KenH wrote:

>Is there a limit on the number of columns you can create in SQL Server?
>I keep hearing 250 but I can't find anything definate.
>Ken
Hi Ken,
Did you check Books Online? In the index, "Maximum number of columns per
table" points to the description of CREATE TABLE, where (under the
heading "Remarks") it says:
(...)
And the index entry "maximum capacity specifications" takes you to a
table that lists the same number, plus much more information.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks guys!
I'll start using the books online.
Ken

Column limit

This could belong in the CRM community but I thought I'd come here first.
I am trying to add for custom fields to the CRM Schema manager. It creates
the database portion. As I understand it, SQL can have 1024 columns per base
table. I just hit 256 and stopped cold. I find it odd that it stopped at the
8 bit mark. I have been using Picklists, Memo fields and boolean fields for
the most part.
Any insight is greatly appreciated.
~GrahamGraham,
The maximum row size for a data page is 8060 bytes. Perhaps this is the
limit you're bumping up against.
HTH
Jerry
"GDaxon" <GDaxon@.discussions.microsoft.com> wrote in message
news:CCC35A02-7936-4538-AE89-338C4E47ED3A@.microsoft.com...
> This could belong in the CRM community but I thought I'd come here first.
> I am trying to add for custom fields to the CRM Schema manager. It creates
> the database portion. As I understand it, SQL can have 1024 columns per
> base
> table. I just hit 256 and stopped cold. I find it odd that it stopped at
> the
> 8 bit mark. I have been using Picklists, Memo fields and boolean fields
> for
> the most part.
> Any insight is greatly appreciated.
> ~Graham|||In addition to Jerry's post:
"stopped cold" doesn't give us much to go on. Try adding the column (ALTER TABLE ... ADD ...) or
creating the table with that many column (whichever applies to you) using Query Analyzer. If you get
an error, the problem is at the SQL Server side, and you can post the error message. If not, the
problem is outside SQL Server. Also, you can backup and restore your CRM database on another
machine, or another database name if you don't want to mess about with your production database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"GDaxon" <GDaxon@.discussions.microsoft.com> wrote in message
news:CCC35A02-7936-4538-AE89-338C4E47ED3A@.microsoft.com...
> This could belong in the CRM community but I thought I'd come here first.
> I am trying to add for custom fields to the CRM Schema manager. It creates
> the database portion. As I understand it, SQL can have 1024 columns per base
> table. I just hit 256 and stopped cold. I find it odd that it stopped at the
> 8 bit mark. I have been using Picklists, Memo fields and boolean fields for
> the most part.
> Any insight is greatly appreciated.
> ~Graham|||Its not the 8000 limit.. I learned that one the hard way already. The error
in the event viewer has no error code. Basically the errors say " didnt
create the column" and "can't create the column" no explaination or clues as
to why.
Im starting to think its more about how CRM controls SQL and that it's a CRM
limitation.
"Tibor Karaszi" wrote:
> In addition to Jerry's post:
> "stopped cold" doesn't give us much to go on. Try adding the column (ALTER TABLE ... ADD ...) or
> creating the table with that many column (whichever applies to you) using Query Analyzer. If you get
> an error, the problem is at the SQL Server side, and you can post the error message. If not, the
> problem is outside SQL Server. Also, you can backup and restore your CRM database on another
> machine, or another database name if you don't want to mess about with your production database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "GDaxon" <GDaxon@.discussions.microsoft.com> wrote in message
> news:CCC35A02-7936-4538-AE89-338C4E47ED3A@.microsoft.com...
> > This could belong in the CRM community but I thought I'd come here first.
> >
> > I am trying to add for custom fields to the CRM Schema manager. It creates
> > the database portion. As I understand it, SQL can have 1024 columns per base
> > table. I just hit 256 and stopped cold. I find it odd that it stopped at the
> > 8 bit mark. I have been using Picklists, Memo fields and boolean fields for
> > the most part.
> >
> > Any insight is greatly appreciated.
> >
> > ~Graham
>|||> Im starting to think its more about how CRM controls SQL and that it's a CRM
> limitation.
As I said, try using Query Analyzer to do the change/create and see if you get an error from SQL
Server. Then you know. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"GDaxon" <GDaxon@.discussions.microsoft.com> wrote in message
news:4182B61F-3F37-43D7-BB2E-A575A3C154E0@.microsoft.com...
> Its not the 8000 limit.. I learned that one the hard way already. The error
> in the event viewer has no error code. Basically the errors say " didnt
> create the column" and "can't create the column" no explaination or clues as
> to why.
> Im starting to think its more about how CRM controls SQL and that it's a CRM
> limitation.
> "Tibor Karaszi" wrote:
>> In addition to Jerry's post:
>> "stopped cold" doesn't give us much to go on. Try adding the column (ALTER TABLE ... ADD ...) or
>> creating the table with that many column (whichever applies to you) using Query Analyzer. If you
>> get
>> an error, the problem is at the SQL Server side, and you can post the error message. If not, the
>> problem is outside SQL Server. Also, you can backup and restore your CRM database on another
>> machine, or another database name if you don't want to mess about with your production database.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "GDaxon" <GDaxon@.discussions.microsoft.com> wrote in message
>> news:CCC35A02-7936-4538-AE89-338C4E47ED3A@.microsoft.com...
>> > This could belong in the CRM community but I thought I'd come here first.
>> >
>> > I am trying to add for custom fields to the CRM Schema manager. It creates
>> > the database portion. As I understand it, SQL can have 1024 columns per base
>> > table. I just hit 256 and stopped cold. I find it odd that it stopped at the
>> > 8 bit mark. I have been using Picklists, Memo fields and boolean fields for
>> > the most part.
>> >
>> > Any insight is greatly appreciated.
>> >
>> > ~Graham
>>

Column constraint

Is it possible to limit a column's value to 0 or 1 and to allow zero or one
row to contain a value
of 1 for that column?
TIA, LarsNot 100% sure of your question but I think you want to look at CHECK
constraints and Unique Indexes in BooksOnLine. You can also enforce data
with triggers.
Andrew J. Kelly SQL MVP
"larzeb" <larzeb@.community.nospam> wrote in message
news:po5f811s7pvik05doqb0roide0g2uvla32@.
4ax.com...
> Is it possible to limit a column's value to 0 or 1 and to allow zero or
> one row to contain a value
> of 1 for that column?
> TIA, Lars|||> Is it possible to limit a column's value to 0 or 1
Yes, using either a Bit field or a Check constraint on a TinyInt, SmallInt,
or
Integer column.

> and to allow zero or one row to contain a value
> of 1 for that column?
This part is a bit trickier. Based purely on the information given, you coul
d
solve this in a couple of ways:
1. A trigger that raises an error when on an Insert or Update, a value alrea
dy
exists in the table with a value of 1.
2. Create a View that selects all rows that have a value of 1 and put a uniq
ue
index on that view.
There are other ways to solve this problem if you give us more information a
bout
the schema and problem you are trying to solve. For example, in building a
system for an insurance company, each insurance quote could be associated wi
th
multiple insurance brokers. However, each quote also had one and only one
"primary" broker. Thus, we solved this problem by putting a PrimaryBrokerId
column on the quote itself as well as had a table of "associated" brokers. T
his
made it easy to ensure that each quote had a primary broker and that there w
as
one and only one.
Granted, this sort of problem is much easier to solve in an object model tha
n it
is with database schema but with more infomration about the actual problem,
there might be a solution in the database design.
Thomas|||CREATE TABLE Foobar
( ..
silly_col INTEGER DEFAULT 0 NOT NULL
CONSTRAINT silly_col_range
CHECK (silly_col IN (0,1)),
CONSTRAINT silly_col_single flag
CHECK (1 - (SELECT SUM(silly_col) FROMbar)),
.);
But in SQL Server, you cannot write that second constraint like you can
in other products with better SQL-92 conformance. You would never use
bit data types because they are proprietary.
if you want to flag one row for some purpose, I would recomment that
you use a unique numeric column and select the minimum value:
CREATE TABLE Foobar
( ..
silly_col INTEGER DEFAULT 0 NOT NULL UNIQUE
CONSTRAINT silly_col_range
CHECK (silly_col >= 0),
.);
CREATE VIEW NewFoobar
AS
SELECT .. CASE WHEN silly_col
= (SELECT MIN(silly_col) FROM Foobar)
THEN 1 ELSE 0 END AS silly_flag,
FROM Foobar;|||If you just want to set the state (col =1) of exactly one row in a table
then maybe you could use a second table to do that:
CREATE TABLE YourTable (key_col INTEGER PRIMARY KEY, ... /* other cols */)
CREATE TABLE foo (key_col INTEGER NOT NULL REFERENCES YourTable (key_col), x
INTEGER DEFAULT 1 NOT NULL CHECK (x=1) UNIQUE /* Permit only one row */)
David Portas
SQL Server MVP
--