Querying, Managing, and Administering Databases Using SQL Server 2005

When you write stored procedures, triggers, and user-defined functions, you need todecide whether to use traditional Transact-SQL or a programming language that iscompatible with the .NET Framework, such as Visual Basic .NET or C#. Transact-SQL is best for situations in which the code primarily performs data access with littleor no procedural logic. Programming languages that are compatible with the.NET Framework are best-suited for computationally-intensive functions andprocedures that feature complex logic or for situations where you want to takeadvantage of the .NET Framework class library...
Nội dung trích xuất từ tài liệu:
Querying, Managing, and Administering Databases Using SQL Server 2005 Chapter 1Collaborate Knowledge Byte In this section you will learn about: .NET Framework SQL Server 2005 tools Grouping data by using the UNPIVOT operator.NET Framework Read the following topic in the Introduction to SQL Server 2005 section of Chapter 1 of the book Querying and Managing Data Using SQL Server 2005: The .NET FrameworkWorking with SQL Server 2005 Tools Read the following section of Chapter 1 of the book Querying and Managing Data Using SQL Server 2005: Identifying the SQL Server 2005 ToolsGrouping Data by Using the UNPIVOT Operator The UNPIVOT operator allows database users to normalize the data that has earlier been pivoted. The UNPIVOT operator transforms the multiple column values of a record into multiple records with the same values in a single column. For example, a table that stores applicants’ records has one row for each applicant. The table stores the applicant’s name, qualification and grade. After applying the UNPIVOT operator on the table, the table contains a different row for each qualification that the applicant has completed. Querying, Managing, and Administering Databases Using SQL Server 2005 1.3 NIIT The following table shows the structure of the Applicant table before using the UNPIVOT operator. Column Applicant Matriculation Higher Secondary Graduation Name Name Data Anderson A A B Records Samuel A B A Sandra B B B Applicant Table Before Using the UNPIVOT Operator The following table shows the structure of the Applicant table after using the UNPIVOT operator. Column Name Applicant Name Qualification Grade Anderson Matriculation A Data Records Anderson Higher Secondary A Anderson Graduation B Samuel Matriculation A Samuel Higher Secondary B Samuel Graduation A Sandra Matriculation B Sandra Higher Secondary B Sandra Graduation B Applicant Table After Using the UNPIVOT Operator NIIT1.4 Querying, Managing, and Administering Databases Using SQL Server 2005 The syntax for using the UNPIVOT operator is: SELECT ApplicantName, newTable.Qualification, newTable.Grade FROM tblPivot UNPIVOT (Qualification FOR Grade IN (Matriculation, Higher Secondary, Graduation)) as tblUnPivot Querying, Managing, and Administering Databases Using SQL Server 2005 1.5NIIT From the Expert’s Desk This section contains: Best practices Tips and tricks FAQsBest Practices The following best practices can be considered while using the query statements in SQL Server 2005: When you write stored procedures, triggers, and user-defined functions, you need to decide whether to use traditional Transact-SQL or a programming language that is compatible with the .NET Framework, such as Visual Basic .NET or C#. Transact- SQL is best for situations in which the code primarily performs data access with little or no procedural logic. Programming languages that are compatible with the .NET Framework are best-suited for computationally-intensive functions and procedures that feature complex logic or for situations where you want to take advantage of the .NET Framework class library. To rotate the data of a table you can use the PIVOT and UNPIVOT operators, instead of using complex JOIN statements. SQL Server 2005 provides three new data types for storing long columns: VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX). These new data types are easier to use than the older large object data types, such as TEXT, NTEXT, and IMAGE. When building a new application that needs to store a column that possibly might exceed 8000 bytes, you should consider using these new large value data types. The total characters displayed in the Query Editor Results window are limited to 256 characters. If the result exceeds the limit of 256 characters, it gets truncated. In this case, you can use the Options tab in Management Studio to set the maximum column size under the Results section. The result sets that you return from your database should be kept as small as possible. This greatly improves the performance and makes the database much more scalable. NIIT1.6 Querying, Managing, and Administering Databases Using SQL Server 2005Tips and Tricks The following tips and tricks will help you perform effective query by using SQL Server 2005: When writing calculation expressions such as “expr1 * expr2”, ensure that the expression sweep ...