Detailed explanation of Microsoft SQL Server database development issues

I would like to emphasize that from a technical standpoint, it is possible to write a stored procedure without using T-SQL. So is there any reason to do so? One scenario is that this is a CLR stored procedure used to retrieve data from a normal file or network service and format the data as a rowset. There may be operations that don't require T-SQL-but this is not a good metaphor for the capabilities of T-SQL stored procedures.

Concurrency problems with stored procedures in 1.SQL Server 2005

Q: I encountered a concurrency problem in SQL Server2005. There are some vacant seats on the bus where I hold the ticket. I need to see if there are any vacant seats before I insert the ticket for sale. The things my stored procedure does are as follows:

The following are the referenced contents: CREATE PROCEDURE Add_ticket-parameters DECLARE Free_seats int BEGIN TRANSACTION SELECT Free_seats = COUNT (*) from Tickets WHERE Seat_is_not_taken IF Free_seats <> 0 INSERT into Tickets VALUES (...) -some statements End TRANSACTION

The problem is that two processes can read free tickets at the same time, and you can book a ticket, even if there is no more free. I need a way to prevent a process from running the Add_ticket program in another process, but not to read the number of empty tickets without inserting a new ticket.

Answer: you are right; higher isolation levels do not guarantee that multiple readers will be able to read the same data row at the same time. However, there are several ways you can finish the work. For example, you can assign a unique identifier to each seat (meaning, a unique key-not necessarily a GUID), and create a table that describes which seats are already subscribed to. With a unique constraint on the table, you can make sure that the same seat is not inserted twice.

That said, I think a more interesting approach is to use SQL service Broker. You can set up a session for each bus and place the handle of the session in a table that the reader can refer to before performing receive. In this way, readers can filter correctly. Each seat on the bus is plugged into a message into the queue. The reader can simply receive the desired message (in the process, book the seats on the bus). Service Broker ensures that no messages are accepted two times, meaning that you will not encounter concurrent problems.

Index categories in 2.SQL Server 2005

Q: Is there a new index category in SQL Server 2005?

Answer: SQL Server 2005 does not introduce a new index type for the relational table. Basically-clustered and nonclustered indexes are implemented in a b-trees way-still in use. However, SQL Server 2005 does contain some enhancement on indexes, whether it is a full text index or XML data, and these improvements can also improve some of the problems associated with relational indexing.

The full text indexing feature of SQL Server 2005 is new and is rewritten. For information on this feature, read the MSDN Broadcast documentation for Nimish Khanolkar, which describes the Full-text lookup in SQL Server 2005.

XML is one of the other things that has changed dramatically in SQL Server 2005. Now, for developers, a first-class XML data type is available. This type supports the XQuery query language, and a field of this type can be indexed through a specially formatted XML index. To learn more about XML types, look for articles on MSDN.

There are also a variety of enhanced T-SQL index commands. Perhaps the most intriguing is the new "online" index type, which allows database administrators to perform index maintenance tasks without having to lock users out of the table. This is likely to mark the end of the situation where the database administrator needs to wait until 3 o'clock to open the Maintenance window to fix the problem!

3. Common name constraints for tables and fields

Q: What is the common name constraint on tables and fields?

Answer: Table and field names under SQL Server 2000 have a limit of 1 to 128 bytes and follow the rules for identification.

The first letter must be one of the following:

· The letter specified in Unicode Standard 2.0.

Unicode definitions of letters include: Latin alphabet, from A to Z, except for letters from other languages.

· Underscore (_), at sign (@), or number sign (#)

The beginning of these symbols as identifiers in SQL Server has a special meaning. An identifier that begins with an at sign (@) represents a local variable or parameter. An identifier that begins with a number sign (#) represents a temporary table or procedure. An identifier that begins with a two digit sign (# #) identifies a global temporary object.

The names of some Transact-SQL functions begin with two at symbols (@@). To avoid confusion with these functions, it is recommended that you do not use identifiers that begin with two at symbols (@@).

The next letter can be any of several of the following:

· Unicode Standard 2.0-defined letters

· Decimal digits from basic Latin or other speech

· At sign (@), dollar sign ($), number sign (#), or underline

Identifiers must not be reserved words for Transact-SQL. SQL Server retains some uppercase and lowercase reserved words. Built-in spaces or special letters are not allowed, although you can see in the good old version of the Northwind that they contain built-in space. You have to be able to access them by enclosing them in parentheses.

4. Write SQL Server2005 stored procedures without any T-SQL knowledge

Q: Is it possible to write stored procedures without understanding T-SQL? ］

Answer: As in the past few years Microsoft tried to use SQL Server 2005. NET integration to dominate market ambitions, many programmers believe that creating SQL Server stored procedures no longer requires T-SQL. Unfortunately (or not, depending on your point of view), this is not all true. It is technically possible to create a stored procedure without the knowledge of T-SQL, but you cannot access any data without T-SQL.

Data access within a CLR stored procedure is accomplished by using the standard Ado.net class. Developers will find many of the same useless data access codes at the application layer, which can easily be transferred to the SQLCLR routines. When these ado.net classes in the middle tier need to use T-SQL to access data, the same classes are used in the context provided by the CLR host.