Re: T-SQL is not quite there yet
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Sat, 14 Nov 2009 22:30:01 +0000 (UTC)
DWalker07 (none@xxxxxxxx) writes:
1) Calls to stored procedures can't be nested. That is, one stored
procedure can't do an Insert Into <Table> Exec <StoredProc1> if
StoredProc1 does the same thing to insert data from StoredProc2. Why
not?
My assumption is that they piggyback on the output buffer, and it is
a trick that can only be performed once.
In any case, see http://www.sommarskog.se/share_data.html for alternatives.
In this article I also discuss other problems with INSERT-EXEC and
shows that this is not a very good construct, with or without this
restriction.
3) It's hard to create indexes on table variables. It can be done by
creating a Unique constraint or a Unique Clustered constraint on one set
of columns, but sometimes this is not what we want. I often want non-
unique indexes, and I prefer to create one index per column.
Keep in mind that table variables are declared entities and such
they are static. It makes perfect sense that you can add indexes on
them later. Possibly the syntax could permit you declare non-unique
indexes on it. Personally, I've never seen this much of an issue. Since
a non-clustered index includes the clustered index key this means that
adding the primary key to an extra index has no cost, as long as the PK
is clustered.
4) In spite of what I remember Microsoft saying way back when -- that
they would consider it a bug if table variables perform worse than
temporary tables -- I have found many situations where table variables do
perform slower.
Certainly. Inserting into table variable precludes parallelism. And
since table variales do not have statistics, the optimizer has
less information than with a temp table which has statistics. Which means
that you get better chances for better query performance with temp tables.
(But with temp tables you can lose on recompiles instead.)
5) "Remote table-valued function calls are not allowed". Why not? Just
to frustrate us?
Overall, there are a lot of restrictions with linked servers. For instance,
neither you cannot pass XML over linked servers.
I assume a lot of this has to do with that the linked server may not
support these features. Keep in mind that from SQL Server's perspective,
the linked server may be Oracle, MySQL or a home-grown data source.
In fact, I don't think there is any support in OLE DB at all for table-
valued functions, and OLE DB is what SQL Server uses the access the linked
server.
Now, since surely over 90% of all linked server in this world, is another
SQL Server instance, one could surely wish that SQL Server had special
support for this.
I realize that T-SQL is not yet a first class language, but nit-picky
things like "Can't nest Insert Into calls on stored procedures" and
"can't create temp tables in user-defined functions" and "Remote table-
valued function calls are not allowed" all conspire to make using SQL
that much harder.
Again, I like to stress that the issue on remote table-valued function
is not a language issue. There is simply no infrastructure the language
can use.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.
- Follow-Ups:
- Re: T-SQL is not quite there yet
- From: DWalker07
- Re: T-SQL is not quite there yet
- References:
- T-SQL is not quite there yet
- From: DWalker07
- T-SQL is not quite there yet
- Prev by Date: Re: Strange perfomance on count witn group by
- Next by Date: Re: SQL Subquery/Temp Value
- Previous by thread: Re: T-SQL is not quite there yet
- Next by thread: Re: T-SQL is not quite there yet
- Index(es):
Relevant Pages
|
