1 / 31

T-SQL for Data Definition

This overview covers Transact-SQL's data definition language, managing databases, data types, tables, and other SQL Server objects at Hanoi University of Technology. Learn about creating, altering, and dropping databases and database objects.

mbahe
Download Presentation

T-SQL for Data Definition

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. T-SQL for Data Definition Vu Tuyet Trinh trinhvt-fit@mail.hut.edu.vn Hanoi University of Technology

  2. Overview of Transact-SQL • Based on AINSI SQL 92 standard • Composing of three categories • Data Manipulation Language (DML) • Data Definition Language (DDL) • Data Control Language (DCL) • Having some Microsoft specific extensions • Beyond relational data • .net framework integration

  3. Data Definition Language • Create used to create databases and their objects. • Use allows you to specify the database you wish to work with within your DBMS. • Alter used to modify the definition of it without deleting it • Drop used to remove entire database objects

  4. Overview of Database Objects

  5. Outline • Data Definition Language • Managing Databases • Data Types • Managing Tables • Managing other SQL Server Objects

  6. SQL Server Enterprise Edition Databases in SQL Server • Database Storing data and other database objects • Database Snapshot • Maintain historical data for report generation • Safeguard data against administrative error • Safeguard data against user error

  7. Creating a New Database • Factors to consider • Default: Sysadmin, dbcreator • Creator becomes the owner • Maximum of 32,767 per server • Follow naming rules

  8. Creating a New Database • Some arguments: • The name of the database • The size of the database • The files where the database will reside CREATE DATABASE SampleON PRIMARY ( NAME=SampleData, FILENAME='c:\Program Files\..\..\Data\Sample.mdf', SIZE=10MB, MAXSIZE=15MB, FILEGROWTH=20%) LOG ON ( NAME=SampleLog, FILENAME= 'c:\Program Files\..\..\Data\Sample.ldf', SIZE=3MB, MAXSIZE=5MB, FILEGROWTH=1MB)COLLATE SQL_Latin1_General_Cp1_CI_AS

  9. Setting & Viewing Database Options • Set Database Options By Using: • SQL Server Management Studio • ALTER DATABASE statement • Database Option Categories • Auto options • Cursor options • Recovery options • SQL options • State options

  10. Retrieving Database Information • Determining database properties by using the DATABASEPROPERTYEX Function • SELECT DATABASEPROPERTYEX (‘pubs’,’useraccess’) • SELECT DATABASEPROPERTYEX (‘pubs’,’recovery’) • Using system stored procedures to display information about databases and its parameters • sp_helpdb • sp_helpdbdatabase_name • sp_spaceused [objname]

  11. Attaching an Existing Database

  12. Creating a Snapshot Database

  13. Managing Databases • Shrinking a Database or File • Dropping a Database

  14. Shrinking a Database or File • Shrinking an Entire Database • Shrinking a Data File in the Database • Shrinking a Database Automatically Set autoshrink database option to true DBCC SHRINKDATABASE (Sample, 25) DBCC SHRINKFILE (Sample_Data, 10)

  15. Dropping a Database • Methods of Dropping a Database • SQL Server Enterprise Manager • DROP DATABASE statement • Restrictions on Dropping a Database • While it is being restored • When a user is connected to it • When publishing as part of replication • If it is a system database DROP DATABASE Northwind, pubs

  16. Outline • Data Definition Language • Managing Databases • Data Types • Managing Tables • Managing other SQL Server Objects

  17. Data Types • System data types • User-defined data type • CLR types • Spatial data • Filestreams • XML

  18. System Data Types • Exact numeric • bit, tinyint, smallint, int, bigint, numeric, decimal, smallmoney, money • Approximate numeric • float, Real • Date & Time • datetime, smalldatetime • Character string • char, varchar, text • Unicode character string • nchar, nvarchar, ntext • Binary character string • binary, varbinary, image • Others • sql_variant, timestamp, xml…..

  19. Creating Tables • Determining column & data type • Determining column nullability • Defining column default values

  20. Example CREATE TABLE dbo.Categories (CategoryID CategoryName Description Picture int IDENTITY(1,1)nvarchar(15) ntext image NOT NULL,NOT NULL, NULL, NULL)

  21. Modifying Table Definition ALTER TABLE table_name {[ALTER COLUMN column_name {DROP DEFAULT |SET DEFAULT constant_expression |IDENTITY [(seed,increment)]} |ADD {< column_definition >|< table_constraint>} [ ,...n ] |DROP {[ CONSTRAINT ] constraint_name | COLUMN column } ]}

  22. Example ALTER TABLE CategoriesNew ADD Commission money null ADD Customer_name Sales_amount Sales_date Customer ID Commission DROP ALTER TABLE CategoriesNew DROP COLUMN Sales_date

  23. Outline • Data Definition Language • Managing Databases • Data Types • Managing Tables • Managing other SQL Server Objects

  24. Views • What is a view? • Creating a virtual collection of records from existing tables • Being used for security and/or performance issues • Creating view • Removing view • CREATE VIEW [schema_name.]view_name [(column[,...n])] • [ WITH <view_attribute> [ ,...n ] ] • AS • select_statement • [ WITH CHECK OPTION ] [ ; ] • <view_attribute>::={[ENCRYPTION] • [SCHEMABINDING] • [ VIEW_METADATA ]} DROP VIEW { view_name } [ ,...n ]

  25. Others CREATE/ALTER/DROP… command • CREATE/ALTER/DROP RULE … • CREATE/ALTER/DROP PROCEDURE … • CREATE/ALTER/DROP FUNCTION … • CREATE/ALTER/DROP TRIGGER … • CREATE/ALTER/DROP USER … • … For further detail information, see [Microsoft SQL Server Books Online ]

  26. Summary • Data definition language • Creating and managing database object Create, Use, Alter, Drop, • SQL Server database objects database, table, view, index, … • Simplifying the management of more complicated data • Relational data • User-defined Data • CLR types • Spatial data • Filestreams • XML 30

More Related