Upgrading from SQL Aerver 2K to 2K5 need opinions 3

[Tomadams]

Hi,

I am upgrading to SQL 2k5. Mainly because I want to get away from T-SQL for stored procedures. I have used a lot of VBA (mostly DAO). What do you think of using VB.net for developing stored procedures. Is it a big change from VBA. Will I need visual Studio? All thoughts appreciated.
Thanks,
Tom

 

[SQLSister]

VB.net is very different from VBA.

Also, if a stored proc can be done in T-SQL it is probably best to continue to do so. If you thoroughly learn T_SQL it is usually the most efficient way to access data in a SQL Server db. If you have been working in VBA, you are probally used to doing things with recordsets and looping through one record at a time. With SQL Server this is a very bad practice. It is far better to use set-based T_SQL instead of looping or cursors.

"NOTHING is more important in a database than integrity." ESquared

 

[SQLDenis]

>>Mainly because I want to get away from T-SQL for stored procedures

what do you mean, SQL 2005 still uses stored procs, unless you need some specialized code which runs better in .NET I wouldn't use SQLCLR

Some shops might not even enable them for security reasons

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[mrdenny]

You will still need to use the T/SQL for your stored procedures. For most operations T/SQL stored procedures will be faster to execute than a CLR procedure. Also T/SQL procedures can modify data in bulk, where CLR procedures will usually handle data row by row, unless they log back into the SQL Server from the CLR procedure and run a T/SQL statement in which case why not simply use T/SQL to begin with.

SQL CLR procedures were not designed to be a replacement for T/SQL procedures. They are there as a supplement to the existing T/SQL language so that you can do advanced math, and string parsing functions that T/SQL can't do well (or at all).

The basic rule of thumb with regard to SQL CLR is if it can be done in T/SQL it should be. If it can't be done in T/SQL and it must be done at the database level then use a SQL CLR. If it can't be done in T/SQL and it can be done at another level of the app (application, middle tier, etc) to it there and pass the results back to the SQL Server.

Check out my blog tomorrow (see link below), there is actually going to be a nice little posting about SQL CLR coming out in the morning. It'll get published at midnight PST tonight.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[Tomadams]

Thanks all. My application is quite complex. It is quality analysis with lots of statistical analysis formulas and interactions between numerous data elements to develope probability and risk assesments. I guess I will have to work on my T-SQL coding. Can you recommend a good manual that has T-SQL examples.

 

[Tomadams]

Mr. Denny - Your blog is very helpful for me. I think now that I will need to use both tsql and .net. I need to spend sometime studying this thing out.
Appreciate your help

 

[mrdenny]

Denis is the local book recommender around here. I haven't read a book that I've liked all that much yet (granted I don't have much time to read either).

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog