Then that book is great. It will teach you all you need to know (from a developers point of view) about SQL Server. It covers SQL Server stored procedure programming, security systems, query optimization, indexing internals, best practices and so on. It provides you with the knowledge you need to make educated decisions, but it is by NO MEANS a learn by example kinda of book. Morty ;)

Well, this depends on your data. Speaking from a purely SQL Server perspective the basic SELECT is the fastest, but if the DISTINCT clause eliminates a large amount of records it might be faster when taking the middle tier, network access, aso (basically the rest of your processing...) into consideration. The thing to remember is that a DISTINCT is post processed. So the SQL Server actually makes a complete SELECT first, and then filters the records. Morty

This does depend a bit upon what you want to learn, but the best "SQL Server Book" IMHO is Inside SQL Server 2000 (Delaney). The book discusses SQL Server in great detail, but it does not offer any information regarding ADO / ADO.NET er other middle layer / access technologies. Morty

Just as a note, there are _a few_ managed providers (ie like System.Data.SqlClient) for MySql that should work well, and probably a lot faster than an ODBC approach. I don't remember any links, but search sourceforge.net (or google) or something :) Morty

What you would like to do is the basically pass a recordset to a stored procedure in SQL Server 2000. (?) The only way I know of to do this is to create an Xml file, pass it as a text (ntext in .Net :) ) parameter to the stored procedure. And then use the OPENXML SQL statement, which enables you to treat an XML file as a table (check OPENXML in Books Online). You can then easily use something like INSERT INTO MyTable (val1, val2, val3) SELECT val1, val2, val3 FROM OPENXML(...) It very performant and works like a charm! :) Hope this helps, Morty

I agree. If you do an order by and that order by is not the clustered index SQL Server would have to preprocess the entire dataset. (meaning the keys .. not necessarily the data). However, it would be interesting to see what happens if you don't use a postprocessing instruction. (ORDER BY, GROUP BY, UNION, DISTINCT etc) :) Morty

Hehe... been there ;) However, this is not an issue dependant on the .NET SQL Client layer. It's a question of how the SQL Server handles standard query processing. If you issue a select query with a 100.000 record resultset, it would be returned as a datastream (TDS) to the client. I would think that if there is no isolation (tx) there would just be a read lock on the current row (or index key) and not on the table, so records could easily be added or deleted. So if I'm correct the SQL Server would just read the data page by page and never precalculate the total amount and lock it. Hence the "imagined resultset" could be radically (add/delete) changed during the processing (which could take time), and a prior Count would be useless. Just my 2c :)

Well... the SqlDataReader is by no means a client side cursor... it's simply a TDS parser. That's what the dataset is for ;) Would be cool to check out the theory though... (how much locking does the TDS generator really support...) but as usual, I would probably never find the time. Morty

You could use the XML version IXmlBulkLoad (?) found in one of the SqlXml releases for SQL Server 2000. It would give you a more "programmable" approach. You could also use the native OLEDB interface (used by BCP ? ) IRowsetFastLoad. (look it up in the SQLOLEDB specs.) Morty

As you have correctly pointed out. SQL Server 2000 doesn't allow table variables as input parameters in stored procedures. What you could use instead is the "politically correct" xml document. Pass an xml document (array... ) to the SQL Server, and use the OPENXML statement to process it. :) Morty

I would be very surprised if you were able to retrieve the user password using ANY form of API ;)

What do you base this on ? AFAIK, the data is streamed directly from the server, and even though it doesn't utilize server-side cursors it doesn't precalculate the entire dataset (it's streamed ... standard SQL Server dataset processing...). So if records are appended during the read it should be reflected. However I could be wrong, so if you have any docs / references please post them :) .NET SDK: Changes made to a resultset by another process or thread while data is being read may be visible to the user of the SqlDataReader. However, the precise behavior is timing dependent. Morty

Yeah.. and that sort of sucks because that specific type is (sometimes?) transferred differently than normal SQL statements/types in order to handle the large amount of data it MIGHT contain. (2 147 483 647 bytes to be exact) ;) Morty

You can't use the System.Data.SqlClient namespace for this. As it requires an active connection to work. You should let the user configure the name of his SQL Server on initial startup. Morty

If you had used an SQL COUNT() function before you retrieved the it probably wouldn't be accurate either (atleast not in a multiuser enviornment), unless it's run in a serializable transaction. The SqlDataReader streams the data and will reflect all the changes in the database until the data has passed through the reader. So records could easily be added or deleted after the count was issued and while the reader is processing. :) Morty

Yes, the ArtistId can't be an IDENTITY column if that message appears. He should also consider using a different datatype than the all-mighty "text" for his input parameters. Morty