Need help approximating how much hard drive space I need for my DB table.

In our SQL Server database we will have a table that will be populated with about 2000 records
per day. That is2000 records per day for 5 days per week. Currently the computer we are using has about 50 gigabytes
of available hard drive space on it. We are concerned that maybe we will need a bigger hard drive,
based solely on the number of records entered into this table per day. The problem is I don't
know how to calculate how much hard drive space we need. I think I read that using varchar,
sql server 2005 really optimizes a database. Here is a typical example of data in our
database. I put dots on three lines between the first and last sample record to just
illustrate that there are many records in between.

Basically we only need 8 months of data at a time in the table and then we can purge
records older than 8 months.
Can someone help me approximate how much hard drive space I might need for 8 months of data,
given the following sample record in the database?

Sample: -->34.5 4.08 10.6 .0012

Sample Table in my DB just for illustration:

(PPsquare inch) (Diameter) (Weight gm) (coeffOfSatFriction)

34.5 4.08 10.6 .0012
.
.
.
21.7 3.54 6.22 .019

Why dont you monitor your db size. Get the size on a sunday evening and check the same information on friday evening, you will see how much your db has increased. monitor this over a couple of week and you will get a better estimate. Also, create a job that will alert you if the db size reaches 70% of allocated space.|||Well actually right now we are in design mode and no dat is in the database yet. The software to populate the tables in the database has not even be written at this point in time. Basically we are required to calculate the estimated hard drive space over an 8 month period. Then based on the approximated database size we will make a decision from there. So I need to figure out a way to calculate a good estimate of the size of that data table in the database. I don't know if SQL server does any type of data compression if necessary?|||

Hi,

The estimate depends entirely on the type of data you want to store. For an article on data types sizes, you can have a look at this article:

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

