Can Sql Server do this?

[DavidR]

Question from a SAS programmer:

I don't have much experience with SQL Server, but have used the native SQL
code to create datasets from DB2 tables.

Here where I work, we use SAS primarily to do data processing - for example,
a database of customer records that consists of an updated record for each
customer at the end of each month - with the same variables (fields) such
as the balance.

We routinely take recordsets like this and transform them into one record
for each with a renamed field for each previous duplicate field - balance_03_2001
for example.

We're trying to find alternatives to SAS. Is this something that SQL Server
can do efficiently? Are there better alternatives? What would the solution
to this problem look like?

Thanks,

David Ramsey

[david]

Hi David,

I don't know anything about SAS but if this might be of any help to you,
I would like to say that SQL Server, like any other Relational Database Management
System (RDBMS), suits most of the business information scenarios we might
think of and would therefore be much appropriate for your requirements too.
If you ever switch to SQL Server, you'll most certainly benefit from a wide
variety of features that would make your life easier. You would have Stored
Procedures, Views and Triggers amongst others to help you efficiently organise
and process your data and bcp features (for fast bulk copying data to and
from text files) as well as basic backup tools for your archiving needs.
I do certainly believe SQL Server is a valuable option for your business
requirements for the features, reliability and performance you might obviously
need. You can find more info in the Microsoft site at http://www.microsoft.com.
I develop solutions using VB 5.0 / SQL Server 6.5 / MS Access 97 and if you
think there are areas in which i might help you, please do feel free to mail
me.

Regards,

David
david@servihoo.com


"DavidR" <david.c.ramsey@bankofamerica.com> wrote:
>
>Question from a SAS programmer:
>
>I don't have much experience with SQL Server, but have used the native SQL
>code to create datasets from DB2 tables.
>
>Here where I work, we use SAS primarily to do data processing - for example,
>a database of customer records that consists of an updated record for each
>customer at the end of each month - with the same variables (fields) such
>as the balance.
>
>We routinely take recordsets like this and transform them into one record
>for each with a renamed field for each previous duplicate field - balance_03_2001
>for example.
>
>We're trying to find alternatives to SAS. Is this something that SQL Server
>can do efficiently? Are there better alternatives? What would the solution
>to this problem look like?
>
>Thanks,
>
>David Ramsey

[DavidR]

david,

Thanks so much for the response - here's a scenario type question for you:

I currently have a database that has upwards of 7 million records. MS Access,
as you can well imagine, is seriously slow in doing anything with this large
amount of data. I use Access to link to some RDBMS tables on another system,
and after pulling in the data that I need, the Access file can exceed 800
MB easily. Of course simply performing a sort on that amount of data can
take 30 min. to an hour (typically).

This is why most of the processing to rearrange the data is done remotely
on a Unix based Solaris server using SAS (speed is the benefit.)

Although it is really difficult to estimate, is there a big boost in efficiency/memory
usage/speed if I were to switch the process from Access to SQL Server?




Thanks again,

David Ramsey











"david" <david@servihoo.com> wrote:
>
>Hi David,
>
>I don't know anything about SAS but if this might be of any help to you,
>I would like to say that SQL Server, like any other Relational Database
Management
>System (RDBMS), suits most of the business information scenarios we might
>think of and would therefore be much appropriate for your requirements too.
>If you ever switch to SQL Server, you'll most certainly benefit from a wide
>variety of features that would make your life easier. You would have Stored
>Procedures, Views and Triggers amongst others to help you efficiently organise
>and process your data and bcp features (for fast bulk copying data to and
>from text files) as well as basic backup tools for your archiving needs.
>I do certainly believe SQL Server is a valuable option for your business
>requirements for the features, reliability and performance you might obviously
>need. You can find more info in the Microsoft site at http://www.microsoft.com.
>I develop solutions using VB 5.0 / SQL Server 6.5 / MS Access 97 and if
you
>think there are areas in which i might help you, please do feel free to
mail
>me.
>
>Regards,
>
>David
>david@servihoo.com
>
>
>"DavidR" <david.c.ramsey@bankofamerica.com> wrote:
>>
>>Question from a SAS programmer:
>>
>>I don't have much experience with SQL Server, but have used the native
SQL
>>code to create datasets from DB2 tables.
>>
>>Here where I work, we use SAS primarily to do data processing - for example,
>>a database of customer records that consists of an updated record for each
>>customer at the end of each month - with the same variables (fields) such
>>as the balance.
>>
>>We routinely take recordsets like this and transform them into one record
>>for each with a renamed field for each previous duplicate field - balance_03_2001
>>for example.
>>
>>We're trying to find alternatives to SAS. Is this something that SQL Server
>>can do efficiently? Are there better alternatives? What would the solution
>>to this problem look like?
>>
>>Thanks,
>>
>>David Ramsey
>

[David Macknet]

David,

Yes, SQL Server certainly can. SQL Server is not even something that you'd
typically compare with Access (although many ask the question). If you're
simply interested in sorting records and extracting results (with no updates
to the dataset), SQL Server will allow you to do build indexes on the data
"WITH FILLFACTOR 100" - meaning that all leaf nodes of the binary tree (of
the index) are filled. This means that the index would be hideous if you
were updating data, but will perform quite well for looking things up. This's
just a small sample, but the power of the engine is tremendous. Even if
you weren't to get fancy with fillfactors and pinning tables in memory, you'll
be amazed at the sheer speed with which it operates. There's simply no comparison.

To give you an example from an application I developed:
Every night a job runs to pull in over 5 gigabytes of historical data from
four different sources and summarize it in several ways. This summary operation
takes a little over an hour. The users, with the end data, are able to extract
statistical reports (further aggregating/summarizing the data). The users'
reports take less than 45 seconds to run, even if they're aggregating over
3 million rows.

It was my first SQL Server Application, so I'm sure that I could've done
better.

Comparing Access to SQL Server is like comparing a Yugo to a Ferarri. Once
you've seen the two....

Good luck!

-David

"DavidR" <david.c.ramsey@bankofamerica.com> wrote:
>
>david,
>
>Thanks so much for the response - here's a scenario type question for you:
>
>I currently have a database that has upwards of 7 million records. MS Access,
>as you can well imagine, is seriously slow in doing anything with this large
>amount of data. I use Access to link to some RDBMS tables on another system,
>and after pulling in the data that I need, the Access file can exceed 800
>MB easily. Of course simply performing a sort on that amount of data can
>take 30 min. to an hour (typically).
>
>This is why most of the processing to rearrange the data is done remotely
>on a Unix based Solaris server using SAS (speed is the benefit.)
>
>Although it is really difficult to estimate, is there a big boost in efficiency/memory
>usage/speed if I were to switch the process from Access to SQL Server?
>
>
>
>
>Thanks again,
>
>David Ramsey
>
>
>
>
>
>
>
>
>
>
>
>"david" <david@servihoo.com> wrote:
>>
>>Hi David,
>>
>>I don't know anything about SAS but if this might be of any help to you,
>>I would like to say that SQL Server, like any other Relational Database
>Management
>>System (RDBMS), suits most of the business information scenarios we might
>>think of and would therefore be much appropriate for your requirements
too.
>>If you ever switch to SQL Server, you'll most certainly benefit from a
wide
>>variety of features that would make your life easier. You would have Stored
>>Procedures, Views and Triggers amongst others to help you efficiently organise
>>and process your data and bcp features (for fast bulk copying data to and
>>from text files) as well as basic backup tools for your archiving needs.
>>I do certainly believe SQL Server is a valuable option for your business
>>requirements for the features, reliability and performance you might obviously
>>need. You can find more info in the Microsoft site at http://www.microsoft.com.
>>I develop solutions using VB 5.0 / SQL Server 6.5 / MS Access 97 and if
>you
>>think there are areas in which i might help you, please do feel free to
>mail
>>me.
>>
>>Regards,
>>
>>David
>>david@servihoo.com
>>
>>
>>"DavidR" <david.c.ramsey@bankofamerica.com> wrote:
>>>
>>>Question from a SAS programmer:
>>>
>>>I don't have much experience with SQL Server, but have used the native
>SQL
>>>code to create datasets from DB2 tables.
>>>
>>>Here where I work, we use SAS primarily to do data processing - for example,
>>>a database of customer records that consists of an updated record for
each
>>>customer at the end of each month - with the same variables (fields) such
>>>as the balance.
>>>
>>>We routinely take recordsets like this and transform them into one record
>>>for each with a renamed field for each previous duplicate field - balance_03_2001
>>>for example.
>>>
>>>We're trying to find alternatives to SAS. Is this something that SQL
Server
>>>can do efficiently? Are there better alternatives? What would the solution
>>>to this problem look like?
>>>
>>>Thanks,
>>>
>>>David Ramsey
>>
>

[DavidR]

Thanks again! Since I have the Ferrari installation cd right here, it looks
as though I will be driving much faster in days to come.





"David Macknet" <david_t_macknet@agilent.com> wrote:
>
>David,
>
>Yes, SQL Server certainly can. SQL Server is not even something that you'd
>typically compare with Access (although many ask the question). If you're
>simply interested in sorting records and extracting results (with no updates
>to the dataset), SQL Server will allow you to do build indexes on the data
>"WITH FILLFACTOR 100" - meaning that all leaf nodes of the binary tree (of
>the index) are filled. This means that the index would be hideous if you
>were updating data, but will perform quite well for looking things up.
This's
>just a small sample, but the power of the engine is tremendous. Even if
>you weren't to get fancy with fillfactors and pinning tables in memory,
you'll
>be amazed at the sheer speed with which it operates. There's simply no
comparison.
>
>To give you an example from an application I developed:
>Every night a job runs to pull in over 5 gigabytes of historical data from
>four different sources and summarize it in several ways. This summary operation
>takes a little over an hour. The users, with the end data, are able to
extract
>statistical reports (further aggregating/summarizing the data). The users'
>reports take less than 45 seconds to run, even if they're aggregating over
>3 million rows.
>
>It was my first SQL Server Application, so I'm sure that I could've done
>better.
>
>Comparing Access to SQL Server is like comparing a Yugo to a Ferarri. Once
>you've seen the two....
>
>Good luck!
>
>-David
>
>"DavidR" <david.c.ramsey@bankofamerica.com> wrote:
>>
>>david,
>>
>>Thanks so much for the response - here's a scenario type question for you:
>>
>>I currently have a database that has upwards of 7 million records. MS
Access,
>>as you can well imagine, is seriously slow in doing anything with this
large
>>amount of data. I use Access to link to some RDBMS tables on another system,
>>and after pulling in the data that I need, the Access file can exceed 800
>>MB easily. Of course simply performing a sort on that amount of data
can
>>take 30 min. to an hour (typically).
>>
>>This is why most of the processing to rearrange the data is done remotely
>>on a Unix based Solaris server using SAS (speed is the benefit.)
>>
>>Although it is really difficult to estimate, is there a big boost in efficiency/memory
>>usage/speed if I were to switch the process from Access to SQL Server?
>>
>>
>>
>>
>>Thanks again,
>>
>>David Ramsey
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>"david" <david@servihoo.com> wrote:
>>>
>>>Hi David,
>>>
>>>I don't know anything about SAS but if this might be of any help to you,
>>>I would like to say that SQL Server, like any other Relational Database
>>Management
>>>System (RDBMS), suits most of the business information scenarios we might
>>>think of and would therefore be much appropriate for your requirements
>too.
>>>If you ever switch to SQL Server, you'll most certainly benefit from a
>wide
>>>variety of features that would make your life easier. You would have Stored
>>>Procedures, Views and Triggers amongst others to help you efficiently
organise
>>>and process your data and bcp features (for fast bulk copying data to
and
>>>from text files) as well as basic backup tools for your archiving needs.
>>>I do certainly believe SQL Server is a valuable option for your business
>>>requirements for the features, reliability and performance you might obviously
>>>need. You can find more info in the Microsoft site at http://www.microsoft.com.
>>>I develop solutions using VB 5.0 / SQL Server 6.5 / MS Access 97 and if
>>you
>>>think there are areas in which i might help you, please do feel free to
>>mail
>>>me.
>>>
>>>Regards,
>>>
>>>David
>>>david@servihoo.com
>>>
>>>
>>>"DavidR" <david.c.ramsey@bankofamerica.com> wrote:
>>>>
>>>>Question from a SAS programmer:
>>>>
>>>>I don't have much experience with SQL Server, but have used the native
>>SQL
>>>>code to create datasets from DB2 tables.
>>>>
>>>>Here where I work, we use SAS primarily to do data processing - for example,
>>>>a database of customer records that consists of an updated record for
>each
>>>>customer at the end of each month - with the same variables (fields)
such
>>>>as the balance.
>>>>
>>>>We routinely take recordsets like this and transform them into one record
>>>>for each with a renamed field for each previous duplicate field - balance_03_2001
>>>>for example.
>>>>
>>>>We're trying to find alternatives to SAS. Is this something that SQL
>Server
>>>>can do efficiently? Are there better alternatives? What would the solution
>>>>to this problem look like?
>>>>
>>>>Thanks,
>>>>
>>>>David Ramsey
>>>
>>
>

[david]

David Macknet has so nicely summarised the power of SQL Server. So, welcome
to the SQL Server club David Ramsey. Seems Davids are all for SQL Server
8-)

