Querying, Managing, and Administering Databases Using SQL Server 2005

Chia sẻ: Nguyen Bao Ngoc | Ngày: | Loại File: PDF | Số trang:80

Thêm vào BST

Báo xấu

141
lượt xem 7
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

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...

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Querying, Managing, and Administering Databases Using SQL Server 2005

Chapter 1 Collaborate
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 Framework Working 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 Tools Grouping 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 NIIT 1.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.5 NIIT
From the Expert’s Desk This section contains: Best practices Tips and tricks FAQs Best 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. NIIT 1.6 Querying, Managing, and Administering Databases Using SQL Server 2005
Tips 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 sweeping the largest area/volume is on the left side. For instance, write “Sales * ExchangeRate” instead of “ExchangeRate * Sales”, and “Sales * 1.15” instead of “1.15 * Sales”. Here Sales value will be larger than the exchange rate. Consider replacing simple “Measure1 + Measure2” calculations with computed columns in the SQL data source. Avoid using Select * in your query design. Instead, ensure that you use the proper column names in the query. Using the proper column names decreases network traffic, puts less load on the database, and therfore, improves performance. FAQs What are the two new components introduced with SQL Server 2005? The two new components introduced with SQL are: SQL Server Management Studio SQL Server Business Intelligence Development Studio Can we use the PIVOT and UNPIVOT operators in SQL Server 2000? No, SQL Server 2000 does not support the PIVOT and UNPIVOT operators. To achieve the same functionality as that of the PIVOT and UNPIVOT operators, you can use JOINS in SQL Server 2000. Which framework is supported by SQL Server 2005? SQL Server 2005 supports Framework V2.0. Which application architecture does SQL Server 2005 support? SQL Server 2005 supports n-tier architecture. Which service of SQL Server 2005 allows you to gather and integrate data from various disparate data sources available in an organization? Integration services of SQL Server 2005 allow you to gather and integrate data from various disparate data sources available in an organization. Querying, Managing, and Administering Databases Using SQL Server 2005 1.7 NIIT
Challenge 1. Which of the following is not a component of the .NET Framework? a. Development tools and languages b. Base class library c. Service Broker d. Common Language Runtime 2. Which of the following component of the .NET Framework is used to create the interface for the Windows forms? a. Base class library b. Service Broker c. Development tools and languages d. Common Language Runtime 3. Which component of the .NET Framework provides an environment for the application to run? a. Development tools and languages b. Base class library c. Security Management d. Common Language Runtime 4. Which of the following is not a feature provided by the common language runtime? a. Service-oriented architecture b. Language interoperability c. Automatic memory management d. Platform independence 5. Which of the following is not a main component of the SQL Server Management Studio interface? a. Solution Explorer b. Query Editor c. Object Explorer d. SQL Server Client Agent NIIT 1.8 Querying, Managing, and Administering Databases Using SQL Server 2005
Home Assignment 1. What is the maximum length of NVARCHAR data type? a. 8000 characters b. 6000 characters c. 4000 characters d. 400 characters 2. Which of the following data type does not store integer value? a. tinyint b. int c. smallint d. decimal 3. Which operator is used to change the precedence of the arithmetic operators in SQL queries? a. \ b. [ ] c. ( ) d. { } 4. Which of the following query retrieves the record of the students who are studying in a class higher than class 10? a. SELECT * FROM Student WHERE NOT class=10 d. SELECT * FROM Student WHERE NOT class>=10 5. Which of the following query retrieves the names of the cities that start with letter M and whose fourth letter is B? a. SELECT City FROM City_List WHERE City LIKE ‘M_ B_%’ b. SELECT City FROM City_List WHERE City LIKE ‘M_ _ B[]’ c. SELECT City FROM City_List WHERE City LIKE ‘M_ _ B%’ d. SELECT City FROM City_List WHERE City LIKE ‘M% B%’ 6. Which SQL Server 2005 service provides data mining solutions that are built on data integrated in the data warehouse? a. Analysis services b. Reporting services c. Database engine d. Integration services Querying, Managing, and Administering Databases Using SQL Server 2005 1.9 NIIT
7. Which type of statement in SQL Server allows you to control the data access in the database? a. DML b. DCL c. DDL d. DQL 8. Which data type is used to store fixed length character data? a. text b. varchar(n) c. char(n) d. nchar 9. Which clause allows you to group data in a SELECT statement? a. WHERE b. HAVING c. ORDER BY d. COMPUTE BY 10. Which group operator is used to transform a set of columns into values? a. COMPUTE b. COMPUTE BY c. GROUP BY d. PIVOT NIIT 1.10 Querying, Managing, and Administering Databases Using SQL Server 2005
Chapter 2 Collaborate
Knowledge Byte In this section, you will learn about: Functions to customize the result set in SQL Server 2005 Using Functions to Customize the Result Set Read the following section of Chapter 2 of the book Querying and Managing Data Using SQL Server 2005: Using Functions to Customize the Result Set Querying, Managing, and Administering Databases Using SQL Server 2005 2.3 NIIT
From the Expert’s Desk This section contains: Best practices Tips and tricks FAQs Best Practices The following best practices can be considered while using procedures and functions in SQL Server 2005: Naming stored procedures: Never start the name of any stored procedure you create, with the SP prefix. This is because all system stored procedures start with the SP prefix. Using the INSERT statement: Avoid using the INSERT statement when performing bulk inserts into your database. Using a table from another database: To reference a table from another database in the same server, prefix the table with the database name. Use the following syntax to refer a table from another database in the same server: SELECT * from [].[dbo].[< table name>] Tips and Tricks The following tips and tricks will help you use functions in SQL Server 2005: Use SPACE() function for concatenating values in a query. Use VARCHAR(max) data type with LEFT(), RIGHT(), SUBSTRING() functions because these functions do not support ntext and text data types. Analyze all your query plans by using the SQL Query Analyzer to ensure that they are performing at optimum speed. The best things any serious developer can do to improve performance in an application is to learn to use the SQL Query Analyzer. Using this tool, you can find where the bottlenecks in your code are and, thereby, increase performance by altering indexes or even re-writing stored procedures. Avoid the use of nullable columns. The use of the nullable column consumes an extra byte on each column used. While querying data, there is much more overhead with nullable columns. While designing a database, you should use an alternative method to allow for a representation of zero data in the column, such as giving “–“ as the default value for a column of char type. 2.4 Querying, Managing, and Administering Databases Using SQL Server 2005 NIIT
FAQs What is the difference between the DELETE and TRUNCATE commands? The DELETE command removes the rows from a table based on the condition provided with the WHERE clause. The TRUNCATE command removes all the rows from a table and does not give an option to selectively delete records. How can you figure out if a string is completely in uppercase? You can find if a string is completely in uppercase by using the ASCII string function. Can you define any length for a VARCHAR data type, such as VARCHAR(10000)? When the length is specified in declaring a VARCHAR variable or column, the maximum length allowed is 8000 characters. If the length is greater than 8000 characters, you have to use the MAX specifier as the length. If there are VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) data types, are there CHAR(MAX), NCHAR(MAX) and BINARY(MAX) data types also? No, there is no CHAR(MAX), NCHAR(MAX), or BINARY(MAX) data types. The main reason why the MAX specifier is not included for these data types is because these data types are fixed-length data types. If the MAX specifier is included for these data types, it will be a big waste of disk space as each column will consume 2 GB of memory even if only a short string value is assigned to the column. How can you turn on IDENTITY on an existing column? To change an existing column as an IDENTITY column, perform the following steps: a. Modify table in object browser. b. Change column property to IDENTITY “yes”. c. Save the change. Querying, Managing, and Administering Databases Using SQL Server 2005 2.5 NIIT
Challenge 1. Identify the output of the following command: Select left(‘SQL Server’,3) a. Ser b. SQL c. ver d. QL S 2. Which function is used to return the length of a string? a. length b. lenString c. StrLen d. len 3. Which string is used to replace a character with another character? a. charindex b. space c. stuff d. patindex 4. Which method returns the current date and time? a. getdate b. date c. gettime d. time 5. What is the return type of rand function? a. numeric b. int c. float d. smallint 2.6 Querying, Managing, and Administering Databases Using SQL Server 2005 NIIT
Home Assignment 1. Which join should be used to retrieve records with values satisfying the join condition in the common column? a. Outer Join b. Inner Join c. Cross Join d. Equi Join 2. Which database contains critical data that controls SQL Server operations? a. model b. msdb c. tempdb d. master 3. What should be the size of the transaction log file? a. 25-35 percent b. 25-30 percent c. 25-40 percent d. 20-40 percent 4. Which key constraint is used to maintain referential integrity? a. Foreign key constraint b. Primary key constraint c. Unique key constraint d. Check constraint 5. Which stored procedure is used to bind a rule? a. sp_bind b. sp_bindrule c. sp_rulebind d. sp_unbindrule 6. Which join returns the Cartesian Product of the rows of two tables? a. EQUI JOIN b. INNER JOIN c. CROSS JOIN d. OUTER JOIN Querying, Managing, and Administering Databases Using SQL Server 2005 2.7 NIIT
7. What does >ALL mean in a SELECT statement that is using a subquery? a. It means greater than the maximum value in the list. b. It means any of the values in the list. c. It means greater than the minimum value in the list. d. It means lesser than the minimum value in the list. 8. Which system database contains all system objects? a. Resource database b. master database c. temdb database d. model database 9. What should be the size of a transaction log file? a. 25-40 percent of the database size b. 25-30 percent of the database size c. 25-35 percent of the database size d. 22-40 percent of the database size 10. Which keyword with the CHECK constraint ensures that the values entered are from a list of constant expressions? a. BETWEEN b. IN c. ALL d. LIKE 2.8 Querying, Managing, and Administering Databases Using SQL Server 2005 NIIT
Chapter 3 Collaborate