Based on the sample in your post, you could use a decimal with a precision of 9 (http://msdn2.microsoft.com/en-us/library/ms187746.aspx for decimal data type) which takes 5 bytes for each field. So, your estimate would be something like this:

5 (bytes) x 4 (fields) x 2000 (records) x 5 (days) x 36(approx 36 weeks in 8 months) = 7 200 000 bytes = 6.87 Megs of data. So those 50 gigs of data would be more than enough to keep your table.

|||

Thank you very much for the help!

Kind regards

Need help about how to avoid bad plan used by the optimizer

Hi,
We had the big table containing about 600M records and the following query
used to take long time (this is subset of the query but this part takes the
most time and is part of this question/discussion):
Select top 1001 col3, col1, col4, col5
From tab1 au --with (index (idx_tab1))
Where au.col6 = 44431 and au.col7 = 857669662 and au.col8 is null
This table have clustered index on col1 and non-clustered index on (col6,
col7, col3).
Since this col6 = 44431 hold about 120M records in this table so whenever we
run the above query with this col6 as predicate, SQL Server always do
clustered index san and takes about 4 min but we apply the non-clustered
index in the query and time reduces to 3 sec. So the bottom line is due to
the skewed statistics SQL Server feels that index scan will be better that
doing the combination of index seek and bookmark lookup and forgets about
that we specified the TOP 1001 and it will be better to do index seek than
clustered scan.
So far this was working and now we have deployed the partition view in the
database and partition this table on (col7, col1) and now the data is spread
out on different partitions that all are on different databases. Now since we
are running this query against view so we can’t apply index hints so we are
wondering what are the other ways we can force optimizer to use non-clustered
seek or come up with better execution plan by seeing that TOP 1001 hint is
specified.
Isn't it the kind of a bug that SQL Server optimizer don't always use the
index seek when TOP clause is specified with small number of rows to be
returned?
Thanks
--Harvinder
Hi,
If you place a covering index on
Col6,col7,col8,col3,col1,col4,col5
on each of your partition tables . Then the optimizer will use this index
to select your row.
One question though which 1001 rows do you want? because the order is no
guaranteed here and you are not ordering or grouping. So sql will return
the first 1001 rows it finds and the order will be first found first get and
depending on the index used this could be different rows.
kind regards
Greg O
Looking to use CLR in SQL 2005. Try some pre-build CLR Functions and SP
AGS SQL 2005 Utilities, over 20+ functions
http://www.ag-software.com/?tabid=38
"harvinder" <harvinder@.discussions.microsoft.com> wrote in message
news:4367F496-593E-4118-8128-E4F1FA8815AA@.microsoft.com...
> Hi,
> We had the big table containing about 600M records and the following query
> used to take long time (this is subset of the query but this part takes
> the
> most time and is part of this question/discussion):
> Select top 1001 col3, col1, col4, col5
> From tab1 au --with (index (idx_tab1))
> Where au.col6 = 44431 and au.col7 = 857669662 and au.col8 is null
> This table have clustered index on col1 and non-clustered index on (col6,
> col7, col3).
> Since this col6 = 44431 hold about 120M records in this table so whenever
> we
> run the above query with this col6 as predicate, SQL Server always do
> clustered index san and takes about 4 min but we apply the non-clustered
> index in the query and time reduces to 3 sec. So the bottom line is due to
> the skewed statistics SQL Server feels that index scan will be better that
> doing the combination of index seek and bookmark lookup and forgets about
> that we specified the TOP 1001 and it will be better to do index seek than
> clustered scan.
> So far this was working and now we have deployed the partition view in the
> database and partition this table on (col7, col1) and now the data is
> spread
> out on different partitions that all are on different databases. Now since
> we
> are running this query against view so we can't apply index hints so we
> are
> wondering what are the other ways we can force optimizer to use
> non-clustered
> seek or come up with better execution plan by seeing that TOP 1001 hint is
> specified.
> Isn't it the kind of a bug that SQL Server optimizer don't always use the
> index seek when TOP clause is specified with small number of rows to be
> returned?
> Thanks
> --Harvinder
>
|||On Thu, 17 Nov 2005 12:50:16 -0800, harvinder
<harvinder@.discussions.microsoft.com> wrote:
>We had the big table containing about 600M records and the following query
>used to take long time (this is subset of the query but this part takes the
>most time and is part of this question/discussion):
> Select top 1001 col3, col1, col4, col5
> From tab1 au --with (index (idx_tab1))
> Where au.col6 = 44431 and au.col7 = 857669662 and au.col8 is null
>This table have clustered index on col1 and non-clustered index on (col6,
>col7, col3).
>Since this col6 = 44431 hold about 120M records in this table so whenever we
>run the above query with this col6 as predicate, SQL Server always do
>clustered index san and takes about 4 min but we apply the non-clustered
>index in the query and time reduces to 3 sec.
...
>Isn't it the kind of a bug that SQL Server optimizer don't always use the
>index seek when TOP clause is specified with small number of rows to be
>returned?
When you ask for 1001 records, this may be enough for the optimizer to
decide to scan rather than index. What happens if you ask for top 10?
A covering index to fit the query would of course work (we hope!), but
if you have many queries, such combinatorics can kill you. Another
way to go is to put separate indexes on col6 alone and col7 alone and
col8 alone, and see if SQLServer can order them by selectivity, do a
hash match, and get you good results.
Josh

Need help about how to avoid bad plan used by the optimizer

Hi,
We had the big table containing about 600M records and the following query
used to take long time (this is subset of the query but this part takes the
most time and is part of this question/discussion):
Select top 1001 col3, col1, col4, col5
From tab1 au --with (index (idx_tab1))
Where au.col6 = 44431 and au.col7 = 857669662 and au.col8 is null
This table have clustered index on col1 and non-clustered index on (col6,
col7, col3).
Since this col6 = 44431 hold about 120M records in this table so whenever we
run the above query with this col6 as predicate, SQL Server always do
clustered index san and takes about 4 min but we apply the non-clustered
index in the query and time reduces to 3 sec. So the bottom line is due to
the skewed statistics SQL Server feels that index scan will be better that
doing the combination of index seek and bookmark lookup and forgets about
that we specified the TOP 1001 and it will be better to do index seek than
clustered scan.
So far this was working and now we have deployed the partition view in the
database and partition this table on (col7, col1) and now the data is spread
out on different partitions that all are on different databases. Now since we
are running this query against view so we canâ't apply index hints so we are
wondering what are the other ways we can force optimizer to use non-clustered
seek or come up with better execution plan by seeing that TOP 1001 hint is
specified.
Isn't it the kind of a bug that SQL Server optimizer don't always use the
index seek when TOP clause is specified with small number of rows to be
returned?
Thanks
--HarvinderHi,
If you place a covering index on
Col6,col7,col8,col3,col1,col4,col5
on each of your partition tables . Then the optimizer will use this index
to select your row.
One question though which 1001 rows do you want? because the order is no
guaranteed here and you are not ordering or grouping. So sql will return
the first 1001 rows it finds and the order will be first found first get and
depending on the index used this could be different rows.
kind regards
Greg O
--
Looking to use CLR in SQL 2005. Try some pre-build CLR Functions and SP
AGS SQL 2005 Utilities, over 20+ functions
http://www.ag-software.com/?tabid=38
"harvinder" <harvinder@.discussions.microsoft.com> wrote in message
news:4367F496-593E-4118-8128-E4F1FA8815AA@.microsoft.com...
> Hi,
> We had the big table containing about 600M records and the following query
> used to take long time (this is subset of the query but this part takes
> the
> most time and is part of this question/discussion):
> Select top 1001 col3, col1, col4, col5
> From tab1 au --with (index (idx_tab1))
> Where au.col6 = 44431 and au.col7 = 857669662 and au.col8 is null
> This table have clustered index on col1 and non-clustered index on (col6,
> col7, col3).
> Since this col6 = 44431 hold about 120M records in this table so whenever
> we
> run the above query with this col6 as predicate, SQL Server always do
> clustered index san and takes about 4 min but we apply the non-clustered
> index in the query and time reduces to 3 sec. So the bottom line is due to
> the skewed statistics SQL Server feels that index scan will be better that
> doing the combination of index seek and bookmark lookup and forgets about
> that we specified the TOP 1001 and it will be better to do index seek than
> clustered scan.
> So far this was working and now we have deployed the partition view in the
> database and partition this table on (col7, col1) and now the data is
> spread
> out on different partitions that all are on different databases. Now since
> we
> are running this query against view so we can't apply index hints so we
> are
> wondering what are the other ways we can force optimizer to use
> non-clustered
> seek or come up with better execution plan by seeing that TOP 1001 hint is
> specified.
> Isn't it the kind of a bug that SQL Server optimizer don't always use the
> index seek when TOP clause is specified with small number of rows to be
> returned?
> Thanks
> --Harvinder
>|||On Thu, 17 Nov 2005 12:50:16 -0800, harvinder
<harvinder@.discussions.microsoft.com> wrote:
>We had the big table containing about 600M records and the following query
>used to take long time (this is subset of the query but this part takes the
>most time and is part of this question/discussion):
> Select top 1001 col3, col1, col4, col5
> From tab1 au --with (index (idx_tab1))
> Where au.col6 = 44431 and au.col7 = 857669662 and au.col8 is null
>This table have clustered index on col1 and non-clustered index on (col6,
>col7, col3).
>Since this col6 = 44431 hold about 120M records in this table so whenever we
>run the above query with this col6 as predicate, SQL Server always do
>clustered index san and takes about 4 min but we apply the non-clustered
>index in the query and time reduces to 3 sec.
...
>Isn't it the kind of a bug that SQL Server optimizer don't always use the
>index seek when TOP clause is specified with small number of rows to be
>returned?
When you ask for 1001 records, this may be enough for the optimizer to
decide to scan rather than index. What happens if you ask for top 10?
A covering index to fit the query would of course work (we hope!), but
if you have many queries, such combinatorics can kill you. Another
way to go is to put separate indexes on col6 alone and col7 alone and
col8 alone, and see if SQLServer can order them by selectivity, do a
hash match, and get you good results.
Josh

Need help about how to avoid bad plan used by the optimizer

Hi,
We had the big table containing about 600M records and the following query
used to take long time (this is subset of the query but this part takes the
most time and is part of this question/discussion):
Select top 1001 col3, col1, col4, col5
From tab1 au --with (index (idx_tab1))
Where au.col6 = 44431 and au.col7 = 857669662 and au.col8 is null
This table have clustered index on col1 and non-clustered index on (col6,
col7, col3).
Since this col6 = 44431 hold about 120M records in this table so whenever we
run the above query with this col6 as predicate, SQL Server always do
clustered index san and takes about 4 min but we apply the non-clustered
index in the query and time reduces to 3 sec. So the bottom line is due to
the skewed statistics SQL Server feels that index scan will be better that
doing the combination of index seek and bookmark lookup and forgets about
that we specified the TOP 1001 and it will be better to do index seek than
clustered scan.
So far this was working and now we have deployed the partition view in the
database and partition this table on (col7, col1) and now the data is spread
out on different partitions that all are on different databases. Now since w
e
are running this query against view so we can’t apply index hints so we ar
e
wondering what are the other ways we can force optimizer to use non-clustere
d
seek or come up with better execution plan by seeing that TOP 1001 hint is
specified.
Isn't it the kind of a bug that SQL Server optimizer don't always use the
index seek when TOP clause is specified with small number of rows to be
returned?
Thanks
--HarvinderHi,
If you place a covering index on
Col6,col7,col8,col3,col1,col4,col5
on each of your partition tables . Then the optimizer will use this index
to select your row.
One question though which 1001 rows do you want? because the order is no
guaranteed here and you are not ordering or grouping. So sql will return
the first 1001 rows it finds and the order will be first found first get and
depending on the index used this could be different rows.
kind regards
Greg O
--
Looking to use CLR in SQL 2005. Try some pre-build CLR Functions and SP
AGS SQL 2005 Utilities, over 20+ functions
http://www.ag-software.com/?tabid=38
"harvinder" <harvinder@.discussions.microsoft.com> wrote in message
news:4367F496-593E-4118-8128-E4F1FA8815AA@.microsoft.com...
> Hi,
> We had the big table containing about 600M records and the following query
> used to take long time (this is subset of the query but this part takes
> the
> most time and is part of this question/discussion):
> Select top 1001 col3, col1, col4, col5
> From tab1 au --with (index (idx_tab1))
> Where au.col6 = 44431 and au.col7 = 857669662 and au.col8 is null
> This table have clustered index on col1 and non-clustered index on (col6,
> col7, col3).
> Since this col6 = 44431 hold about 120M records in this table so whenever
> we
> run the above query with this col6 as predicate, SQL Server always do
> clustered index san and takes about 4 min but we apply the non-clustered
> index in the query and time reduces to 3 sec. So the bottom line is due to
> the skewed statistics SQL Server feels that index scan will be better that
> doing the combination of index seek and bookmark lookup and forgets about
> that we specified the TOP 1001 and it will be better to do index seek than
> clustered scan.
> So far this was working and now we have deployed the partition view in the
> database and partition this table on (col7, col1) and now the data is
> spread
> out on different partitions that all are on different databases. Now since
> we
> are running this query against view so we can't apply index hints so we
> are
> wondering what are the other ways we can force optimizer to use
> non-clustered
> seek or come up with better execution plan by seeing that TOP 1001 hint is
> specified.
> Isn't it the kind of a bug that SQL Server optimizer don't always use the
> index seek when TOP clause is specified with small number of rows to be
> returned?
> Thanks
> --Harvinder
>|||On Thu, 17 Nov 2005 12:50:16 -0800, harvinder
<harvinder@.discussions.microsoft.com> wrote:
>We had the big table containing about 600M records and the following query
>used to take long time (this is subset of the query but this part takes the
>most time and is part of this question/discussion):
> Select top 1001 col3, col1, col4, col5
> From tab1 au --with (index (idx_tab1))
> Where au.col6 = 44431 and au.col7 = 857669662 and au.col8 is null
>This table have clustered index on col1 and non-clustered index on (col6,
>col7, col3).
>Since this col6 = 44431 hold about 120M records in this table so whenever w
e
>run the above query with this col6 as predicate, SQL Server always do
>clustered index san and takes about 4 min but we apply the non-clustered
>index in the query and time reduces to 3 sec.
...
>Isn't it the kind of a bug that SQL Server optimizer don't always use the
>index seek when TOP clause is specified with small number of rows to be
>returned?
When you ask for 1001 records, this may be enough for the optimizer to
decide to scan rather than index. What happens if you ask for top 10?
A covering index to fit the query would of course work (we hope!), but
if you have many queries, such combinatorics can kill you. Another
way to go is to put separate indexes on col6 alone and col7 alone and
col8 alone, and see if SQLServer can order them by selectivity, do a
hash match, and get you good results.
Josh

Need Help - Custome Paging Using ROW_NUMBER()

Hi,

I am attempting to implement a custome paging solution for my web Application, I have a table that has 30,000 records and I need to bw able to page through these using a Gridview. Here is my curent code but it generates an error when I try to compile the Stored Procedure, I get the following errors:

<Error messages>

These are on the first SELECT Line..

Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "dbo.NAME.CODE" could not be bound.
Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "dbo.NAME.LAST_NAME" could not be bound.
Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "dbo.NAME.FIRST_NAME" could not be bound.
Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "dbo.NAME.MIDDLE_NAME" could not be bound.
Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "dbo.NAMETYPE.TYPE" could not be bound.
Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "dbo.FUNERAL.NUMBER" could not be bound.
Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "mort.NAME.CODE" could not be bound.
Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "NAME.LAST_NAME" could not be bound.
Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "NAME.FIRST_NAME" could not be bound.
Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "NAME.MIDDLE_NAME" could not be bound.
Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "NAMETYPE.TYPE" could not be bound.
Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "FUNERAL.NUMBER" could not be bound.

</Error Messages>

<Sotred Procedure>

CREATE PROCEDURE proc_NAMEGetPaged
@.startRowIndex int,
@.maximumRows int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

SELECT NAME.CODE, NAME.LAST_NAME, NAME.FIRST_NAME + ' ' + NAME.MIDDLE_NAME AS Name, NAMETYPE.TYPE, FUNERAL.NUMBER
FROM
(SELECT CODE, LAST_NAME, FIRST_NAME + ' ' + MIDDLE_NAME AS Name, NAMETYPE.TYPE, FUNERAL.NUMBER,
ROW_NUMBER() OVER(ORDER BY LAST_NAME) as RowNum
FROM Name n) as NameInfo
WHERE RowNum BETWEEN @.startRowIndex AND (@.startRowIndex + @.maximumRows) -1
END
GO

</Stored Procedure>

Any assistance in resolving this would be greatly appreciated..

Regards..

Peter.

Hi,

Have resolved my problem see code below..

<Stored Procedure>

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Peter Annandale
-- Create date: 21/10/2006
-- Description: Return Subset from Names table
-- =============================================
CREATE PROCEDURE proc_NAMEGetPaged
-- Add the parameters for the stored procedure here
@.startRowIndex int,
@.maximumRows int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT CODE, LAST_NAME, Name, TYPE, NUMBER
FROM
(SELECT n.CODE, n.LAST_NAME, n.FIRST_NAME + ' ' + n.MIDDLE_NAME AS Name, nt.TYPE, f.NUMBER,
ROW_NUMBER() OVER(ORDER BY n.LAST_NAME) as RowNum
FROM dbo.NAME n
LEFT OUTER JOIN NAMETYPE nt ON n.NAME_TYPE = nt.NAME_TYPE
LEFT OUTER JOIN FUNERAL f ON n.CODE = f.DECEASED) as NameInfo
WHERE RowNum BETWEEN @.startRowIndex AND (@.startRowIndex + @.maximumRows) -1
END
GO

< /Stored Procedure>

Regards..

Peter.

Need Execute SQL component to fail package if zero records

I have a SSIS package that has several Execute SQL Components. One of the first components reurns a Full Result Set of IDs based on a stored procedure call. The stored procedure can return multiple rows. I store the results to an ADO recordset (object variable) to be used later. I want the component to fail, and the package if the return of the stored procedure is zero records. What is the best way to do this? I had a raise error statement if @.@.rowcount was zero but this did not fail the component. Any other suggestions?A way could be to have an extra execute sql task at the begginig with a Select count(*) from...; put the value into a variable and then use an expression in the precedence constraint to continue only if the variable value is greater than 0.|||I have thought about that as a work around. But it seems to me that I should be able to throw an error via RAISERROR or some other method in the stored procedure and have it result in the execute sql statement in the dts package to error as well.|||I don't see another way of doing using a single Execute SQL task. To be honest I don't see anything wrong on implementing pre execution logic in yiour package as far a performande does not suffer too much. Perhaps, you may want to write your own solution using an script task.|||I guess that is the path I will go. I was just figuring there may be an easier way since the Single Result Set fails if nothing is returned, I thought the Full Result Set may have been able to function in the same way. Thanks for the input.

Need Execute SQL component to fail package if zero records

I have a SSIS package that has several Execute SQL Components. One of the first components reurns a Full Result Set of IDs based on a stored procedure call. The stored procedure can return multiple rows. I store the results to an ADO recordset (object variable) to be used later. I want the component to fail, and the package if the return of the stored procedure is zero records. What is the best way to do this? I had a raise error statement if @.@.rowcount was zero but this did not fail the component. Any other suggestions?A way could be to have an extra execute sql task at the begginig with a Select count(*) from...; put the value into a variable and then use an expression in the precedence constraint to continue only if the variable value is greater than 0.|||I have thought about that as a work around. But it seems to me that I should be able to throw an error via RAISERROR or some other method in the stored procedure and have it result in the execute sql statement in the dts package to error as well.|||I don't see another way of doing using a single Execute SQL task. To be honest I don't see anything wrong on implementing pre execution logic in yiour package as far a performande does not suffer too much. Perhaps, you may want to write your own solution using an script task.|||I guess that is the path I will go. I was just figuring there may be an easier way since the Single Result Set fails if nothing is returned, I thought the Full Result Set may have been able to function in the same way. Thanks for the input.

Need Better Performance

Dear friends,
I am having 10GB for my database. In my table having 10 Millian records per day. I want to select records per date and by product status. So,

1. How can I design my Database initialization paramet ?

2. How can I tune my database for better performance.

My Machine Configuration :

Windows 2003 Server OS,1 GB Ram, 80 GB Hard Disk, 3.99 GHz

We need more details about your hardware and environment, along with your table schema and one of the queries you want to tune.

Having a single hard drive for a SQL Server instance is going to be a big bottleneck.

Need basic SQL query help

Trying to select unique records from a database. A combination of 2 variables make the record unique. Need to export all rows of the return. I have below rudimentary sql skills (select/where) and cannot figure this one out. The database has ~ 180k rows, of which there are only ~ 50k unique records.

The data is on a single table, 15 seperate columns. It is a table containing network router data. The existing table set up shows the historical progression of changes/updates to these routers. I am trying to determine the latest records of the routers in question. Since some of the IP addresses have been used multiple times (moved from site A to site B for example), there are multiple dupe records that I do not want.
The two fields I need to use to identify unique records are:
serial_IP and site_id
In addition to that, I would also like to know how to format "max date" query, so that I get the latest information. I can do that manually if I must via a sort when I export the data, but it would be nice to know how to do that in the future.

thanks

noclueAs those two columns make a unique key on the table, there is possibility of use of a DISTINCT keyword, such as

SELECT DISTINCT serial_IP, site_id FROM your_table;

However, regarding your next request (selecting records with the latest date), there's need to use a subquery:SELECT DISTINCT t.serial_IP, t.site_id
FROM your_table t
WHERE t.date_column = (SELECT max(t1.date_column)
FROM your_table t1
WHERE t1.serial_IP = t.serial_IP
AND t1.site_id = t.site_id
);The DISTINCT keyword might, or might not be needed in this case: if table has only one record per "date_column", you won't need that. If there are multiple records per every "date_column" value, you'll still need it to eliminate multiple records.

Need a way to switch specific data from columns

Basically I have 635k records in a table with a person's first name, and date of birth (other stuff but it's not relavent). I imported all the data from excel files, but somehow a bunch of records got the first name and date of birth mixed up, so I'm trying to write a stored procedure that would switch the first name with the date of birth wherever the firstname is purely numeric, or something of the sort. Now records are in fact repeated so another possible but more time taking solution is to write a stored procedure that I give the date of birth and it does the switching around for the respective date of birth when it's found inside the First name. Any suggestions? All the code I've written has proved useless :/