"DavidR" <david.c.ramsey@bankofamerica.com> wrote:
>
>Thanks again! Since I have the Ferrari installation cd right here, it looks
>as though I will be driving much faster in days to come.
>
>
>
>
>
>"David Macknet" <david_t_macknet@agilent.com> wrote:
>>
>>David,
>>
>>Yes, SQL Server certainly can. SQL Server is not even something that you'd
>>typically compare with Access (although many ask the question). If you're
>>simply interested in sorting records and extracting results (with no updates
>>to the dataset), SQL Server will allow you to do build indexes on the data
>>"WITH FILLFACTOR 100" - meaning that all leaf nodes of the binary tree
(of
>>the index) are filled. This means that the index would be hideous if you
>>were updating data, but will perform quite well for looking things up.

>This's
>>just a small sample, but the power of the engine is tremendous. Even if
>>you weren't to get fancy with fillfactors and pinning tables in memory,
>you'll
>>be amazed at the sheer speed with which it operates. There's simply no
>comparison.
>>
>>To give you an example from an application I developed:
>>Every night a job runs to pull in over 5 gigabytes of historical data from
>>four different sources and summarize it in several ways. This summary
operation
>>takes a little over an hour. The users, with the end data, are able to
>extract
>>statistical reports (further aggregating/summarizing the data). The users'
>>reports take less than 45 seconds to run, even if they're aggregating over
>>3 million rows.
>>
>>It was my first SQL Server Application, so I'm sure that I could've done
>>better.
>>
>>Comparing Access to SQL Server is like comparing a Yugo to a Ferarri.
Once
>>you've seen the two....
>>
>>Good luck!
>>
>>-David
>>
>>"DavidR" <david.c.ramsey@bankofamerica.com> wrote:
>>>
>>>david,
>>>
>>>Thanks so much for the response - here's a scenario type question for
you:
>>>
>>>I currently have a database that has upwards of 7 million records. MS
>Access,
>>>as you can well imagine, is seriously slow in doing anything with this
>large
>>>amount of data. I use Access to link to some RDBMS tables on another
system,
>>>and after pulling in the data that I need, the Access file can exceed
800
>>>MB easily. Of course simply performing a sort on that amount of data
>can
>>>take 30 min. to an hour (typically).
>>>
>>>This is why most of the processing to rearrange the data is done remotely
>>>on a Unix based Solaris server using SAS (speed is the benefit.)
>>>
>>>Although it is really difficult to estimate, is there a big boost in efficiency/memory
>>>usage/speed if I were to switch the process from Access to SQL Server?
>>>
>>>
>>>
>>>
>>>Thanks again,
>>>
>>>David Ramsey
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>"david" <david@servihoo.com> wrote:
>>>>
>>>>Hi David,
>>>>
>>>>I don't know anything about SAS but if this might be of any help to you,
>>>>I would like to say that SQL Server, like any other Relational Database
>>>Management
>>>>System (RDBMS), suits most of the business information scenarios we might
>>>>think of and would therefore be much appropriate for your requirements
>>too.
>>>>If you ever switch to SQL Server, you'll most certainly benefit from
a
>>wide
>>>>variety of features that would make your life easier. You would have
Stored
>>>>Procedures, Views and Triggers amongst others to help you efficiently
>organise
>>>>and process your data and bcp features (for fast bulk copying data to
>and
>>>>from text files) as well as basic backup tools for your archiving needs.
>>>>I do certainly believe SQL Server is a valuable option for your business
>>>>requirements for the features, reliability and performance you might
obviously
>>>>need. You can find more info in the Microsoft site at http://www.microsoft.com.
>>>>I develop solutions using VB 5.0 / SQL Server 6.5 / MS Access 97 and
if
>>>you
>>>>think there are areas in which i might help you, please do feel free
to
>>>mail
>>>>me.
>>>>
>>>>Regards,
>>>>
>>>>David
>>>>david@servihoo.com
>>>>
>>>>
>>>>"DavidR" <david.c.ramsey@bankofamerica.com> wrote:
>>>>>
>>>>>Question from a SAS programmer:
>>>>>
>>>>>I don't have much experience with SQL Server, but have used the native
>>>SQL
>>>>>code to create datasets from DB2 tables.
>>>>>
>>>>>Here where I work, we use SAS primarily to do data processing - for
example,
>>>>>a database of customer records that consists of an updated record for
>>each
>>>>>customer at the end of each month - with the same variables (fields)
>such
>>>>>as the balance.
>>>>>
>>>>>We routinely take recordsets like this and transform them into one record
>>>>>for each with a renamed field for each previous duplicate field - balance_03_2001
>>>>>for example.
>>>>>
>>>>>We're trying to find alternatives to SAS. Is this something that SQL
>>Server
>>>>>can do efficiently? Are there better alternatives? What would the
solution
>>>>>to this problem look like?
>>>>>
>>>>>Thanks,
>>>>>
>>>>>David Ramsey
>>>>
>>>
>>
>