CLR vs T-SQL

Hi,

I bought Pro ADO.NET2.0 but could not find a clear description of the optimum use-case for CLR and T-SQL Stored Procedures in general. (although the book is great)

It seems for complex or recursive tasks CLR SP is useful. But would you recommend using CLR SP for basic INSERT, UPDATE, DELETE, SELECT queries?

Thanks,
Bahadir

...very common question these days.

Simple answer lies in the fact that let the each person(in this case CLR/T-SQL engines) do what it knows best!

Say for e.g. if you have lot many string manipulation activities to be accomplished. Now of course, in such a case writing a T-SQL UDF or a SP won't help much in performance gains. On all grounds for such activities, the CLR would be much stronger than the T-SQL engine.

Another example -- consider a scenario where a T-SQL cursor is pretty much evident in your SP logic. Try considering writing a CLR SP instead -- looping etc would be very much optimized when in use with CLR rather than T-SQL.

Does than mean T-SQL is always the 2nd choice ? NO Not at all !!

Please recall that T-SQL engine is primarily a SET basaed operational engine. Therefore as mentioened above, CLR was the best choice for row based operations, while consider fetching multiple rows in a set based patter using various joins or order by ann group by options -- of course, T-SQL is the best choice. Similarly, update of columns hitting several rows -- T-SQL better than CLR SP.

I hope I am able to answer your doubt.

Regd. the books you were ref. to -- try reading Pro SQL Server 2005 (A Press)

and SQL Server Magazine (www.sqlmag.com)

cheers,

Ujwal

|||Thanks, this was the kind of rule-of-thumb answer I was expecting.

Bahadir
|||

well, I don't know how much are you convinced with the answer or do you still have doubts.

anycase, I would like to pass on a pointer to one more thread which I happened to come across very much on similar topic on this very forum try browsing the same as well --Where to use SQLCLR ? Data access is not recommended?

|||

hi bahadir,

i think i discussed those thing in my blogs

kindly check

look for the SQL CLR topic

regards,

joey

|||

Hi Bhadir,

You get some insight from the whitepaper that we published on CLR integrationyou can find it here.

The basic guidelines are that for procedural, computational work, the CLR will outperform T-SQL. For data access, or things that can be written declaratively, or that perform data acces, T-SQL will win. Obviously there's a lot of grey area in this answer, but there's a lot of grey area in practice as well.

Cheers,

-Isaac

|||

Hi there,

check this post http://www.yukonizer.com/dasblogce/PermaLink,guid,59a1fbee-1df3-4f9c-be64-de1c778c198e.aspx

Might help you.

Cheers, -DS

CLR vs T-SQL

Hi,

I bought Pro ADO.NET2.0 but could not find a clear description of the optimum use-case for CLR and T-SQL Stored Procedures in general. (although the book is great)

It seems for complex or recursive tasks CLR SP is useful. But would you recommend using CLR SP for basic INSERT, UPDATE, DELETE, SELECT queries?

Thanks,
Bahadir

...very common question these days.

Simple answer lies in the fact that let the each person(in this case CLR/T-SQL engines) do what it knows best!

Say for e.g. if you have lot many string manipulation activities to be accomplished. Now of course, in such a case writing a T-SQL UDF or a SP won't help much in performance gains. On all grounds for such activities, the CLR would be much stronger than the T-SQL engine.

Another example -- consider a scenario where a T-SQL cursor is pretty much evident in your SP logic. Try considering writing a CLR SP instead -- looping etc would be very much optimized when in use with CLR rather than T-SQL.

Does than mean T-SQL is always the 2nd choice ? NO Not at all !!

Please recall that T-SQL engine is primarily a SET basaed operational engine. Therefore as mentioened above, CLR was the best choice for row based operations, while consider fetching multiple rows in a set based patter using various joins or order by ann group by options -- of course, T-SQL is the best choice. Similarly, update of columns hitting several rows -- T-SQL better than CLR SP.

I hope I am able to answer your doubt.

Regd. the books you were ref. to -- try reading Pro SQL Server 2005 (A Press)

and SQL Server Magazine (www.sqlmag.com)

cheers,

