Top 10 issues in SQL Server database development

Top 10 issues in SQL Server database development

Developing in SQL Server puts you at risk and finds a quick solution. We edited the top 10 Frequently Asked Questions about SQL Server development. Common name constraints for tables and fields are explored. Learn how to solve concurrency issues and write stored procedures without understanding the T-SQL. See quick reference for these solutions and learn more.
You may ask ten questions about SQL Server development:

1. What are common name constraints for tables and fields?

2. Is it possible to write a stored procedure without understanding the T-SQL?

3. How to compare the performance of CLR Stored Procedures and functions in T-SQL?

4. How can I use the results produced by another stored procedure in one stored procedure?

5. How can I solve the SQL Server 2005 concurrency problem?

6. What tools are used in SQL Server 2005 to replace the query analyzer?

7. Can you provide some details about SQL and T-SQL?

8. Is there any new index type in SQL Server 2005?

9. How do I create a script and select it in the table?

10. How do I list database tables with no records?

Common name constraints on tables and fields

Experts replied:

The table and field names in SQL Server 2000 have a limit of 1 to 128 bytes and follow the rules used for identification. The first letter must be one of the following:

The letter specified in UNICODE standard 2.0.

Unicode defines letters in addition to letters from other languages.

Underline (_), at sign (@), or digit sign (#)

Starting with these symbols in SQL Server has special meanings. An identifier starting with the at sign (@) represents a local variable or parameter. An identifier starting with a digit (#) represents a temporary table or process. An identifier starting with two numeric symbols (#) identifies a global temporary object. Some Transact-SQL functions start with two at symbols. To avoid confusion with these functions, we recommend that you do not use identifiers starting with two at symbols. The following letters can be any of the following:

Unicode Standard 2.0-defined letters

Decimal number from base Latin or other speech

At sign (@), dollar sign ($), digit sign (#), or underline

The identifier cannot be a reserved word of transact-SQL. SQL Server retains reserved characters in both upper and lower case. Built-in spaces or special letters are not allowed to appear, although you can see in the old versions of northwind that they contain built-in spaces. You must enclose them in brackets before accessing them.

Does the SQL server2005 stored procedure be written without having any T-SQL knowledge?

As a result of Microsoft's ambition to use SQL Server 2005. Net integration to dominate the market in the past few years, manyProgramMembers think that the T-SQL is no longer necessary to create an SQL server stored procedure. Unfortunately (or not, based on your point of view), this is not all truth. Technically, you can create a stored procedure without understanding the T-SQL, but you cannot access any data without a T-SQL.

Data access is performed within the CLR Stored Procedure by using the standard ADO. Net class. Developers will find a lot of useless data access at the application layer.CodeThe code is easily transferred to the sqlclr routine. When these ADO. Net classes in the middle layer need to use T-SQL to access data, the same class will be used in the environment provided by the CLR host.

From a technical point of view, it is possible not to use T-SQL to write stored procedures. Is there any reason to do this? One case is that this is a CLR stored procedure used to retrieve data from common files or network services and set the data format to a row set. Here you may use operations that do not require T-SQL-but this is not a good metaphor for T-SQL stored procedure capabilities.

CLR Stored Procedure vs. T-SQL Stored Procedure

What is the performance of CLR Stored Procedures and functions compared to T-SQL stored procedures and functions?

Experts replied:

Here is a common rule to compare the performance of T-SQL routines and CLR routines: Use your data to test two sets of configuration on your server to see which one is better. That is to say, many people have run a performance test, the general result is that the T-SQL in the standard CRUD (create, read, update, delete) operation performance is better, in terms of complex mathematics, string assignment, and other tasks that exceed data access, CLR performs better. SQL Server expert Gustavo larriera edited the following useful links on this topic:

Use CLR integration in SQL Server 2005.

This section briefly introduces CLR integration in SQL Server 2005.

Select Clr and T-SQL in SQL Server 2005.

This section describes CLR integration in SQL Server 2005.

SQL Server: SQL Server 2005 beta 2 CLR questions and answers.

Database Administrator defense. net. · Performance Comparison of user-defined functions.

How to use the results of another stored procedure in one Stored Procedure

Experts replied:

As long as the stored procedure produces only one single result and uses the output produced by this stored procedure in another stored procedure, this technology is very direct. This technology is to use a temporary table to load the huge amount of iguo stored procedures, and then execute this process and save the results through the insert exec statement. Once the result is retained in the temporary table, they can use it just like other table data. Here is a process example that we may reuse:

Create proc usp_demo_allauthors

Select * from pubs... authors

Go