so I'm trying to write a stored procedure that would switch the first name with the date of birth wherever the firstname is purely numeric, or something of the sort.

If you have an ID column all you would need to do is check if there are multiple records (count(*)> 1). if so keep the first one (min(id) or whichever you choose), delete the rest, get the two values into local variables and update the record in hand.
if you already made an attempt post some code and we can help you out.

Need a SP to read Table A and update Table B

Hi

I need a SP to read table A (1000 records) and updates Table B. I think I have to use Cursor, but I'm not very good at writing SP. Any help would be appreciated. Thanks.You need to help us a little bit. What, if any relationship exists between Table A and B? Is there a column in each that links one to the other?|||The two tables (A and B) are identical and both have a primary key. What I need to do is to update all rows in Table B with the new one from Table A. This is like a batch update from A to B.

Need a fast queue using a table

I am trying to implement a very fast queue using SQL Server.

The queue table will contain tens of millions of records.

The problem I have is the more records completed, the the slower it
gets. I don't want to remove data from the queue because I use the
same table to store results. The queue handles concurrent requests.

The status field will contain the following values:
0 = Waiting
1 = Started
2 = Finished

Any help would be greatly appreciated.

Here is a simplified script to demonstrate what has been done.

CREATE TABLE [dbo].[Queue] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[JobID] [int] NOT NULL ,
[Status] [tinyint] NOT NULL
) ON [PRIMARY]
GO