Ujwal

|||Thanks, this was the kind of rule-of-thumb answer I was expecting.

Bahadir|||

well, I don't know how much are you convinced with the answer or do you still have doubts.

anycase, I would like to pass on a pointer to one more thread which I happened to come across very much on similar topic on this very forum try browsing the same as well -- Where to use SQLCLR ? Data access is not recommended?

|||

hi bahadir,

i think i discussed those thing in my blogs

kindly check

look for the SQL CLR topic

regards,

joey

|||

Hi Bhadir,

You get some insight from the whitepaper that we published on CLR integrationyou can find it here.

The basic guidelines are that for procedural, computational work, the CLR will outperform T-SQL. For data access, or things that can be written declaratively, or that perform data acces, T-SQL will win. Obviously there's a lot of grey area in this answer, but there's a lot of grey area in practice as well.

Cheers,

-Isaac

|||

Hi there,

check this post http://www.yukonizer.com/dasblogce/PermaLink,guid,59a1fbee-1df3-4f9c-be64-de1c778c198e.aspx

Might help you.

Cheers, -DS

CLR vs T-SQL

Hi,

I bought Pro ADO.NET2.0 but could not find a clear description of the optimum use-case for CLR and T-SQL Stored Procedures in general. (although the book is great)

It seems for complex or recursive tasks CLR SP is useful. But would you recommend using CLR SP for basic INSERT, UPDATE, DELETE, SELECT queries?

Thanks,
Bahadir

...very common question these days.

Simple answer lies in the fact that let the each person(in this case CLR/T-SQL engines) do what it knows best!

Say for e.g. if you have lot many string manipulation activities to be accomplished. Now of course, in such a case writing a T-SQL UDF or a SP won't help much in performance gains. On all grounds for such activities, the CLR would be much stronger than the T-SQL engine.

Another example -- consider a scenario where a T-SQL cursor is pretty much evident in your SP logic. Try considering writing a CLR SP instead -- looping etc would be very much optimized when in use with CLR rather than T-SQL.

Does than mean T-SQL is always the 2nd choice ? NO Not at all !!

Please recall that T-SQL engine is primarily a SET basaed operational engine. Therefore as mentioened above, CLR was the best choice for row based operations, while consider fetching multiple rows in a set based patter using various joins or order by ann group by options -- of course, T-SQL is the best choice. Similarly, update of columns hitting several rows -- T-SQL better than CLR SP.

I hope I am able to answer your doubt.

Regd. the books you were ref. to -- try reading Pro SQL Server 2005 (A Press)

and SQL Server Magazine (www.sqlmag.com)

cheers,

Ujwal

|||Thanks, this was the kind of rule-of-thumb answer I was expecting.

Bahadir|||

well, I don't know how much are you convinced with the answer or do you still have doubts.

anycase, I would like to pass on a pointer to one more thread which I happened to come across very much on similar topic on this very forum try browsing the same as well -- Where to use SQLCLR ? Data access is not recommended?

|||

hi bahadir,

i think i discussed those thing in my blogs

kindly check

look for the SQL CLR topic

regards,

joey

|||

Hi Bhadir,

You get some insight from the whitepaper that we published on CLR integrationyou can find it here.

The basic guidelines are that for procedural, computational work, the CLR will outperform T-SQL. For data access, or things that can be written declaratively, or that perform data acces, T-SQL will win. Obviously there's a lot of grey area in this answer, but there's a lot of grey area in practice as well.

Cheers,

-Isaac

|||

Hi there,

check this post http://www.yukonizer.com/dasblogce/PermaLink,guid,59a1fbee-1df3-4f9c-be64-de1c778c198e.aspx

Might help you.

Cheers, -DS

Close to quit working with VS 2005 and SQL Express (Cannot Create Database)

About 2 months ago, with great interest installed VS 2005 beta on my local Windows XP Pro. The SQL Express is running.

I cannot work FULLY on starter kits like Personal Web Site SK or Community SK. I create a new web site based on the Community SK. I can successfully bring up ASP.NET configuration, create roles, create users etc. When I look in the App_Data folder there is no ASPNET.MDF database. Where is this getting created?

