SQL 2005 - Is it possible to write stored procedures without knowing T-SQL?
As a result of Microsoft's ambitious marketing of SQL Server 2005's .NET integration over the last few years, many developers believe that T-SQL will no longer be necessary to create SQL Server stored procedures. Unfortunately (or not, depending on your point of view), this is only partially true. While it is technically possible to create a stored procedure without using T-SQL, it is not possible to do any data access without T-SQL.
Data access within CLR stored procedures is done using the standard ADO.NET classes. Developers will find that much of the same data access code usable in application tiers will be easily portable into SQLCLR routines. As these ADO.NET classes in the middle tier require T-SQL for data access, so do the same classes used within the context of the hosted CLR provider.
I noted that it is technically possible to write a T-SQL-less stored procedure. So is there any reason to do so? One case for this is a CLR stored procedure written to retrieve data from a flat file or Web service and format it into a rowset. That would be an operation that would not require T-SQL – but it's not a good comparison with the abilities of T-SQL stored procedures.
SQL 2005 - How do CLR stored procedures and functions perform compared to those in T-SQL?
Here is the general performance rule when comparing T-SQL routines to equivalent CLR routines: Test both configurations with your data on your servers and figure out which one is better.
That said, many people have run performance tests and the general consensus is that T-SQL will always perform better for standard CRUD (Create, Read, Update, Delete) operations, whereas CLR code will perform better for complex math, string manipulation and other tasks that go beyond data access.
How can I use results from one stored procedure in another stored procedure?
So long as the stored procedure produces only a single result, the technique for using the output of one stored procedure in another is pretty straightforward. The technique is to use a temporary table to hold the results of the stored procedure and an INSERT EXEC statement to execute the sproc and save the results. Once the results are in the temporary table they can be used like any other table data.
Here's an example procedure that we might like to reuse:
CREATE PROC usp_Demo_AllAuthors as
select * from pubs..authors
GO
Now here's a stored procedure that uses the results of usp_Demo_AllAuthors:
CREATE proc usp_Demo_SPUser as
CREATE TABLE #Authors (
au_id varchar(11) NOT NULL PRIMARY KEY CLUSTERED,
au_lname varchar (40) NOT NULL ,
au_fname varchar (20) NOT NULL ,
phone char (12) NOT NULL,
address varchar (40) NULL ,
city varchar (20) NULL ,
state char (2) NULL ,
zip char (5) NULL ,
contract bit NOT NULL
)
-- Execute usp_Demo_AllAuthors storing the
-- results in #Authors
insert into #Authors
exec usp_Demo_AllAuthors
-- Here we use the #Authors table. This example only
-- only selects from the temp table but you could do much
-- more such as use a cursor on the table or join with
-- other data.
SELECT au_fName + ' ' + au_lname as [name]
, address+', '+city+', '+state+' '+zip [Addr]
from #Authors
DROP TABLE #Authors
GO
Could you provide some detailed information about SQL vs. T-SQL?
SQL is the Structured Query Language, the ANSI/ISO Standard database language. SQL Server's implementation of the language is called Transact-SQL (T-SQL). T-SQL is based primarily on the version of the ISO standard released in 1992, with bits added from the 1999 standard. In addition, various proprietary enhancements have been made by Microsoft.
There are a number of differences between Standard SQL and T-SQL -- too many to name here. And, yes, there are advantages to using the proprietary extensions if you are working with SQL Server. Due to the nature of many of SQL Server's features, you simply cannot harness its full power without using some nonstandard commands. If you'd like to find out whether your SQL conforms to the standard, you can use the SET FIPS_FLAGGER command.
Does SQL Server 2005 have any new index types?
SQL Server 2005 does not introduce new index types for relational tables. The basic -- clustered and non-clustered indexes implemented as B-trees -- still apply. However, SQL Server 2005 does include indexing enhancements both for Full Text Indexing and for XML data, as well as enhancements that ease some problems associated with relational indexing.
SQL Server 2005's Full Text Indexing feature is completely new and rewritten. For information on this feature, watch Nimish Khanolkar's archived MSDN webcast, Introducing Full-Text Search in SQL Server 2005.
XML has also undergone a drastic transformation in the way it is treated in SQL Server 2005. There is now a first-class XML data type available to developers. The type supports the XQuery query language, and columns using the type can be indexed using a special form of XML indexes. More information on the XML type can be found in this MSDN article.
A variety of enhancements have been made to the T-SQL relational indexing commands. Perhaps the most interesting of these is the new "online" indexing mode, which allows database administrators to perform index maintenance tasks without locking users out of tables. This hopefully marks the beginning of the end of database administrators having to wait for the 3:00 a.m. maintenance window to fix database issues!
How can I list out database tables which have no records?
You can make your solution more flexible by grabbing the table names from the sysobjects table:
declare @strsql varchar(256)
create table #emptytables (tablename varchar(128), table_rowcount int)
select @strsql='select distinct o.name as TableName, x.rowcnt as Table_RowCount
from sysobjects o
inner join sysindexes x
on o.id = x.id
where x.rowcnt = 0 and
o.type = ''U'''
insert #emptytables (TableName, Table_rowcount) exec (@strsql)
select * from #emptytables
drop table #emptytables
|