CREATE INDEX [Status] ON [dbo].[Queue]([Status]) ON [PRIMARY]
GO

CREATE PROCEDURE dbo.NextItem
@.JobID integer,
@.ID integer output
AS
SELECT TOP 1 @.ID = [ID]
FROM Queue WITH (READPAST, XLOCK)
WHERE (Status = 0) AND (JobID = @.JobID)
RETURN
GOmy idea: i would use 3 different tables, one for waiting entries,
one for the running entries and one for the finished.
And when status is changing instead of updating field "status"
(which is now not longer necessary) move the record from one
table to the other.

hth,
Helmut

"Chris Foster" <chrisfoster@.btinternet.com> schrieb im Newsbeitrag
news:b311a0b8.0307020655.9a5beeb@.posting.google.co m...
> I am trying to implement a very fast queue using SQL Server.
> The queue table will contain tens of millions of records.
> The problem I have is the more records completed, the the slower it
> gets. I don't want to remove data from the queue because I use the
> same table to store results. The queue handles concurrent requests.
> The status field will contain the following values:
> 0 = Waiting
> 1 = Started
> 2 = Finished
> Any help would be greatly appreciated.
> Here is a simplified script to demonstrate what has been done.
> CREATE TABLE [dbo].[Queue] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [JobID] [int] NOT NULL ,
> [Status] [tinyint] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE INDEX [Status] ON [dbo].[Queue]([Status]) ON [PRIMARY]
> GO
> CREATE PROCEDURE dbo.NextItem
> @.JobID integer,
> @.ID integer output
> AS
> SELECT TOP 1 @.ID = [ID]
> FROM Queue WITH (READPAST, XLOCK)
> WHERE (Status = 0) AND (JobID = @.JobID)
> RETURN
> GO|||[posted and mailed, please reply in public]

Chris Foster (chrisfoster@.btinternet.com) writes:
> The queue table will contain tens of millions of records.
> The problem I have is the more records completed, the the slower it
> gets. I don't want to remove data from the queue because I use the
> same table to store results. The queue handles concurrent requests.
> The status field will contain the following values:
> 0 = Waiting
> 1 = Started
> 2 = Finished
>...
> CREATE TABLE [dbo].[Queue] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [JobID] [int] NOT NULL ,
> [Status] [tinyint] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE INDEX [Status] ON [dbo].[Queue]([Status]) ON [PRIMARY]
> GO
> CREATE PROCEDURE dbo.NextItem
> @.JobID integer,
> @.ID integer output
> AS
> SELECT TOP 1 @.ID = [ID]
> FROM Queue WITH (READPAST, XLOCK)
> WHERE (Status = 0) AND (JobID = @.JobID)
> RETURN
> GO

Since you have no index on JobID, I am not surprise if this is running
slow beyond all belief. The index you have on Status is likely to be
worthless, not being selective enough.

Helmut Wss suggested using three tables, and this may be worth considering.

However, you should start with getting a decent index structure. It
is from the table definition unclear to be whether there can be more
than one row for the same JobID. If there is, add a clustered index on
JobID and Status. If JobID is infact unique, get rid of that Identity
column. If your aim is to get high speed, then you should trim the
table, since the smaller the rows, the more rows you can fit on a page,
and the faster you can do I/O.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thank you for your reply

The script I submitted is a smaller sample of what I have. I forgot to
state that the JobID has a foreign key to another table, there will be
many JobIds. Am I correct in assuming that a foreign key is indexed?
I can't set the index to be clustered, because it will only allow one
clustered index, ID is the primary key and is clustered.

The final queue will contain more fields than stated, if this effects
the speed I guess I will have to use a separate queuing table.

My first thought was that if status is indexed and has a value between
0 and 2, the index would be sorted ascending and 0's would be at the
top, therefore would remain the same speed throughout the queue. I am
not sure exactly what indexing does behind the scenes, I should
probably look into this. What difference would it make creating a
composite index of JobID and Status?

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns93AD579174Yazorman@.127.0.0.1>...
> [posted and mailed, please reply in public]
> Chris Foster (chrisfoster@.btinternet.com) writes:
> > The queue table will contain tens of millions of records.
> > The problem I have is the more records completed, the the slower it
> > gets. I don't want to remove data from the queue because I use the
> > same table to store results. The queue handles concurrent requests.
> > The status field will contain the following values:
> > 0 = Waiting
> > 1 = Started
> > 2 = Finished
> >...
> > CREATE TABLE [dbo].[Queue] (
> > [ID] [int] IDENTITY (1, 1) NOT NULL ,
> > [JobID] [int] NOT NULL ,
> > [Status] [tinyint] NOT NULL
> > ) ON [PRIMARY]
> > GO
> > CREATE INDEX [Status] ON [dbo].[Queue]([Status]) ON [PRIMARY]
> > GO
> > CREATE PROCEDURE dbo.NextItem
> > @.JobID integer,
> > @.ID integer output
> > AS
> > SELECT TOP 1 @.ID = [ID]
> > FROM Queue WITH (READPAST, XLOCK)
> > WHERE (Status = 0) AND (JobID = @.JobID)
> > RETURN
> > GO
> Since you have no index on JobID, I am not surprise if this is running
> slow beyond all belief. The index you have on Status is likely to be
> worthless, not being selective enough.
> Helmut Wss suggested using three tables, and this may be worth considering.
> However, you should start with getting a decent index structure. It
> is from the table definition unclear to be whether there can be more
> than one row for the same JobID. If there is, add a clustered index on
> JobID and Status. If JobID is infact unique, get rid of that Identity
> column. If your aim is to get high speed, then you should trim the
> table, since the smaller the rows, the more rows you can fit on a page,
> and the faster you can do I/O.|||Hi Chris

If you are after speed and with those sorts of volumes, I would not be
recommending that you used SQL Server to implement a TP queue in the
first place to store requests that are either waiting or started.
There are other options worth considering including IBM WebSphere
MQSeries.

chrisfoster@.btinternet.com (Chris Foster) wrote in message news:<b311a0b8.0307020655.9a5beeb@.posting.google.com>...
> I am trying to implement a very fast queue using SQL Server.
> The queue table will contain tens of millions of records.
> The problem I have is the more records completed, the the slower it
> gets. I don't want to remove data from the queue because I use the
> same table to store results. The queue handles concurrent requests.
> The status field will contain the following values:
> 0 = Waiting
> 1 = Started
> 2 = Finished
> Any help would be greatly appreciated.
> Here is a simplified script to demonstrate what has been done.
> CREATE TABLE [dbo].[Queue] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [JobID] [int] NOT NULL ,
> [Status] [tinyint] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE INDEX [Status] ON [dbo].[Queue]([Status]) ON [PRIMARY]
> GO
> CREATE PROCEDURE dbo.NextItem
> @.JobID integer,
> @.ID integer output
> AS
> SELECT TOP 1 @.ID = [ID]
> FROM Queue WITH (READPAST, XLOCK)
> WHERE (Status = 0) AND (JobID = @.JobID)
> RETURN
> GO|||Chris Foster (chrisfoster@.btinternet.com) writes:
> The script I submitted is a smaller sample of what I have. I forgot to
> state that the JobID has a foreign key to another table, there will be
> many JobIds. Am I correct in assuming that a foreign key is indexed?

No. There are no automatic indexes created on foriegn-key columns. You need
to add any index yourself.

> I can't set the index to be clustered, because it will only allow one
> clustered index, ID is the primary key and is clustered.

On second thought, a non-clustered index on (JobId, Status) is likely
to be ideal. You could add ID explicitly to this index, but it is in fact
already there, because for a non-clustered index, SQL Server uses the
clustered key as the address to the data page.

The point here is that you get a *covering index*, which means that
SQL Server can resolve the query from the index alone. This is good
for speed. Since the index nodes are smaller than the complete rows,
you get more rows per page, and fewer pages to read.

> My first thought was that if status is indexed and has a value between
> 0 and 2, the index would be sorted ascending and 0's would be at the
> top, therefore would remain the same speed throughout the queue. I am
> not sure exactly what indexing does behind the scenes, I should
> probably look into this.

Yes, you should. :-) In a non-clustered index on Status only, SQL
Server needs to go to the data pages to find JobId so it can compare
this condition. This can be a more expensive operation that scanning
the table from left to right, because some pages may have to be read
more than twice. SQL Server uses the statistics is has on Status
to determine whether using the index is a viable way to go. You can
force SQL Server to use the index, by means of an index hint. You may find
that this gives even worse performance.

> What difference would it make creating a composite index of JobID and
> Status?

Because now SQL Server has all the information to resolve the query in
the index.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Indexing both JobID and Status has made the queue work at an acceptable speed.

Thanks you

Chris

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns93AF515713E6Yazorman@.127.0.0.1>...
> Chris Foster (chrisfoster@.btinternet.com) writes:
> > The script I submitted is a smaller sample of what I have. I forgot to
> > state that the JobID has a foreign key to another table, there will be
> > many JobIds. Am I correct in assuming that a foreign key is indexed?
> No. There are no automatic indexes created on foriegn-key columns. You need
> to add any index yourself.
> > I can't set the index to be clustered, because it will only allow one
> > clustered index, ID is the primary key and is clustered.
> On second thought, a non-clustered index on (JobId, Status) is likely
> to be ideal. You could add ID explicitly to this index, but it is in fact
> already there, because for a non-clustered index, SQL Server uses the
> clustered key as the address to the data page.
> The point here is that you get a *covering index*, which means that
> SQL Server can resolve the query from the index alone. This is good
> for speed. Since the index nodes are smaller than the complete rows,
> you get more rows per page, and fewer pages to read.
> > My first thought was that if status is indexed and has a value between
> > 0 and 2, the index would be sorted ascending and 0's would be at the
> > top, therefore would remain the same speed throughout the queue. I am
> > not sure exactly what indexing does behind the scenes, I should
> > probably look into this.
> Yes, you should. :-) In a non-clustered index on Status only, SQL
> Server needs to go to the data pages to find JobId so it can compare
> this condition. This can be a more expensive operation that scanning
> the table from left to right, because some pages may have to be read
> more than twice. SQL Server uses the statistics is has on Status
> to determine whether using the index is a viable way to go. You can
> force SQL Server to use the index, by means of an index hint. You may find
> that this gives even worse performance.
> > What difference would it make creating a composite index of JobID and
> > Status?
> Because now SQL Server has all the information to resolve the query in
> the index.

Need a detail section which runs horizontally

I'm make a report which has a detail section that runs left to right (records 1 to 3) then to the next line down etc... so line 2 will have records 4 to 5.

Traditionally detail sections run top to bottom and this is very easy to design but I'm at a complete loss as to how to do it with the records running left to right as well. Perhaps you'd need to somehow manual increment the record pointer ??

Crystal reports v9

Thanks for the helpDoes CR9 have a 'Format with multiple columns' checkbox in the section expert?
If so, check it and then choose the 'Layout' tab that appears. You'll then need to experiment with the values in that tab - see help on 'Layout tab (Section Expert)'.

Need - help to create DLOOKUP with SSIS

Hi,

Does anybody knows how to create a DLOOKUP (dynamic lookup) in SSIS withour writing any kind of script?
I need to test records existance in destination from the source before inserting or updating in the destination (if the record exist in destination then update, else insert).

Any help apreciated.
I find the Lookup component works best for this. Jamie wrote an article that compares lookup as well as other methods.
http://www.sqlis.com/default.aspx?311
Adrian

Nearest distance

Hi
How do I get a nearest distance of a point? For example, I have two tables A and B and I want to find the nearest distance between the records of the two tables. In addition, one of the tables should also give me the distance. The data I have geo spatial data. Can this be done in SQL
Help will be appreciatedThey talk about something like that here, in their database section...

http://skyserver.fnal.gov/en/

As far as understanding it....I have enough trouble tieing my shoes...|||Hi
I looked through the site but is not of much help..can you elaborate
thanks

Originally posted by Brett Kaiser
They talk about something like that here, in their database section...

http://skyserver.fnal.gov/en/

As far as understanding it....I have enough trouble tieing my shoes...|||Originally posted by Brett Kaiser
As far as understanding it....I have enough trouble tieing my shoes...

You didn't get this part, huh...

What are you trying to do...maybe it's a simple answer...

"Distance" is a relative thing...

Got indexes?|||Hi
I need the nearest distance in miles of one point from the other.

Originally posted by namitao
Hi
How do I get a nearest distance of a point? For example, I have two tables A and B and I want to find the nearest distance between the records of the two tables. In addition, one of the tables should also give me the distance. The data I have geo spatial data. Can this be done in SQL
Help will be appreciated|||Nearest distance between two values? A distance can only be "nearest" to one point (in the general case), because you can't optimize for more than one criteria. Are you talking about some kind of linear regression between the points?

I think you need to post some sample data and an example of the result you are looking for.|||I'm confused...I originally thought you wanted the distance between rows of data...

Do you want the distance between points on a map?

I did this once for a delivery system...

You want to google the "Great Circle" trig function...I can't find the math...but here's someone who built something...

http://williams.best.vwh.net/gccalc.htm

But you need longitude and latitude of the addresses...

Is that what you're looking for?|||Hi
For Example I have 2 tables
Table A
Number Latitude Longitude Time total
1 48.2951 -122.276 1:22:49 -87
2 48.2952 -122.292 1:17:35 -92
3 48.2952 -122.292 1:16:35 -91
4 48.2952 -122.276 1:21:59 -86
5 48.2952 -122.276 1:22:48 -91
6 48.2953 -122.292 1:17:34 -87
Table B
Number Latitude Longitude Time total_c
1 48.2904 -122.271 1:24:30 -87
2 48.2904 -122.271 1:23:51 -88
3 48.2904 -122.271 1:24:29 -87
4 48.2904 -122.271 1:23:52 -85
5 48.2904 -122.271 1:24:28 -86.5
6 48.2904 -122.271 1:23:53 -86

I need to find the shortest distance between the points of the 2 tables.
I need to find the shortest distance of points in Table B to point in Table A.
I hope this can help someone answer my question
:(
Thanks


Originally posted by Brett Kaiser
I'm confused...I originally thought you wanted the distance between rows of data...

Do you want the distance between points on a map?

I did this once for a delivery system...

You want to google the "Great Circle" trig function...I can't find the math...but here's someone who built something...

http://williams.best.vwh.net/gccalc.htm

But you need longitude and latitude of the addresses...

Is that what you're looking for?|||I did this in Access once...

You need the formual

http://en2.wikipedia.org/wiki/Great_circle_distance

Then create it as a udf and join the tables passing the 2 ponts in...

Have to udf return the distance...

I should rewrite this in sql server...I'll have to dig it up...

It was actually a lot of fun building it (...geez what a geek)

NEAR operator

Is it possible in any way to control the NEAR operator so that it returns
only records containing the search words within a certain distance - e.g
within 3 words or 5 words or a paragraph etc.?
Apparently the way the NEAR operator works is that it returns all (or almost
all..) the records containing the specified words, and then ranks them based
on the words 'nearness'. The problem with this approach is that if the result
of the search are display ordered not by rank but by some other criteria
using 'NEAR' is exactly the same as using 'AND' - and as a matter of fact
newspaper librarians and journalist always sort the result of a search by
publishing date, not by ranking, so no NEAR operator with SQL full text for
them.
Thank you
- Michele
Michele,
Unfortunately, no. There is no way to control how the NEAR operator
determines "nearness" as it is hard-coded at 50 words and the "definition of
nearness is fixed inside mssearch", and are not user controllable :-(
The following quote (from a Microsoft FTS Developer) was taken from another
thread on this subject related to SQL Server 2005 (Yukon), but also applies
to SQL Server 2000 and proximity (or NEAR) searches and RANK:
"distance between terms for a match
number of matches
document length
etc..
so it is possible for a document with term1 right next to term2 to return a
lower rank than another document with many matches with greater distance
between terms:
eg:
document1 = term1 term2 word word word word word word word word word word
word word word.... word word
document2 = term1 word term1 word term1 word term2 word term1 word term2
word term1 word term2 word term1 word term2 word term1 word term2
document1 may have a lower rank that document2 because it has fewer matches
even though the one match it has is very "near"."
Hopefully this sheds more light on this subject.
Thanks,
John
"Michele Mottini" <Michele Mottini@.discussions.microsoft.com> wrote in
message news:380FD11C-01C0-4AC8-BC8B-82EC0524F6B7@.microsoft.com...
> Is it possible in any way to control the NEAR operator so that it returns
> only records containing the search words within a certain distance - e.g
> within 3 words or 5 words or a paragraph etc.?
> Apparently the way the NEAR operator works is that it returns all (or
almost
> all..) the records containing the specified words, and then ranks them
based
> on the words 'nearness'. The problem with this approach is that if the
result
> of the search are display ordered not by rank but by some other criteria
> using 'NEAR' is exactly the same as using 'AND' - and as a matter of fact
> newspaper librarians and journalist always sort the result of a search by
> publishing date, not by ranking, so no NEAR operator with SQL full text
for
> them.
> Thank you
> - Michele
>