2) Do we have permissions to create a SQL Database from VS 2005 Beta? I assume not, because at MSDN, there are generic SQL scripts for adding tables to Personal Web site SK and Community SK. For example, a start a new web ASP.NET web site. When I go to add new item, choose SQL Database, I get this error (see below). I am looking for documentation of SQL Express, I don't find any.

With very little time available after work, wife and kids, I am spending tons of hours looking to solve these issues (User Instance, Password Quality and this Access Denied) looking at MSDN, Forums etc. I agree that VS 2005 is a great product and there are great tutorials for VS 2005 both at Microsoft and other places (www.learningvisualstudio.net)

But if I cannot create SQL Databases, from VS 2005 interface, what is the use of going ahead and trying to learn these tools?

If somebody has specific answers or links that can resolve the above issues, please post here. If you have similar difficulties and how you overcame them, post them here. Thanks.
================================

Directory lookup for the file D:\Documents and Settings\<local computer name>\my documents\visual studio 2005\WebSites\Lesson07\App_Data\Customers.mdf” failed with the operating system error 5 (Access Denied).

CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

User does not have permission to alter database ‘4BE36399-4C49-4103-963D-7F34626E3914’ or the database does not exist.

ALTER DATABASE statement failed.

User does not have permission to alter database ‘4BE36399-4C49-4103-963D-7F34626E3914’ or the database does not exist.

ALTER DATABASE ‘4BE36399-4C49-4103-963D-7F34626E3914’ does not exist. Use sp_helpdb to show available databases.

=================================

There are two ways you can create databases. As you note, the MDF should be in the data folder. However, if you are running SSE with a UserInstance token in the connection string, then you are running SSE under your own account and you are full-on admin and can do anything you want. In this scenario, you can be logged on as admin or a normal user.

If you are connecting to SSE in the main instance, and you are running as a normal user, you might run into the situation you're describing.

If you get a copy of SSEUtil from the web (search for it), you will find it's a great way to figure out what's going on. It's like SQLCmd, but a lot friendlier. And, it works witih both SSE in the main instance and when it runs under your user account as a normal user.

In order, the things to check are:

1.) is SSE running?
2.) are you working in USERInstance mode or normal?
3.) Can you use SSEUtil to connect to and create databases.

Client can not connect to SQL Express - The Solution

Hi,

When I try to connect to SQL Express from another computer I get error 26.

The setup is as follows:

Server is Windows XP Pro, Firewall turned off.

Client is Windows XP Pro, Firewall turned off.

Both computers are on the same workgroup, no domain or other servers on the network.

The server is configured for network: protocols enabled, browser running.

The client has MS SQL Server Management Studio Express installed. When I try to connect the SSMSE finds the SQL Express on the server (using the Browse for more – Network server option), but the connection fails with error 26.

When I try to connect from MS Access 2003 I get the same error.

Thank for any help

Amihy

I found this link http://www.datamasker.com/SSE2005_NetworkCfg.htm maybe this will help you out.|||

Thanks for the link, it helped me understand that all my SQL settings are OK.

So I started to think about the network and at last found that NetBios over TCP\IT was disabled. It did not bother me before since the local network works with NetBEUI protocol.

Once I enabled TCP\IP over NetBios in Local Area Connection-Properties-TCP\IP properties-General-advanced-wins, The client immediatly connected to the server.

|||good to hear your ok now

Client can not connect to SQL Express

Hi,

When I try to connect to SQL Express from another computer I get error 26.

The setup is as follows:

Server is Windows XP Pro, Firewall turned off.

Client is Windows XP Pro, Firewall turned off.

Both computers are on the same workgroup, no domain or other servers on the network.

The server is configured for network: protocols enabled, browser running.

The client has MS SQL Server Management Studio Express installed. When I try to connect the SSMSE finds the SQL Express on the server (using the Browse for more – Network server option), but the connection fails with error 26.

When I try to connect from MS Access 2003 I get the same error.

Thank for any help

Amihy

I found this link http://www.datamasker.com/SSE2005_NetworkCfg.htm maybe this will help you out.|||

