Row size limitations of SQL Server 2005

I've created a database design and I'm trying to figure out if it's workable or not. I have 2 tables in particular that are sparse (20 columns, but only a few have data - about 100 bytes/row) but will grow very large - to the tune of 700 million rows.

My question is whether or not there is a problem with SQL Server 2005 having 1.5+ billion rows of data even though it will likely only take up 100 gigs or so on disk. Anyone have experience in large numbers of rows like this? We're still doing testing as far determining how slow queries will get.

Thanks,

Craig

That's fine. SQL Server can deal with that many rows of data. I've worked with tables with billions of rows before. Don't forget to plan for your index space as well and any necessary index maintenance.

-Sue

|||

Try the link below for the SQL Server Max CAP.

http://msdn2.microsoft.com/en-us/library/ms143432.aspx

Row Level Locking

Hello,
I have been trying to figure out how to user Row Level locking feature in
SQL Server 2005. Here is the scenario.
1.Open a new query window in SQL Server Management sudio.
2.Execute a Begin transaction and an Update statement that updates a row in
a table. Do not commit the transaction yet.
3. Open another query window
4. Execute a Begin transaction and an Update statement that updates a
different row in the same table. Do not commit the transaction yet.
The secod session does not complete execution and waits because the table is
locked. I verified that by checking the locks in the database. I was
expecting just the first row to be locked and not the entire table. Once I
commit the first transaction, the second session completes execution of the
update statement.
How do I make it to lock only the row and not the table. I did try using
ROWLOCK hint as part of te UPDATE statement. It did not help. I had turned
off the Page Level locks for the index in the table.
VMake sure you have an index on the column you use in the WHERE clause. Other
wise, SQL Server has to
look at each row to see whether the row satisfies the WHERE condition (and t
he other connection has
one row with excusive lock).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"V" <V@.discussions.microsoft.com> wrote in message
news:1631B36D-FC9E-4B3B-A1ED-7586261C74B1@.microsoft.com...
> Hello,
> I have been trying to figure out how to user Row Level locking feature in
> SQL Server 2005. Here is the scenario.
> 1.Open a new query window in SQL Server Management sudio.
> 2.Execute a Begin transaction and an Update statement that updates a row
in
> a table. Do not commit the transaction yet.
> 3. Open another query window
> 4. Execute a Begin transaction and an Update statement that updates a
> different row in the same table. Do not commit the transaction yet.
> The secod session does not complete execution and waits because the table
is
> locked. I verified that by checking the locks in the database. I was
> expecting just the first row to be locked and not the entire table. Once I
> commit the first transaction, the second session completes execution of th
e
> update statement.
> How do I make it to lock only the row and not the table. I did try using
> ROWLOCK hint as part of te UPDATE statement. It did not help. I had turned
> off the Page Level locks for the index in the table.
> V
>|||Thanks, That helped. After creating an index on the column used in where
clause, it worked.
"Tibor Karaszi" wrote:

> Make sure you have an index on the column you use in the WHERE clause. Oth
erwise, SQL Server has to
> look at each row to see whether the row satisfies the WHERE condition (and
the other connection has
> one row with excusive lock).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "V" <V@.discussions.microsoft.com> wrote in message
> news:1631B36D-FC9E-4B3B-A1ED-7586261C74B1@.microsoft.com...
>

Rounding...Help!

Hello, all. Tired of trying to figure out SQLs rounding scheme. If I issue
:
select (540/60)/60, I'd like to get .15 however I get 0.
How can I get the .15 result I want?
Thanks
Rozselect (540/60)/60.0
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Roz" <Roz@.discussions.microsoft.com> wrote in message
news:21B68ED2-CA86-43D8-AD1A-F970E0CD37AC@.microsoft.com...
> Hello, all. Tired of trying to figure out SQLs rounding scheme. If I iss
ue:
> select (540/60)/60, I'd like to get .15 however I get 0.
> How can I get the .15 result I want?
> Thanks
> Roz|||To get a decimal result, you have to use at least one decimal operand. When
you use integer operands, you get an integer result.
SELECT ( 540.0 / 60 ) / 60
Use CAST or CONVERT functions if you want to convert an integer datatype to
numeric/decimal datatype with required precision.
Anith|||The problem is that you haven't specified a datatype explicitly so SQL
treats 60 as an integer and you get an integer division. To avoid this use a
NUMERIC datatype for example, or include decimals to imply a NUMERIC:
SELECT (540.0/60.0)/60.0
David Portas
SQL Server MVP
--|||Folks,
Thanks. These all worked beautifully!!!!
Roz
"David Portas" wrote:

> The problem is that you haven't specified a datatype explicitly so SQL
> treats 60 as an integer and you get an integer division. To avoid this use
a
> NUMERIC datatype for example, or include decimals to imply a NUMERIC:
> SELECT (540.0/60.0)/60.0
> --
> David Portas
> SQL Server MVP
> --
>
>|||Try this...
SELECT RTRIM((CONVERT(FLOAT(2),
540)/CONVERT(FLOAT(2),60))/CONVERT(FLOAT(2),60))
Regards
Sivakumar
"Roz" wrote:

> Hello, all. Tired of trying to figure out SQLs rounding scheme. If I iss
ue:
> select (540/60)/60, I'd like to get .15 however I get 0.
> How can I get the .15 result I want?
> Thanks
> Roz

Rounding seconds up to the nearest 15 minutes

I have a field with seconds in it and I need to disply it in hours which I can do by dividing it by 3600, but I am trying to figure out how to round it up to the nearest 15 minutes. I have tried a couple of things with ROUND and CEILING, but am not getting the right numbers back. Any help would be greatly appreciated.

Hi,

You need to divide the seconds by 15 first, round that (up or down as your logic dictates) and then divide by 4 to get hours.

Try this as example:

DECLARE @.secondsint
SET @.seconds = 1632
DECLARE @.hoursdecimal(18,2)
SET @.hours = CEILING(convert(decimal,@.seconds)/15 )
SELECT @.hours, @.hours/4

The result should be 109, 27.25 (27 and a quarter hours).
I included the two results so you can see what is happening.
Change the seconds to 1640 and the figures are 110, 27.50 (27 and half hours)

|||

Okay, you totally lost me. I only get to dabble with mssql every few months so please excuse my ignorance. I am trying to work what you said into my query, but I when I put in the decimal it throws an error.

SELECT dbo.SLPTRANS.ClientID,SUM(dbo.SLPTRANS.TransValue)AS Expr1,SUM(CEILING(dbo.SLPTRANS.TimeSpent / 15) * dbo.SLPTRANS.RateValue)AS BillableFeesFROM dbo.SLPTRANSINNERJOIN dbo.INVOICEON dbo.SLPTRANS.InvoiceID = dbo.INVOICE.RecordIDGROUP BY dbo.SLPTRANS.ClientIDHAVING (dbo.SLPTRANS.ClientID = 405)

|||

Hi,
Apologies for the late reply!

I think the SUM should be:
SUM( (CEILING(db.SLPTRANS.TimeSpent /15)/4 ) * dbo.SLPTRANS.RateValue)

What error message do you get?

Rounding Problem

Hi all,
Is there any function that truncates a decimal numeric without rounding, if
i use cast it rounds the figure but can i don't want that i.e i want it to
be as follows:
23.1238 turns to 23.123 and not 23.124 so it keeps only three decimals as
is
Please advise if there is any function or so
Thanks in advance
Suad
Try
Round(23.1238,3,1)

Rotate table query help

