Appendix SQL Server 2000

eger data from -2^31 through 2^31 - 1. Smallint: -32768..32767 Decimal and Numeric Decimal:Fixed precision and scale numeric data from -10^38 +1 through 10^38 –1. Numeric: Functionally equivalent to decimal. Sql Server Enterprise Manager Data types Float and Real Float: Floating precision number data from -1.79E + 308 through 1.79E + 308. Real: Floating precision number data from -3.40E + 38 through 3.40E + 38. DateTime Sql Server Enterprise Manager Data types Character string Char: Fixed-length non-Unicode character data with a maximum length of 8,000 characters. Varchar: Variable-length non-Unicode data with a maximum of 8,000 characters. Text: Variable-length non-Unicode data with a maximum length of 2^31 - 1 (2,147,483,647) characters. Sql Server Enterprise Manager Data types Unicode character strings Nchar: Fixed-length Unicode data with a maximum length of 4,000 characters. Nvarchar: Variable-length Unicode data with a maximum length of 4,000 characters. Ntext: Variable-length Unicode data with a maximum length of 2^30 - 1 (1,073,741,823) characters. Sql Server Enterprise Manager Data types Binary strings Binary: Fixed-length binary data with a maximum length of 8,000 bytes. Varbinary: Variable-length binary data with a maximum length of 8,000 bytes. Image: Variable-length binary data with a maximum length of 2^31 - 1 (2,147,483,647) bytes. Sql Server Enterprise Manager To modify a table Expand a server group, and then expand a server. Expand Databases, expand the database in which the table belongs, and then click Tables. In the details pane, right-click the table, and then click Design Table. Sql Server Enterprise Manager To open a table Expand a server group, and then expand a server. Expand Databases, expand the database in which the table belongs, and then click Tables. In the details pane, right-click the table, and then click Open Table -> Return All Rows. Sql Server Enterprise Manager To delete a table Expand a server group, and then expand a server. Expand Databases, expand the database in which the table belongs, and then click Tables. In the details pane, right-click the table, and then click Delete. To see how deleting this view will affect the database, click Show Dependencies. Click Drop All. Sql Server Enterprise Manager To rename a table Expand a server group, and then expand a server. Expand Databases, expand the database in which the table belongs, and then click Tables. In the details pane, right-click the table, and then click Rename. Enter the new name of the table. Confirm the new name. Sql Server Enterprise Manager To create a view Expand a server group, and then expand a server. Expand Databases, expand the database in which the view belongs, and then click Views. In the details pane, right-click the view, and then click New View. To add additional tables or views to the view, right-click in the diagram pane, and then click Add Table. Sql Server Enterprise Manager To create a view On the Tables or Views tabs, click the table or view to add to the new view, and then click Add. Repeat for each table or view you want to add to the new view. To remove an entire table or view from the view, in the diagram pane, right-click the title bar of the table, and then click Remove. In the Column box of the grid pane, select the columns to be referenced in the view. Sql Server Enterprise Manager To create a view Select Output if the column is to appear in the result set of the view. To group by column, right-click the column, and then click Group By. In the Criteria column, enter the criteria specifying which rows to retrieve; this determines the WHERE clause. If Group By is specified, this determines the HAVING clause. Sql Server Enterprise Manager To create a view In the Or column, enter any additional criteria to specify which rows to retrieve. Right-click anywhere in the grid pane, and then click Properties. Optionally, select: Output all columns to display all columns in the view in the result set. DISTINCT values to filter out duplicate values in the result set. Sql Server Enterprise Manager To create a view Right-click anywhere in the diagram pane, and then click Run (to view the result set) or Save (to save the view). Sql Server Enterprise Manager To delete a view Expand a server group, and then expand a server. Expand Databases, expand the database in which the view belongs, and then click Views. In the details pane, right-click the view, and then click Delete. To see how deleting this view will affect the database, click Show Dependencies. Click Drop All. Sql Server Enterprise Manager To rename a view Expand a server group, and then expand a server. Expand Databases, expand the database in which the view belongs, and then click Views. In the details pane, right-click the view, and then click Rename. Enter the new name of the view. Confirm the new name. Sql Server Enterprise Manager To create a user Expand a server group, and then expand a server. Expand Security, click Logins, and then right-click New Login. In tab General In Name, enter a name for the Microsoft® SQL Server™ login. Under Authentication, select SQL Server Authentication. Sql Server Enterprise Manager To create a user In tab General Optionally, in Password, enter a password. In tab Server Roles Set Server Roles In tab Database Specify which databases can be accessed by this login. Click Ok button and confirm password Sql Server Enterprise Manager To grant a user Expand a server group, and then expand a server. Expand Databases, and then expand the database to which the login will be granted access. Right-click Users, and then click New Database User. Sql Server Enterprise Manager To grant a user In the Login name box, click the Microsoft® SQL Server™ login to which database access will be granted. Optionally, in User name, enter the user name that the login is known by in the database. By default, it is set to the login name. Optionally, select database role memberships in addition to public, the default. Sql Server Enterprise Manager To attach a database Expand a server group, and then expand a server. Right-click Databases, and select All Tasks, and then click Attach Database. Enter the name of the MDF (master data file) of the database to attach. If you are not sure where the file is located, click browse (...) to search. To ensure that the specified MDF file is correct, click Verify. Sql Server Enterprise Manager To attach a database In the Attach as box, enter the name of the database. The database name must not match any existing database names. Specify the database owner. Click OK. A database node for the newly attached database is created in the Database folder. Sql Server Enterprise Manager To detach a database Expand a server group, and then expand a server. Expand Databases. Right-click the database, and then select All Tasks/Detach Database. To terminate any existing connections from the database, click Clear. Click OK. The database node for the detached database is removed from the Database folder. Sql Server Enterprise Manager To generate a script Expand a server group, and then expand a server. Expand Databases, right-click the database to script, point to All Tasks, and then click Generate SQL Scripts. On the General tab, select the database objects to script. By default, all objects in the database are scripted. Sql Server Enterprise Manager To generate a script On the Formatting tab, select from the script formatting options: Generate the CREATE command for each object so that each object to be scripted is explicitly created using its existing definition. This is selected by default. Generate the DROP command for each object so that a DROP statement is added to the script for each object to be scripted. This is selected by default. Sql Server Enterprise Manager To generate a script On the Formatting tab Generate scripts for all dependent objects so that all objects in the database which are needed to create the objects listed in the script are included automatically in the script if not already selected. On the Options tab, select the security-related, table-related, and script file-related options. On the General tab, click Preview to view a preview of the generated script. Query Analyzer Microsoft® SQL Server™ 2000 SQL Query Analyzer is a graphical tool that allows you to: Create queries and other SQL scripts and execute them against SQL Server databases. Quickly insert, update, or delete rows in a table. Locate objects within databases (object search feature), or view and work with objects. Query Analyzer Connect to an instance of SQL Server to open a query window in SQL Query Analyzer. To connect to SQL Server In the Connect to SQL Server dialog box, enter the name of the database server in the SQL Server box. To select the local server, select (local). To specify another server or another instance of the server, enter the server name in the SQL Server box. Click the browse button (...) to display a list of active servers. Query Analyzer To connect to SQL Server Click Windows NT Authentication to connect using Windows NT Authentication. Click SQL Server Authentication to connect using SQL Server Authentication. Note: This dialog box is also accessible from the toolbar, and from the Connect command on the File menu. Query Analyzer Query Window The title bar of the query window displays the name of the database server, the name of the current database, the current login name, and the query name. The query window is composed of multiple panes The Editor pane is a text editor where you can enter Transact-SQL statements. The Results pane displays returned result sets. The Messages pane displays error messages. Query Analyzer To work a database To change a database Click icon To execute SQL statements Execute a complete script by creating or opening the script in the Editor pane and pressing F5. Execute only selected SQL statements by highlighting the lines of code in the Editor pane and pressing F5.