January 13, 2006 at 3:08 pm
The ultimate weapon against developers who say they want to re-write all of the stored procedures in CLR languages:
"Sounds like a great idea to me! Let me know how it works out."
-Guaranteed- after less than two days work the developer will give up on that idea once he or she understands what CLR integration truly brings to the table--and doesn't bring to the table.
--
Adam Machanic
whoisactive
January 14, 2006 at 2:05 am
I am quite new to SQL and Visual Studio 2005. For the past few monhts I am working on Sql Server 2005 and working on to develop ERP software for Textile Industy.
I find T-SQL very convenient to enforce Business Logic. Data is stored in number of tables and each table has a small trigger to check and enforce business intelligence. So the whole process become very moduler, independent of one another.
Visal Studio is used to design windows form and Crystal Report for taking out simple report.
Out Clients are looking for software where developmental time will be small even if it is not tested 100%. People who are currently working SQL Server 200 and VB6 are approaching us for faster implementation of business logic and getting some quick result
V.Kadal Amutham
January 14, 2006 at 2:24 am
Adam, I agree fully that 'true' business logic as you define it has only one place and that is in the database. However, I also believe that the logic should be declaratively enforced (to 100%). Stored procedures, whether written in T-SQL or a .NET-language, are just not part of the database. They are a part of an application that use the database, a 'middle' tier that is placed inside the database server.
On a higher level I have many issues with the tight coupling between databases and stored procedures (again, no difference between T-SQL and CLR), but I think that like you I am very interested in the integration of CLR in SQL Server and the new possibilities it brings to implementing database routines. It is a new tool, but like all tools it has it's uses and there are things it should not be used for.
January 14, 2006 at 7:41 am
Stored procedures, sure. But you're forgetting about functions and--even more importantly--user-defined types.
Example: Assume you're coding an application that needs to store URLs in a few different tables. Can you write a T-SQL UDDT that can self-verify the format of the URLs in every place it's used? I can write a CLR function that does so. This means that my type is actually more than just an aliased T-SQL type--it enforces its domain, without need for additional constraints.
--
Adam Machanic
whoisactive
January 14, 2006 at 10:59 am
A few of my thoughts for what they are worth.
Personally my opinion has always been that a database is for the data and thats about it. Obviously some business logic does creep in there to maintain data integrity but I do try to keep it pure.
Also I do find that when a business process can hit the database server a number of times it can be better to roll some of this functionlity into a stored procedure and hit the database once.
In a load balanced environment where there are multiple web servers and one DB server the database can often experience considerable load and yet the web farm is working well within limits. It's for this reason that I would be against putting to much logic into the database. It's much cheaper and less hassle for me to add webservers (or aditional application server) to handle the business side where as upgrading the database can often be more complex and costly.
Having said that the integration of the CLR is a welcomed addition as it solves some problems that I have previously 'fudged' in the business layer to get the data in form that I want. e.g. statistical calculations and string agregation
January 15, 2006 at 11:41 am
Don't get me wrong, I am very positive about CLR integration, and functions in particular is where I see myself using it the most. In the URL example I would probably use a function in a check constraint to enforce the logic.
That said though, I agree that a better domain support would be great, and I might use UDTs to give me a little of it in this way. With UDTs however I am afraid that people will start adding externally visible functionality, e.g. not methods for internally enforcing the domain. Manipulating data in a database should not require any other operations than the normal DML.
January 15, 2006 at 11:43 am
So you're suggesting that the '+' operator be removed from SQL?
--
Adam Machanic
whoisactive
January 15, 2006 at 11:57 pm
Naturally, no. What I am saying is that I do not like adding more operations that are only used for specific types for manipulating data, when the existing set of operations are fully enough of handling it.
January 16, 2006 at 12:07 am
Really? What if I define a point (latitude/longitude) datatype? Does SQL Server have an operator that will let me move my point 10 miles east? Why don't you like type-specific operators? Remember that a type is not defined only by its domain, but also by its operators--without operators, there really is no reason to define a type at all. Why define something that you can't work with?
--
Adam Machanic
whoisactive
January 16, 2006 at 10:42 am
I am just not so sure that the DBMS should include all the behavior of a type. A mutator operator such as the one you described might be necessary to move a point, but I am not sure that the DBMS needs to be able to do it. If the DBMS is able to represent the data (and validate it) then it might be enough to let the users move the points around. But then I would probably store the point as the floats for the latitude and longitude. But you are right that the possibility to enforce business rules fully is quite appealing.
Anyway, what I wanted to say with my first post was that I do not think the fear shown by DBAs of 'bringing in the middle-tier into the database' is fully justified. With stored procedures and other constructs we have had (parts of) the middle-tier inside the database for a long time. However, on the other hand that does not mean that we should now implement everything in an application inside the database. For instance, just because we can have the database download files it does not mean that it is always the correct thing to do.
January 16, 2006 at 1:05 pm
Many people's worlds are shaped and limited by the execution environments and languages they know and work in.
Whether it's desktop app, web app, database, OO language, set language, matrix language... these things each have their power and awful weaknesses. They are both the way to progress and the biggest barrier to it.
Anything that provides useful additional power and breaks the mental barriers and mental poisoning is likely a step forward.
To me the CLR in the dB server is one of those beneficial ideas. It doesn't add much for set handling but it does a lot else that will be useful.
The point about operators is a good one, in my view. OO languages do just about everything with methods. Why? They are not designed for building decent operators easily. To appreciate what can be achieved look at a matrix language like APL or J. These things are a true joy to work with. Ties in very nicely with sets/dB but most programmers/dBA's are unaware that this power is already to hand.
January 25, 2008 at 12:32 am
I know this is a very trivial point, but you do this so often that it's really setting my teeth on edge !
"Since it's release" should be "Since its release". The apostrophe in "it's" is reserved exclusively for "It is", and NOT the possessive!
January 25, 2008 at 1:53 am
What is the best way to learn to develop CLRs and learn first hand what they are useful for and when they are useful?
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
January 25, 2008 at 3:58 am
xp_cmdshell is a big reason for enabling SQL CLR.
In the company I work for we have started replacing functionality using xp_cmdshell with calls to specialized stored procedures and UDFs. So something like:
xp_cmdshell 'dir ...'
is replaced by a call to a CLR sproc that returns a result set with the information. And so on for other manipulations in the file system.
You talked about security nightmares. Well, there is one right there. Through xp_cmdshell a developer has complete control over file system operations, including file deletions, additions etc. Aside of malicious intent, human error can end up causing havoc in the file system (replace 'dir' above with 'del'). Replacing these kinds of very non-specific calls to xp_cmdshell with calls to highly specialized SQLCLR objects that each do a specific task (file deletion etc.) gives the DBA some of the control back. One final example on this. If anyone has ever tried using xp_cmdshell to determine the free space of a drive programmatically will know how tedious that is. With the right SQLCLR object it takes 3 lines of code!
We are entering an era where DBAs will have to start thinking of themselves as IT Professionals, not just DBAs. If we don't feel comfortable with a certain technology, all the more reason to roll up our sleeves and learn it. I would go one step further and say it is our duty as DBAs to point the right path to developers for all things pertaining to SQL Server, including the CLR. Eventually the responsibility for a smooth-running database system rests with us.
One final point about performance. I don't think anyone objects to the fact that T-SQL is inadequate in certain situations. Operations, such as string manipulation and complex mathematical calculations are not what T-SQL was made for, and it is not always possible to split these components off to a separate business tier.
The challenge for DBAs and developers is to exercise judgement on the most judicious use of SQLCLR. It's an exciting challenge that we all need to embrace moving forward.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
January 25, 2008 at 6:14 am
My stance on SQLCLR is that it's just version 1.0. Its not too useful in set manipulation, but adds very convenient ways to fix a few irritating shortcommings in sqlserver 2005. i.e: local time to UTC time conversion, ISO8601 date management (which by the way is erroneous in the CLR also), hex numbers, web service consumption etc. These things takes a few lines of code when you have the CLR available, but is quirky and tedious in t-sql.
For set manipulation and production, SQLCLR is far to immature. For instance, there is no quick way to make a SqlDataReader from a DataTable in SQLCLR, whats up with that? Maybe a hint of LINQ in SQLCLR will do wonders?
But these are all lackings that ms should have fixed a long time anyway. And a major overhaul of T-SQL is long overdue. And I wish MS focused more on that:
* Variable definitions. T-SQL need %TYPE and %ROWTYPE like in oracle, to simplify maintenance, development and perhaps make cursors useful in t-sql.
* Implement ANSI SQL2003 analytical functions and windowing funcitions properly. RANK and ROW_NUMBER doesnt cut it. This can simplify queryies with complex predicates a lot!!!
* Better namespacing and packaging of procedures, types, functions etc. Today we need to over-use schemas or encforce strict naming policies to avoid obscurity. Perhaps packages like in Oracle isn't a bad idea?
* Functions for extracting complete DDL of objects. Its just silly that everyone has to make their own.
* Make it possible to control user defined type's presentation when consumed by ODBC-clients.
* Select * into @myTableVariable from MyTable.
* Convert(DateTime,@Timstamp) and Convert(TimeStamp, @myDate)
Viewing 15 posts - 31 through 45 (of 64 total)
You must be logged in to reply to this topic. Login to reply