I need to join two tables I guess in a rotate fashion but have not
been able to figure it out. We have a Taxo_Status table that contains
up to 2 entries per species. These records need to be joined
Taxo_Status_LUT table such that one record is turned per species with
a state status description (category_code = "S") and a federal status
description (category_code = "F").
This is the result set I need
Species_Code State_Status_Desc Federal_Status_Desc
HALE Endangered Threatened
KALA null Sensitive
MAMU Endangered null
TAXO_STATUS Table
Species_Code Taxo_Listing_ID
HALE 3
HALE 5
MAMU 3
KALA 4
TAXO_STATUS_LUT Table
Taxo_Listing_ID Category_Code Status_Desc
1 S Sensitive
2 S Threatened
3 S Endangered
4 F Sensitive
5 F Threatened
6 F Endangeredselect ts.species_code,
max(case when category_code='S' then status_desc end) as
Status_Status_Desc,
max(case when category_code='F' then status_desc end) as
Federal_Status_Desc
from taxo_status ts
join taxo_status_lut tsl on ts.taxo_listing_id = tsl.taxo_listing_id
group by ts.species_code
order by ts.species_code
Randy K wrote:
> I need to join two tables I guess in a rotate fashion but have not
> been able to figure it out. We have a Taxo_Status table that contains
> up to 2 entries per species. These records need to be joined
> Taxo_Status_LUT table such that one record is turned per species with
> a state status description (category_code = "S") and a federal status
> description (category_code = "F").
> This is the result set I need
> Species_Code State_Status_Desc Federal_Status_Desc
> HALE Endangered Threatened
> KALA null Sensitive
> MAMU Endangered null
> --
> TAXO_STATUS Table
> Species_Code Taxo_Listing_ID
> HALE 3
> HALE 5
> MAMU 3
> KALA 4
> --
> TAXO_STATUS_LUT Table
> Taxo_Listing_ID Category_Code Status_Desc
> 1 S Sensitive
> 2 S Threatened
> 3 S Endangered
> 4 F Sensitive
> 5 F Threatened
> 6 F Endangered

Rotate table help

I need to join two tables I guess in a rotate fashion but have not
been able to figure it out. We have a Taxo_Status table that contains
up to 2 entries per species. These records need to be joined
Taxo_Status_LUT table such that one record is turned per species with
a state status description (category_code = "S") and a federal status
description (category_code = "F").
This is the sult set I need
Species_Code State_Status_Desc Federal_Status_Desc
HALE Endangered Threatened
KALA null Sensitive
MAMU Endangered null
TAXO_STATUS Table
Species_Code Taxo_Listing_ID
HALE 3
HALE 5
MAMU 3
KALA 4
TAXO_STATUS_LUT Table
Taxo_Listing_ID Category_Code Status_Desc
1 S Sensitive
2 S Threatened
3 S Endangered
4 F Sensitive
5 F Threatened
6 F EndangeredHopefully this query can help you:
select c.species_code, max(c.State_Status_Desc) as 'State_Status_Desc',
max(c.Federal_Status_Desc) as 'Federal_Status_Desc'
from
(select a.species_code,
case when b.Category_Code = 'S' then b.Status_Desc else null end as
'State_Status_Desc',
case when b.Category_Code = 'F' then b.Status_Desc else null end as
'Federal_Status_Desc'
from
#TAXO_STATUS a , #TAXO_STATUS_LUT b
where a.Taxo_Listing_ID = b.Taxo_Listing_ID) c
group by c.species_code
"Randy K" wrote:

> I need to join two tables I guess in a rotate fashion but have not
> been able to figure it out. We have a Taxo_Status table that contains
> up to 2 entries per species. These records need to be joined
> Taxo_Status_LUT table such that one record is turned per species with
> a state status description (category_code = "S") and a federal status
> description (category_code = "F").
> This is the sult set I need
> Species_Code State_Status_Desc Federal_Status_Desc
> HALE Endangered Threatened
> KALA null Sensitive
> MAMU Endangered null
> --
> TAXO_STATUS Table
> Species_Code Taxo_Listing_ID
> HALE 3
> HALE 5
> MAMU 3
> KALA 4
> --
> TAXO_STATUS_LUT Table
> Taxo_Listing_ID Category_Code Status_Desc
> 1 S Sensitive
> 2 S Threatened
> 3 S Endangered
> 4 F Sensitive
> 5 F Threatened
> 6 F Endangered
>|||Thanks to Trey my query has been solved
select ts.species_code,
max(case when category_code='S' then status_desc end) as
Status_Status_Desc,
max(case when category_code='F' then status_desc end) as
Federal_Status_Desc
from taxo_status ts
join taxo_status_lut tsl on ts.taxo_listing_id =
tsl.taxo_listing_id
group by ts.species_code
order by ts.species_code
On Fri, 18 Nov 2005 18:41:35 GMT, wawork@.hotmail.com (Randy K) wrote:

>I need to join two tables I guess in a rotate fashion but have not
>been able to figure it out. We have a Taxo_Status table that contains
>up to 2 entries per species. These records need to be joined
>Taxo_Status_LUT table such that one record is turned per species with
>a state status description (category_code = "S") and a federal status
>description (category_code = "F").
>This is the sult set I need
>Species_Code State_Status_Desc Federal_Status_Desc
> HALE Endangered Threatened
> KALA null Sensitive
> MAMU Endangered null
>--
>TAXO_STATUS Table
>Species_Code Taxo_Listing_ID
>HALE 3
>HALE 5
>MAMU 3
>KALA 4
>--
>TAXO_STATUS_LUT Table
>Taxo_Listing_ID Category_Code Status_Desc
> 1 S Sensitive
> 2 S Threatened
> 3 S Endangered
> 4 F Sensitive
> 5 F Threatened
> 6 F Endangered

Rotate table help

I need to join two tables I guess in a rotate fashion but have not
been able to figure it out. We have a Taxo_Status table that contains
up to 2 entries per species. These records need to be joined
Taxo_Status_LUT table such that one record is turned per species with
a state status description (category_code = "S") and a federal status
description (category_code = "F").
This is the sult set I need
Species_Code State_Status_Desc Federal_Status_Desc
HALE Endangered Threatened
KALA null Sensitive
MAMU Endangered null
TAXO_STATUS Table
Species_Code Taxo_Listing_ID
HALE 3
HALE 5
MAMU 3
KALA 4
TAXO_STATUS_LUT Table
Taxo_Listing_ID Category_Code Status_Desc
1 S Sensitive
2 S Threatened
3 S Endangered
4 F Sensitive
5 F Threatened
6 F Endangered
Hopefully this query can help you:
select c.species_code, max(c.State_Status_Desc) as 'State_Status_Desc',
max(c.Federal_Status_Desc) as 'Federal_Status_Desc'
from
(select a.species_code,
case when b.Category_Code = 'S' then b.Status_Desc else null end as
'State_Status_Desc',
case when b.Category_Code = 'F' then b.Status_Desc else null end as
'Federal_Status_Desc'
from
#TAXO_STATUS a , #TAXO_STATUS_LUT b
where a.Taxo_Listing_ID = b.Taxo_Listing_ID) c
group by c.species_code
"Randy K" wrote:

> I need to join two tables I guess in a rotate fashion but have not
> been able to figure it out. We have a Taxo_Status table that contains
> up to 2 entries per species. These records need to be joined
> Taxo_Status_LUT table such that one record is turned per species with
> a state status description (category_code = "S") and a federal status
> description (category_code = "F").
> This is the sult set I need
> Species_Code State_Status_Desc Federal_Status_Desc
> HALE Endangered Threatened
> KALA null Sensitive
> MAMU Endangered null
> --
> TAXO_STATUS Table
> Species_Code Taxo_Listing_ID
> HALE 3
> HALE 5
> MAMU 3
> KALA 4
> --
> TAXO_STATUS_LUT Table
> Taxo_Listing_ID Category_Code Status_Desc
> 1 S Sensitive
> 2 S Threatened
> 3 S Endangered
> 4 F Sensitive
> 5 F Threatened
> 6 F Endangered
>
|||Thanks to Trey my query has been solved
select ts.species_code,
max(case when category_code='S' then status_desc end) as
Status_Status_Desc,
max(case when category_code='F' then status_desc end) as
Federal_Status_Desc
from taxo_status ts
join taxo_status_lut tsl on ts.taxo_listing_id =
tsl.taxo_listing_id
group by ts.species_code
order by ts.species_code
On Fri, 18 Nov 2005 18:41:35 GMT, wawork@.hotmail.com (Randy K) wrote:

>I need to join two tables I guess in a rotate fashion but have not
>been able to figure it out. We have a Taxo_Status table that contains
>up to 2 entries per species. These records need to be joined
>Taxo_Status_LUT table such that one record is turned per species with
>a state status description (category_code = "S") and a federal status
>description (category_code = "F").
>This is the sult set I need
>Species_Code State_Status_Desc Federal_Status_Desc
> HALE Endangered Threatened
> KALA null Sensitive
> MAMU Endangered null
>--
>TAXO_STATUS Table
>Species_Code Taxo_Listing_ID
>HALE 3
>HALE 5
>MAMU 3
>KALA 4
>--
>TAXO_STATUS_LUT Table
>Taxo_Listing_ID Category_Code Status_Desc
> 1 S Sensitive
> 2 S Threatened
> 3 S Endangered
> 4 F Sensitive
> 5 F Threatened
> 6 F Endangered

Rotate table help

I need to join two tables I guess in a rotate fashion but have not
been able to figure it out. We have a Taxo_Status table that contains
up to 2 entries per species. These records need to be joined
Taxo_Status_LUT table such that one record is turned per species with
a state status description (category_code = "S") and a federal status
description (category_code = "F").
This is the sult set I need
Species_Code State_Status_Desc Federal_Status_Desc
HALE Endangered Threatened
KALA null Sensitive
MAMU Endangered null
--
TAXO_STATUS Table
Species_Code Taxo_Listing_ID
HALE 3
HALE 5
MAMU 3
KALA 4
--
TAXO_STATUS_LUT Table
Taxo_Listing_ID Category_Code Status_Desc
1 S Sensitive
2 S Threatened
3 S Endangered
4 F Sensitive
5 F Threatened
6 F EndangeredHopefully this query can help you:
select c.species_code, max(c.State_Status_Desc) as 'State_Status_Desc',
max(c.Federal_Status_Desc) as 'Federal_Status_Desc'
from
(select a.species_code,
case when b.Category_Code = 'S' then b.Status_Desc else null end as
'State_Status_Desc',
case when b.Category_Code = 'F' then b.Status_Desc else null end as
'Federal_Status_Desc'
from
#TAXO_STATUS a , #TAXO_STATUS_LUT b
where a.Taxo_Listing_ID = b.Taxo_Listing_ID) c
group by c.species_code
"Randy K" wrote:
> I need to join two tables I guess in a rotate fashion but have not
> been able to figure it out. We have a Taxo_Status table that contains
> up to 2 entries per species. These records need to be joined
> Taxo_Status_LUT table such that one record is turned per species with
> a state status description (category_code = "S") and a federal status
> description (category_code = "F").
> This is the sult set I need
> Species_Code State_Status_Desc Federal_Status_Desc
> HALE Endangered Threatened
> KALA null Sensitive
> MAMU Endangered null
> --
> TAXO_STATUS Table
> Species_Code Taxo_Listing_ID
> HALE 3
> HALE 5
> MAMU 3
> KALA 4
> --
> TAXO_STATUS_LUT Table
> Taxo_Listing_ID Category_Code Status_Desc
> 1 S Sensitive
> 2 S Threatened
> 3 S Endangered
> 4 F Sensitive
> 5 F Threatened
> 6 F Endangered
>|||Thanks to Trey my query has been solved
select ts.species_code,
max(case when category_code='S' then status_desc end) as
Status_Status_Desc,
max(case when category_code='F' then status_desc end) as
Federal_Status_Desc
from taxo_status ts
join taxo_status_lut tsl on ts.taxo_listing_id =tsl.taxo_listing_id
group by ts.species_code
order by ts.species_code
On Fri, 18 Nov 2005 18:41:35 GMT, wawork@.hotmail.com (Randy K) wrote:
>I need to join two tables I guess in a rotate fashion but have not
>been able to figure it out. We have a Taxo_Status table that contains
>up to 2 entries per species. These records need to be joined
>Taxo_Status_LUT table such that one record is turned per species with
>a state status description (category_code = "S") and a federal status
>description (category_code = "F").
>This is the sult set I need
>Species_Code State_Status_Desc Federal_Status_Desc
> HALE Endangered Threatened
> KALA null Sensitive
> MAMU Endangered null
>--
>TAXO_STATUS Table
>Species_Code Taxo_Listing_ID
>HALE 3
>HALE 5
>MAMU 3
>KALA 4
>--
>TAXO_STATUS_LUT Table
>Taxo_Listing_ID Category_Code Status_Desc
> 1 S Sensitive
> 2 S Threatened
> 3 S Endangered
> 4 F Sensitive
> 5 F Threatened
> 6 F Endangered