Thanks for the link, it helped me understand that all my SQL settings are OK.

So I started to think about the network and at last found that NetBios over TCP\IT was disabled. It did not bother me before since the local network works with NetBEUI protocol.

Once I enabled TCP\IP over NetBios in Local Area Connection-Properties-TCP\IP properties-General-advanced-wins, The client immediatly connected to the server.

|||good to hear your ok now

Client access to sql database

sql 2000 on 2003 server standard, only 2 out of 10 users (xp pro clients)
trying to access sql program are asked to login to database, server name,
database, login id, password to TableName: datatable.
Is this an xp client issue or sql issue.
Can you give more details? How exactly are they logging in? Are you using
Windows authentication or SQL? Windows won't prompt you to login to SQL
Server since you already logged in to the domain.
Andrew J. Kelly SQL MVP
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:FB02C0E3-2E67-4979-B809-C49BE0496BCA@.microsoft.com...
> sql 2000 on 2003 server standard, only 2 out of 10 users (xp pro clients)
> trying to access sql program are asked to login to database, server name,
> database, login id, password to TableName: datatable.
> Is this an xp client issue or sql issue.
|||Users logon to the domain, and from a shared drive the clients run an msi
file that installs a program on their local pc's, this uses a sql database.
They start the program and when they try to run a report they get a pop up
box asking for login info for "TableName datatable". I think it is Windows
Authentication, don't know how to check which one is being used.
Trying to determine if an xp client issue or sql issue.
"Andrew J. Kelly" wrote:

> Can you give more details? How exactly are they logging in? Are you using
> Windows authentication or SQL? Windows won't prompt you to login to SQL
> Server since you already logged in to the domain.
> --
> Andrew J. Kelly SQL MVP
>
> "Billy" <Billy@.discussions.microsoft.com> wrote in message
> news:FB02C0E3-2E67-4979-B809-C49BE0496BCA@.microsoft.com...
>
>
|||If it is windows authentication it should not need to ask. You really need
to see what the application is using for a connection string.
Andrew J. Kelly SQL MVP
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:BEF206D6-AA2F-4949-9D5C-95B13AE16D42@.microsoft.com...[vbcol=seagreen]
> Users logon to the domain, and from a shared drive the clients run an msi
> file that installs a program on their local pc's, this uses a sql
> database.
> They start the program and when they try to run a report they get a pop up
> box asking for login info for "TableName datatable". I think it is Windows
> Authentication, don't know how to check which one is being used.
> Trying to determine if an xp client issue or sql issue.
> "Andrew J. Kelly" wrote:
|||Unfortunately I'm not a sql guru, just a network admin. This program that
points to the database was created by an outside company. Of course they say
that because only 2 of clients have the login issue it must be an xp client
issue and has nothing to do with the program. But the users are already in
the program, it is only when they try to run a report from within the program
that the login box appears?
Got any opinions one way or another?
"Andrew J. Kelly" wrote:

> If it is windows authentication it should not need to ask. You really need
> to see what the application is using for a connection string.
> --
> Andrew J. Kelly SQL MVP
>
> "Billy" <Billy@.discussions.microsoft.com> wrote in message
> news:BEF206D6-AA2F-4949-9D5C-95B13AE16D42@.microsoft.com...
>
>
|||What ever it is that is attempting to run the report must be making a new
connection. But without knowing what or how it is attempting to do this it
is anyone's guess. The vendor must have a clue as to how they connect and
why this is happening. After all they wrote it<g>. You can try using
profiler to see what they are sending to sql server at the time it happens.
Other than that I don't have a clue as to what the app is really doing.
Andrew J. Kelly SQL MVP
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:A498DAFD-838E-466C-A03F-FC3102784162@.microsoft.com...[vbcol=seagreen]
> Unfortunately I'm not a sql guru, just a network admin. This program that
> points to the database was created by an outside company. Of course they
> say
> that because only 2 of clients have the login issue it must be an xp
> client
> issue and has nothing to do with the program. But the users are already in
> the program, it is only when they try to run a report from within the
> program
> that the login box appears?
> Got any opinions one way or another?
> "Andrew J. Kelly" wrote: