Affecting SELECT rows to a table variable

Hi,

I would like to know how to add SELECT row to a table variable. It's not for my SELECT syntax(code following is just an ugly example) that I want help it's for the use of table variable.
Your help will greatly appreciate!!!

ex :
DECLARE @.MyTestVar table (
idTest int NOT NULL,
anotherColumn int NOT NULL)

SET @.MyTestVar = (SELECT idTest, anotherColumn FROM tTest)-- This cause an error :
-- Must declare the variable '@.MyTestVar'. ? What?

Use an Insert statement and treat the table variable as the table:

DECLARE @.MyTestVar table (
idTest int NOT NULL,
anotherColumn int NOT NULL)

Insert @.MyTestVar

SELECT idTest, anotherColumn FROM tTest

There is more information in the Books On-line:

Within its scope, a table variable may be used like a regular table. It may be applied anywhere a table or table expression is used in SELECT, INSERT, UPDATE, and DELETE statements. However, table may not be used in the following statements:

INSERT INTO table_variable EXEC stored_procedure

SELECT select_list INTO table_variable statements.

So, you cannot do a Select..Into or use the results of a stored procedure to populate the table variable.

|||

Thanks for the fast answer!

Affecting performance?

I have a table with 23 columns. 8 columns are not always filled by users and so valued to default. Would this affect the overall performance of querying this table? Should I separate those 8 columns and link with a one-to-one relation?

Thanks

Any idea?|||From a database design point of view I would seperate them.

Affecting performance?

I have a table with 23 columns. 8 columns are not always filled by users and so valued to default. Would this affect the overall performance of querying this table? Should I separate those 8 columns and link with a one-to-one relation?

Thanks

Any idea?|||From a database design point of view I would seperate them.

Affect of FillFactor on Index

I am trying to understand how the fillfactor might be affecting the clusered
index.
The primary key (nonclustered) on the table is a composite index of two (int
data type) fields, where one field is an identity seed incrementing by one,
and the other field being more constant.
The non-unique clustered index (that seems to fragment quite often) is a
composite index of four columns:
lname varchar(35)
fname varchar(35)
mname varchar(35)
rptype char(12)
This index has a fillfactor of zero.
The row size on the table is about 900 bytes.
The number of reads versus writes is about 50:1 under normal activity, but
sometimes (once every two weeks or so) this table is loaded with
approximately 200-500 new records at one time.
With a row size of 900 bytes, I am getting about 8 rows a page.
I am trying to wrap by brain around what affect updates and inserts have.
Please help explain.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1cbrichards via SQLMonster.com wrote:
> I am trying to understand how the fillfactor might be affecting the clusered
> index.
> The primary key (nonclustered) on the table is a composite index of two (int
> data type) fields, where one field is an identity seed incrementing by one,
> and the other field being more constant.
> The non-unique clustered index (that seems to fragment quite often) is a
> composite index of four columns:
> lname varchar(35)
> fname varchar(35)
> mname varchar(35)
> rptype char(12)
> This index has a fillfactor of zero.
> The row size on the table is about 900 bytes.
> The number of reads versus writes is about 50:1 under normal activity, but
> sometimes (once every two weeks or so) this table is loaded with
> approximately 200-500 new records at one time.
> With a row size of 900 bytes, I am getting about 8 rows a page.
> I am trying to wrap by brain around what affect updates and inserts have.
> Please help explain.
>
With a fill rate of 100%, clustered on lname (assuming this is "last
name"), you're going to see page splits (i.e. fragmentation) any time
you insert a new last name between two existing ones. Example:
LNAME: Jenkins
LNAME: Jones
These are stored on a data page, that page is 100% full. You insert a
new record:
LNAME: Johnson
Due to the clustered index, this new record belongs between the two
existing ones. However, since the data page containing those records is
full, it has to be split to make room for the new record.
Does that help?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks Tracy...that makes sense.
When it comes to updates, at 900 bytes per row (and let's say 8 rows per page)
, that would leave approximately 800 bytes free per page (before any page
splits factor in) for updates, correct?
Tracy McKibben wrote:
>> I am trying to understand how the fillfactor might be affecting the clusered
>> index.
>[quoted text clipped - 20 lines]
>> I am trying to wrap by brain around what affect updates and inserts have.
>> Please help explain.
>With a fill rate of 100%, clustered on lname (assuming this is "last
>name"), you're going to see page splits (i.e. fragmentation) any time
>you insert a new last name between two existing ones. Example:
>LNAME: Jenkins
>LNAME: Jones
>These are stored on a data page, that page is 100% full. You insert a
>new record:
>LNAME: Johnson
>Due to the clustered index, this new record belongs between the two
>existing ones. However, since the data page containing those records is
>full, it has to be split to make room for the new record.
>Does that help?
>
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1|||cbrichards via SQLMonster.com wrote:
> Thanks Tracy...that makes sense.
> When it comes to updates, at 900 bytes per row (and let's say 8 rows per page)
> , that would leave approximately 800 bytes free per page (before any page
> splits factor in) for updates, correct?
Sounds right, I think...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||So, if I have 800 bytes free per page (based upon 900 and some bytes per row),
reducing the fillfactor to say, 80%, would not be that beneficial in reducing
page splits due to updates, correct?
Tracy McKibben wrote:
>> Thanks Tracy...that makes sense.
>> When it comes to updates, at 900 bytes per row (and let's say 8 rows per page)
>> , that would leave approximately 800 bytes free per page (before any page
>> splits factor in) for updates, correct?
>Sounds right, I think...
>
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1|||cbrichards via SQLMonster.com wrote:
> So, if I have 800 bytes free per page (based upon 900 and some bytes per row),
> reducing the fillfactor to say, 80%, would not be that beneficial in reducing
> page splits due to updates, correct?
>
Probably not. You'll reduce the frequency of the page splits, but you
will still eventually encounter them. Your options are to monitor
fragmentation, reindexing when necessary, or consider clustering on
different keys that aren't so prone to splitting.
I have a script that you might find useful for handling the
fragmentation. I'm currently rebuilding my web site, and don't have the
script online, but you can pull it from Google's cache by searching for
"www.realsqlguy.com/twiki/bin/view/RealSQLGuy/DefragIndexesAsNeeded".
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Affect of FillFactor on Index

I am trying to understand how the fillfactor might be affecting the clusered
index.
The primary key (nonclustered) on the table is a composite index of two (int
data type) fields, where one field is an identity seed incrementing by one,
and the other field being more constant.
The non-unique clustered index (that seems to fragment quite often) is a
composite index of four columns:
lname varchar(35)
fname varchar(35)
mname varchar(35)
rptype char(12)
This index has a fillfactor of zero.
The row size on the table is about 900 bytes.
The number of reads versus writes is about 50:1 under normal activity, but
sometimes (once every two weeks or so) this table is loaded with
approximately 200-500 new records at one time.
With a row size of 900 bytes, I am getting about 8 rows a page.
I am trying to wrap by brain around what affect updates and inserts have.
Please help explain.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1cbrichards via droptable.com wrote:
> I am trying to understand how the fillfactor might be affecting the cluser
ed
> index.
> The primary key (nonclustered) on the table is a composite index of two (i
nt
> data type) fields, where one field is an identity seed incrementing by one
,
> and the other field being more constant.
> The non-unique clustered index (that seems to fragment quite often) is a
> composite index of four columns:
> lname varchar(35)
> fname varchar(35)
> mname varchar(35)
> rptype char(12)
> This index has a fillfactor of zero.
> The row size on the table is about 900 bytes.
> The number of reads versus writes is about 50:1 under normal activity, but
> sometimes (once every two weeks or so) this table is loaded with
> approximately 200-500 new records at one time.
> With a row size of 900 bytes, I am getting about 8 rows a page.
> I am trying to wrap by brain around what affect updates and inserts have.
> Please help explain.
>
With a fill rate of 100%, clustered on lname (assuming this is "last
name"), you're going to see page splits (i.e. fragmentation) any time
you insert a new last name between two existing ones. Example:
LNAME: Jenkins
LNAME: Jones
These are stored on a data page, that page is 100% full. You insert a
new record:
LNAME: Johnson
Due to the clustered index, this new record belongs between the two
existing ones. However, since the data page containing those records is
full, it has to be split to make room for the new record.
Does that help?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks Tracy...that makes sense.
When it comes to updates, at 900 bytes per row (and let's say 8 rows per pag
e)
, that would leave approximately 800 bytes free per page (before any page
splits factor in) for updates, correct?
Tracy McKibben wrote:
>[quoted text clipped - 20 lines]
>With a fill rate of 100%, clustered on lname (assuming this is "last
>name"), you're going to see page splits (i.e. fragmentation) any time
>you insert a new last name between two existing ones. Example:
>LNAME: Jenkins
>LNAME: Jones
>These are stored on a data page, that page is 100% full. You insert a
>new record:
>LNAME: Johnson
>Due to the clustered index, this new record belongs between the two
>existing ones. However, since the data page containing those records is
>full, it has to be split to make room for the new record.
>Does that help?
>
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1|||cbrichards via droptable.com wrote:
> Thanks Tracy...that makes sense.
> When it comes to updates, at 900 bytes per row (and let's say 8 rows per p
age)
> , that would leave approximately 800 bytes free per page (before any page
> splits factor in) for updates, correct?
Sounds right, I think...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||So, if I have 800 bytes free per page (based upon 900 and some bytes per row
),
reducing the fillfactor to say, 80%, would not be that beneficial in reducin
g
page splits due to updates, correct?
Tracy McKibben wrote:
>Sounds right, I think...
>
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1|||cbrichards via droptable.com wrote:
> So, if I have 800 bytes free per page (based upon 900 and some bytes per r
ow),
> reducing the fillfactor to say, 80%, would not be that beneficial in reduc
ing
> page splits due to updates, correct?
>
Probably not. You'll reduce the frequency of the page splits, but you
will still eventually encounter them. Your options are to monitor
fragmentation, reindexing when necessary, or consider clustering on
different keys that aren't so prone to splitting.
I have a script that you might find useful for handling the
fragmentation. I'm currently rebuilding my web site, and don't have the
script online, but you can pull it from Google's cache by searching for
"www.realsqlguy.com/twiki/bin/view/RealSQLGuy/DefragIndexesAsNeeded".
Tracy McKibben
MCDBA
http://www.realsqlguy.com