KNOWLEDGE BEFORE WRITING QUERIES
WILDCARD EXAMPLES :
% - V% - VENKAT, VIJAY, VAMSI
- - V-NKAT - VENKAT
[] - [CS]HERYL - CHERYL, SHERYL
[^] - [^C] - SHERYL
FUNCTIONS :
Arithmetic operators
Comparison operators
Logical operators
Assignment operators
String concatenation operators
Unary operators
Arithmetic Operators
Operator Description
+ Addition
- Subtraction
* Multiplication
/ Division
% Modulo (returns the integer remainder of a division)
Comparison Operators
= Equal to
> Greater than
<>= Greater than or equal to
<= Less than or equal to <> Not equal to
! = Not equal to (SQL-89 standard)
! <> Not greater than (SQL-89 standard)
Logical Operators
AND True if both expressions evaluate to true
BETWEEN True if the value is within a specified range
IN True if the result is equal to one in a list
LIKE True if the result matches a pattern
NOT Reverses the value of any other logical operator (such as NOT IN)
OR True if either logical expression evaluates to true
EXISTS True if a subquery (introduced later in this session) returns any Records
---ALL True if all of a set of compared values evaluates to true
---ANY True if any one of a set of compared values evaluates to true
---SOME True if some of a set of compared values evaluates to true
The assignment operator
Transact-SQL only has one assignment operator, and you’ve probably guessed it
already—it’s the equals sign (=). You use it when assigning values to variables or
specifying column headings.
The string concatenation operator
String concatenation is an operation you’ll find yourself performing over and over
again. Luckily, it is very intuitive—T-SQL uses the plus sign (+) to concatenate
strings. You can use it in SELECT statements like the following:
SELECT au_fname + ‘,’ + au_lname FROM authors
Unary Operators
Operator Description
+ The number is positive
- The number is negative
Working with Aggregate Functions
DISTINCT tells the query to ignore duplicate values, and ALL is a default
SUM returns the total of all the values in a numeric field
AVG returns the average of all the values in the numeric column:
COUNT returns the number of records in the group:
COUNT_BIG for big size purpose
MAX returns the highest value in the column:
MIN returns the lowest value in the column:
STDEV finding standar deviation
STDEVP finding standar deviation
CLAUSES :
AS
SET
ORDER BY
GROUP BY
UNION
UNIONALL
HAVING
CASE FUNCTION :
SELECT case when id = 4 then 'VENKAT' ELSE 'VIJAY' END NAME FROM SYSOBJECTS
WHILE : WHILE (SELECT AVG(price) FROM titles) < $30BEGIN UPDATE titles SET price = price * 2 SELECT MAX(price) FROM titles IF (SELECT MAX(price) FROM titles) > $50 BREAK ELSE CONTINUEENDPRINT 'Too much for the market to bear'
IFDECLARE @msg varchar(255)IF (SELECT COUNT(price) FROM titles WHERE title_id LIKE 'TC%' AND price BETWEEN 10 AND 20) > 0 BEGIN SET NOCOUNT ON SET @msg = 'There are several books that are a good value between $10 and $20. These books are: ' PRINT @msg SELECT title FROM titles WHERE title_id LIKE 'TC%' AND price BETWEEN 10 AND 20 ENDELSE BEGIN SET NOCOUNT ON SET @msg = 'There are no books between $10 and $20. You might consider the following books that are under $10.' PRINT @msg SELECT title FROM titles WHERE title_id LIKE 'TC%' AND price <>Inbuilt functions :
GOTO
WAITFOR
RTRIM (select Right of given characters)
LTRIM (select Right of given characters)
Getdate()
Cast
Convert
Isnull
Coleasce
Datediff
Identity
Queries :
SELECT employeeID, firstname, lastname FROM names WHERE dept = 'prod' or city = 'Orlando' or division = 'food'
UNION ALL
SELECT employeeID, firstname, lastname FROM names WHERE dept = 'prod' UNION ALL SELECT employeeID, firstname, lastname FROM names WHERE city = 'Orlando' UNION ALL SELECT employeeID, firstname, lastname FROM names WHERE division = 'food'
GROUP BY
USE Northwind SELECT OrderID FROM [Order Details] WHERE UnitPrice > 10 GROUP BY OrderID
Using a NOT EXISTS
SELECT a.hdr_key FROM hdr_tbl a WHERE NOT EXISTS (SELECT * FROM dtl_tbl b WHERE a.hdr_key = b.hdr_key)
Using a NOT IN
SELECT hdr_key FROM hdr_tbl WHERE hdr_key NOT IN (SELECT hdr_key FROM dtl_tbl)
QUERY :
SELECT companyid, plantid, formulaid FROM batchrecords WHERE companyid = '0001' and plantid = '0202' and formulaid = '39988773' OR companyid = '0001' and plantid = '0202'
QUERY WITH VARIABLE :
DECLARE @age int SET @age = "30" DECLARE @service_years int SET @service_years = "10" SELECT employee_id FROM employees WHERE age = @age and service_years = @service_years
................. I will post more................ wait and see.................
Sunday, July 5, 2009
Class 5 - Normalization
Data Models : We can use following models to design our database.
Entity, Hierarchical, Network
DATA MODELING :
1 – 1 Relationship
1 – many
Many – many
1-1 defining with primary – unique key
Students table (studenntID) - Hostel (StudID)
1- many definign Primary – Foreign Key
Students table (StudentID) - Library (StudID)
Many – Many defining with 3 table
Students table (studID) - Subjects (SubjectID) - Third table ( )
NORMALIZATION :
1NF
Eliminate Repeating Groups - Make a separate table for each set of related attributes, and give each table a primary key.
2NF
Eliminate Redundant Data - If an attribute depends on only part of a multi-valued key, remove it to a separate table.
3NF
Eliminate Columns Not Dependent On Key - If attributes do not contribute to a description of the key, remove them to a separate table.
BCNF
Boyce-Codd Normal Form - If there are non-trivial dependencies between candidate key attributes, separate them out into distinct tables.
4NF
Isolate Independent Multiple Relationships - No table may contain two or more 1:n or n:m relationships that are not directly related.
5NF
Isolate Semantically Related Multiple Relationships - There may be practical constrains on information that justify separating logically related many-to-many relationships.
ONF
Optimal Normal Form - a model limited to only simple (elemental) facts, as expressed in Object Role Model notation.
DKNF
Domain-Key Normal Form - a model free from all modification anomalies.
Entity, Hierarchical, Network
DATA MODELING :
1 – 1 Relationship
1 – many
Many – many
1-1 defining with primary – unique key
Students table (studenntID) - Hostel (StudID)
1- many definign Primary – Foreign Key
Students table (StudentID) - Library (StudID)
Many – Many defining with 3 table
Students table (studID) - Subjects (SubjectID) - Third table ( )
NORMALIZATION :
1NF
Eliminate Repeating Groups - Make a separate table for each set of related attributes, and give each table a primary key.
2NF
Eliminate Redundant Data - If an attribute depends on only part of a multi-valued key, remove it to a separate table.
3NF
Eliminate Columns Not Dependent On Key - If attributes do not contribute to a description of the key, remove them to a separate table.
BCNF
Boyce-Codd Normal Form - If there are non-trivial dependencies between candidate key attributes, separate them out into distinct tables.
4NF
Isolate Independent Multiple Relationships - No table may contain two or more 1:n or n:m relationships that are not directly related.
5NF
Isolate Semantically Related Multiple Relationships - There may be practical constrains on information that justify separating logically related many-to-many relationships.
ONF
Optimal Normal Form - a model limited to only simple (elemental) facts, as expressed in Object Role Model notation.
DKNF
Domain-Key Normal Form - a model free from all modification anomalies.
Class 4 - Constraints
CONSTRAINTS :
Primary Key, Foreign Key,Unique ,Default Key,Check,Null
Primary key
NEW TABLE : create table abc (sl int identity(1,1), name varchar(100), constraint PK_sl Primary key(Sl))
ON EXISTING : ALTER TABLE ABC ADD CONSTRAINT pk_sL PRIMARY KEY (SL)
DROPPING : ALTER TABLE ABC DROP CONSTRAINT pk_sL
Foreign Key
NEW TABLE : CREATE TABLE ABCD (SL INT, PLACE VARCHAR(100),DISTRICT VARCHAR(50), CITY VARCHAR(50), CONSTRAINT FK_sl$abc$sl Foreign Key (Sl) references abc(Sl))
ON EXISTING : alter table abcd ADD constraint FK_Sl$abc$sl FOREIGN KEY (SL) REFERENCES ABC(SL)
DROPPNG : alter table abcd drop constraint FK_Sl$abc$sl
unique
NEW TABLE : create table abcde (sl int, rollnum int, classname varchar(10),
Constraint UQ_rollnum Unique(rollnum))
ON EXISTING : ALTER TABLE ABCDE ADD CONSTRAINT uq_ROLLNUM UNIQUE CLUSTERED (ROLLNUM)
DROPPING : ALTER TABLE ABCDE DROP CONSTRAINT UQ_ROLLNUM
check
NEW TABLE : CREATE TABLE ABCDEF (SL INT, AMOUNT INT, CONSTRAINT ch_AMOUNT CHECK(AMOUNT>5))
ON EXISTING : ALTER TABLE ABCDEF ADD CONSTRAINT CH_AMOUNT CHECK(AMOUNT>5)
DROPPING : ALTER TABLE ABCDEF DROP CONSTRAINT CH_AMOUNT
default
CREATE TABLE ABCDEFF (SL INT, MARKS INT DEFAULT 35)
Null / not null
Primary Key, Foreign Key,Unique ,Default Key,Check,Null
Primary key
NEW TABLE : create table abc (sl int identity(1,1), name varchar(100), constraint PK_sl Primary key(Sl))
ON EXISTING : ALTER TABLE ABC ADD CONSTRAINT pk_sL PRIMARY KEY (SL)
DROPPING : ALTER TABLE ABC DROP CONSTRAINT pk_sL
Foreign Key
NEW TABLE : CREATE TABLE ABCD (SL INT, PLACE VARCHAR(100),DISTRICT VARCHAR(50), CITY VARCHAR(50), CONSTRAINT FK_sl$abc$sl Foreign Key (Sl) references abc(Sl))
ON EXISTING : alter table abcd ADD constraint FK_Sl$abc$sl FOREIGN KEY (SL) REFERENCES ABC(SL)
DROPPNG : alter table abcd drop constraint FK_Sl$abc$sl
unique
NEW TABLE : create table abcde (sl int, rollnum int, classname varchar(10),
Constraint UQ_rollnum Unique(rollnum))
ON EXISTING : ALTER TABLE ABCDE ADD CONSTRAINT uq_ROLLNUM UNIQUE CLUSTERED (ROLLNUM)
DROPPING : ALTER TABLE ABCDE DROP CONSTRAINT UQ_ROLLNUM
check
NEW TABLE : CREATE TABLE ABCDEF (SL INT, AMOUNT INT, CONSTRAINT ch_AMOUNT CHECK(AMOUNT>5))
ON EXISTING : ALTER TABLE ABCDEF ADD CONSTRAINT CH_AMOUNT CHECK(AMOUNT>5)
DROPPING : ALTER TABLE ABCDEF DROP CONSTRAINT CH_AMOUNT
default
CREATE TABLE ABCDEFF (SL INT, MARKS INT DEFAULT 35)
Null / not null
Class 3 - Brief about T-SQL
Brief about TSQL
DDL – DATA DEFINITION LANGUAGE
DML – DATA MANIPULATION LANGUAGE
DCL – DATA CONTROL LANGUAGE
TCL – TRANSACT CONTROL LANGUAGE
DDL – CREATE, ALTER, DROP, TRUNCATE
Eg : CREATE DATABASE SampleDatabase
ON
( NAME = MyDatabase,
FILENAME = ‘C:\program files\MyData\mydatabasefile.mdf’,
SIZE = 10MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1MB)
LOG ON
( NAME = MyDatabase_LOG,
FILENAME = ‘C:\program files\MyData\mydatabaselog.ldf’,
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
Eg :
CREATE TABLE MyTable (
Field1 int PRIMARY KEY,
Field2 char(10) NOT NULL),
Field3 datetime
)
Eg : ALTER DATABASE SampleDatabase
ADD FILE
(
NAME = MyDatabase1,
FILENAME =’c:\program files\MyData\mydatabasefile2.ndf’,
SIZE = 10MB,
MAXSIZE = 50MB,
FILEGROWTH = 1MB
)
Eg : ALTER TABLE MyTable ADD Field4 VARCHAR(10) NULL
ALTER TABLE MyTable DROP COLUMN Field4
Eg : DROP DATABASE SampleDatabase
Eg : DROP TABLE MyTable
DML – SELECT, INSERT, UPDATE, DELETE
Eg : _ SELECT @@lNGUAGE displays the name of your SQL Server language.
_ SELECT @@SERVERNAME displays the name of the SQL Server for the current connection.
_ SELECT @@VERSION displays information about Microsoft SQL Server version, build, edition, and so on.
_ SELECT @@TRANCOUNT displays the number of open transactions for the current connection.
_ SELECT @@ERROR displays an error number giving
SELECT :
Select * from [ABCD]
INSERT :
Insert into ABCD [ColumnA, ColumnB] values 5, 6
Insert into ABCD values 5,6
DELETE
Delete from ABCD where ColumnA = 5
UPDATE
Update ABCD set ColumnA = 8 where ColumnA = 5
DCL – GRANT, REVOKE
CREATE LOGIN [venky] WITH PASSWORD=N'111'
CREATE USER [venky] FOR LOGIN [venky]
EXEC sp_addrolemember N'db_owner', N'venky'
DROP LOGIN [venky]
Grant select to venkat
Revoke select to venkat
TCL – COMMIT, ROLLBACK
Commit
Rollback
Class 2 - Data Types
DATA TYPES :
Exact Numerics
bigint
decimal
int
numeric
smallint
money
tinyint
smallmoney
bit
Approximate Numerics
float
real
Date and Time
datetime
smalldatetime
Character Strings
char
text
varchar
Unicode Character Strings
nchar
ntext
nvarchar
Binary Strings
binary
image
varbinary
Other Data Types
cursor
timestamp
sql_variant
uniqueidentifier
table
xml
See Also
New Datatypes :
Varchar(max)
Nvarchar(max)
Varbinary(max)
xml
Exact-number data types that use integer data.
Data type Range Storage
bigint -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807) 8 Bytes
int -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647) 4 Bytes
smallint -2^15 (-32,768) to 2^15-1 (32,767) 2 Bytes
tinyint 0 to 255 1 Byte
decimal and numeric (Transact-SQL)
Numeric data types that have fixed precision and scale.
decimal[ (p[ , s] )] and numeric[ (p[ , s] )]
Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. The SQL-92 synonyms for decimal are dec and dec(p, s). numeric is functionally equivalent to decimal.
p (precision)
The maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision of 38. The default precision is 18.
s (scale)
The maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through p. Scale can be specified only if precision is specified. The default scale is 0; therefore, 0 <= s <= p. Maximum storage sizes vary, based on the precision. Precision Storage bytes 1 – 9 5 10-19 9 20-28 13 29-38 17 money and smallmoney (Transact-SQL) Data types that represent monetary or currency values. Data type Range Storage money -922,337,203,685,477.5808 to 922,337,203,685,477.5807 8 bytes smallmoney - 214,748.3648 to 214,748.3647 4 bytes bit (Transact-SQL) An integer data type that can take a value of 1, 0, or NULL. float and real (Transact-SQL) Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly. Note: The SQL-92 synonym for real is float(24). Data type Range Storage float - 1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308 Depends on the value of n real - 3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38 4 Bytes Date and Time (Transact-SQL) Are data types that are used for representing the date and the time of day. datetime and smalldatetime Represent the date and the time of day. Data type Range Accuracy datetime January 1, 1753, through December 31, 9999 3.33 milliseconds smalldatetime January 1, 1900, through June 6, 2079 1 minute char and varchar (Transact-SQL) Are character data types of either fixed length or variable length. char [ ( n ) ] Fixed-length, non-Unicode character data with a length of n bytes. n must be a value from 1 through 8,000. The storage size is n bytes. The SQL-2003 synonym for char is character. varchar [ ( n max ) ] Variable-length, non-Unicode character data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of data entered + 2 bytes. The data entered can be 0 characters in length. The SQL-2003 synonyms for varchar are char varying or character varying. nchar and nvarchar (Transact-SQL) Character data types that are either fixed-length, nchar, or variable-length, nvarchar, Unicode data and use the UNICODE UCS-2 character set. nchar [ ( n ) ] Fixed-length Unicode character data of n characters. n must be a value from 1 through 4,000. The storage size is two times n bytes. The SQL-2003 synonyms for nchar are national char and national character. nvarchar [ ( n max ) ] Variable-length Unicode character data. n can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size, in bytes, is two times the number of characters entered + 2 bytes. The data entered can be 0 characters in length. The SQL-2003 synonyms for nvarchar are national char varying and national character varying. ntext, text, and image (Transact-SQL) Important: ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead. For more information, see Using Large-Value Data Types.
Fixed and variable-length data types for storing large non-Unicode and Unicode character and binary data. Unicode data uses the UNICODE UCS-2 character set.
ntext
Variable-length Unicode data with a maximum length of 2^30 - 1 (1,073,741,823) characters. Storage size, in bytes, is two times the number of characters entered. The SQL-2003 synonym for ntext is national text.
text
Variable-length non-Unicode data in the code page of the server and with a maximum length of 2^31-1 (2,147,483,647) characters. When the server code page uses double-byte characters, the storage is still 2,147,483,647 bytes. Depending on the character string, the storage size may be less than 2,147,483,647 bytes.
image
Variable-length binary data from 0 through 2^31-1 (2,147,483,647) bytes.
binary and varbinary (Transact-SQL)
Binary data types of either fixed length or variable length.
binary [ ( n ) ]
Fixed-length binary data with a length of n bytes, where n is a value from 1 through 8,000. The storage size is n bytes.
varbinary [ ( n max) ]
Variable-length binary data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of the data entered + 2 bytes. The data that is entered can be 0 bytes in length. The SQL-2003 synonym for varbinary is binary varying.
cursor (Transact-SQL)
A data type for variables or stored procedure OUTPUT parameters that contain a reference to a cursor. Any variables created with the cursor data type are nullable.
The operations that can reference variables and parameters having a cursor data type are:
The DECLARE @local_variable and SET @local_variable statements.
The OPEN, FETCH, CLOSE, and DEALLOCATE cursor statements.
Stored procedure output parameters.
The CURSOR_STATUS function.
The sp_cursor_list, sp_describe_cursor, sp_describe_cursor_tables, and sp_describe_cursor_columns system stored procedures.
Sql_variant (Transact-SQL)
A data type that stores values of various SQL Server 2005-supported data types, except text, ntext, image, timestamp, and sql_variant.
uniqueidentifier (Transact-SQL)
Is a 16-byte GUID.
Remarks
A column or local variable of uniqueidentifier data type can be initialized to a value in the following ways:
By using the NEWID function.
By converting from a string constant in the form xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, in which each x is a hexadecimal digit in the range 0-9 or a-f. For example, 6F9619FF-8B86-D011-B42D-00C04FC964FF is a valid uniqueidentifier value.
xml (Transact-SQL)
Updated: 5 December 2005
Is the data type that stores XML data. You can store xml instances in a column, or a variable of xml type. For more information, see xml Data Type.
Select * from systypes
Exact Numerics
bigint
decimal
int
numeric
smallint
money
tinyint
smallmoney
bit
Approximate Numerics
float
real
Date and Time
datetime
smalldatetime
Character Strings
char
text
varchar
Unicode Character Strings
nchar
ntext
nvarchar
Binary Strings
binary
image
varbinary
Other Data Types
cursor
timestamp
sql_variant
uniqueidentifier
table
xml
See Also
New Datatypes :
Varchar(max)
Nvarchar(max)
Varbinary(max)
xml
Exact-number data types that use integer data.
Data type Range Storage
bigint -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807) 8 Bytes
int -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647) 4 Bytes
smallint -2^15 (-32,768) to 2^15-1 (32,767) 2 Bytes
tinyint 0 to 255 1 Byte
decimal and numeric (Transact-SQL)
Numeric data types that have fixed precision and scale.
decimal[ (p[ , s] )] and numeric[ (p[ , s] )]
Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. The SQL-92 synonyms for decimal are dec and dec(p, s). numeric is functionally equivalent to decimal.
p (precision)
The maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision of 38. The default precision is 18.
s (scale)
The maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through p. Scale can be specified only if precision is specified. The default scale is 0; therefore, 0 <= s <= p. Maximum storage sizes vary, based on the precision. Precision Storage bytes 1 – 9 5 10-19 9 20-28 13 29-38 17 money and smallmoney (Transact-SQL) Data types that represent monetary or currency values. Data type Range Storage money -922,337,203,685,477.5808 to 922,337,203,685,477.5807 8 bytes smallmoney - 214,748.3648 to 214,748.3647 4 bytes bit (Transact-SQL) An integer data type that can take a value of 1, 0, or NULL. float and real (Transact-SQL) Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly. Note: The SQL-92 synonym for real is float(24). Data type Range Storage float - 1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308 Depends on the value of n real - 3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38 4 Bytes Date and Time (Transact-SQL) Are data types that are used for representing the date and the time of day. datetime and smalldatetime Represent the date and the time of day. Data type Range Accuracy datetime January 1, 1753, through December 31, 9999 3.33 milliseconds smalldatetime January 1, 1900, through June 6, 2079 1 minute char and varchar (Transact-SQL) Are character data types of either fixed length or variable length. char [ ( n ) ] Fixed-length, non-Unicode character data with a length of n bytes. n must be a value from 1 through 8,000. The storage size is n bytes. The SQL-2003 synonym for char is character. varchar [ ( n max ) ] Variable-length, non-Unicode character data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of data entered + 2 bytes. The data entered can be 0 characters in length. The SQL-2003 synonyms for varchar are char varying or character varying. nchar and nvarchar (Transact-SQL) Character data types that are either fixed-length, nchar, or variable-length, nvarchar, Unicode data and use the UNICODE UCS-2 character set. nchar [ ( n ) ] Fixed-length Unicode character data of n characters. n must be a value from 1 through 4,000. The storage size is two times n bytes. The SQL-2003 synonyms for nchar are national char and national character. nvarchar [ ( n max ) ] Variable-length Unicode character data. n can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size, in bytes, is two times the number of characters entered + 2 bytes. The data entered can be 0 characters in length. The SQL-2003 synonyms for nvarchar are national char varying and national character varying. ntext, text, and image (Transact-SQL) Important: ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead. For more information, see Using Large-Value Data Types.
Fixed and variable-length data types for storing large non-Unicode and Unicode character and binary data. Unicode data uses the UNICODE UCS-2 character set.
ntext
Variable-length Unicode data with a maximum length of 2^30 - 1 (1,073,741,823) characters. Storage size, in bytes, is two times the number of characters entered. The SQL-2003 synonym for ntext is national text.
text
Variable-length non-Unicode data in the code page of the server and with a maximum length of 2^31-1 (2,147,483,647) characters. When the server code page uses double-byte characters, the storage is still 2,147,483,647 bytes. Depending on the character string, the storage size may be less than 2,147,483,647 bytes.
image
Variable-length binary data from 0 through 2^31-1 (2,147,483,647) bytes.
binary and varbinary (Transact-SQL)
Binary data types of either fixed length or variable length.
binary [ ( n ) ]
Fixed-length binary data with a length of n bytes, where n is a value from 1 through 8,000. The storage size is n bytes.
varbinary [ ( n max) ]
Variable-length binary data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of the data entered + 2 bytes. The data that is entered can be 0 bytes in length. The SQL-2003 synonym for varbinary is binary varying.
cursor (Transact-SQL)
A data type for variables or stored procedure OUTPUT parameters that contain a reference to a cursor. Any variables created with the cursor data type are nullable.
The operations that can reference variables and parameters having a cursor data type are:
The DECLARE @local_variable and SET @local_variable statements.
The OPEN, FETCH, CLOSE, and DEALLOCATE cursor statements.
Stored procedure output parameters.
The CURSOR_STATUS function.
The sp_cursor_list, sp_describe_cursor, sp_describe_cursor_tables, and sp_describe_cursor_columns system stored procedures.
Sql_variant (Transact-SQL)
A data type that stores values of various SQL Server 2005-supported data types, except text, ntext, image, timestamp, and sql_variant.
uniqueidentifier (Transact-SQL)
Is a 16-byte GUID.
Remarks
A column or local variable of uniqueidentifier data type can be initialized to a value in the following ways:
By using the NEWID function.
By converting from a string constant in the form xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, in which each x is a hexadecimal digit in the range 0-9 or a-f. For example, 6F9619FF-8B86-D011-B42D-00C04FC964FF is a valid uniqueidentifier value.
xml (Transact-SQL)
Updated: 5 December 2005
Is the data type that stores XML data. You can store xml instances in a column, or a variable of xml type. For more information, see xml Data Type.
Select * from systypes
Class 1 - Introduction of SQL Server
Overview of Database Concepts
A database is a structured collection of records of data. Its group os objects.
The first database management systems were developed in the 1960s. A pioneer in the field was Charles Bachman
First it was in the form of Files.
Later created like Foxpro, Dbase.
The relational model was proposed by E. F. Codd in 1970.
The rules
Rule 0: The system must qualify as relational, as a database, and as a management system.
For a system to qualify as a relational database management system (RDBMS), that system must use its relational facilities (exclusively) to manage the database.
Rule 1: The information rule:
All information in the database is to be represented in one and only one way, namely by values in column positions within rows of tables.
Rule 2: The guaranteed access rule:
All data must be accessible with no ambiguity. This rule is essentially a restatement of the fundamental requirement for primary keys. It says that every individual scalar value in the database must be logically addressable by specifying the name of the containing table, the name of the containing column and the primary key value of the containing row.
Rule 3: Systematic treatment of null values:
The DBMS must allow each field to remain null (or empty). Specifically, it must support a representation of "missing information and inapplicable information" that is systematic, distinct from all regular values (for example, "distinct from zero or any other number," in the case of numeric values), and independent of data type. It is also implied that such representations must be manipulated by the DBMS in a systematic way.
Rule 4: Active online catalog based on the relational model:
The system must support an online, inline, relational catalog that is accessible to authorized users by means of their regular query language. That is, users must be able to access the database's structure (catalog) using the same query language that they use to access the database's data.
Rule 5: The comprehensive data sublanguage rule:
The system must support at least one relational language that
(a) Has a linear syntax
(b) Can be used both interactively and within application programs,
(c) Supports data definition operations (including view definitions), data manipulation operations (update as well as retrieval), security and integrity constraints, and transaction management operations (begin, commit, and rollback).
Rule 6: The view updating rule:
All views that are theoretically updatable must be updatable by the system.
Rule 7: High-level insert, update, and delete:
The system must support set-at-a-time insert, update, and delete operators. This means that data can be retrieved from a relational database in sets constructed of data from multiple rows and/or multiple tables. This rule states that insert, update, and delete operations should be supported for any retrievable set rather than just for a single row in a single table.
Rule 8: Physical data independence:
Changes to the physical level (how the data is stored, whether in arrays or linked lists etc.) must not require a change to an application based on the structure.
Rule 9: Logical data independence:
Changes to the logical level (tables, columns, rows, and so on) must not require a change to an application based on the structure. Logical data independence is more difficult to achieve than physical data independence.
Rule 10: Integrity independence:
Integrity constraints must be specified separately from application programs and stored in the catalog. It must be possible to change such constraints as and when appropriate without unnecessarily affecting existing applications.
Rule 11: Distribution independence:
The distribution of portions of the database to various locations should be invisible to users of the database. Existing applications should continue to operate successfully :
(a) when a distributed version of the DBMS is first introduced; and
(b) when existing distributed data are redistributed around the system.
Rule 12: The nonsubversion rule:
If the system provides a low-level (record-at-a-time) interface, then that interface cannot be used to subvert the system, for example, bypassing a relational security or integrity constraint.
During the 1980s, research activity focused on distributed database systems and database machines.
In the 1990s, attention shifted to object-oriented databases. These had some success in fields where it was necessary to handle more complex data than relational systems could easily cope with, such as spatial databases, engineering data (including software repositories), and multimedia data.
In the 2000s, the fashionable area for innovation is the XML database. As with object databases, this has spawned a new collection of start-up companies, but at the same time the key ideas are being integrated into the established relational products.
Some of DBMS Products :
ADABAS
BerkeleyDB
dBase
IBM DB2
Informix
Microsoft Access
Microsoft SQL Server
MySQL
Oracle Database
Paradox (database)
Sybase
Teradata
MS SQL SERVER
Microsoft SQL Server is a relational database management system (RDBMS) produced by Microsoft. Its primary query language is Transact-SQL, an implementation of the ANSI/ISO standard Structured Query Language (SQL) used by both Microsoft and Sybase.
For each databse query languge will use :
Oracle : PL/SQL
Sqlserver : T-Sql
Architecture
The architecture of Microsoft SQL Server is broadly divided into three components: SQLOS which implements the basic services required by SQL Server, including thread scheduling, memory management and I/O management; the Relational Engine, which implements the relational database components including support for databases, tables, queries and stored procedures as well as implementing the type system; and the Protocol Layer which exposes the SQL Server functionality.[1]
SQLOS
SQLOS is the base component in the SQL Server architecture. It implements functions normally associated with the Operating System - thread scheduling, memory management, I/O management, buffer pool management, resource management, synchronization primitives and locking, and deadlock detection.
Relational engine
The Relational engine implements the relational data store using the capabilities provided by SQLOS, which is exposed to this layer via the private SQLOS API.
Protocol layer
Protocol layer implements the external interface to SQL Server.
SQL Server 2005
SQL Server 2005, released in November 2005, is the successor to SQL Server 2000. It included native support for managing XML data, in addition to relational data.
SQL Server 2008
The next version of SQL Server is SQL Server 2008,[29] code-named "Katmai",[30] slated to launch on February 27, 2008 and release (RTM) in Q3 2008.[31][32] The most recent CTP was made available on February 19, 2008. SQL Server 2008 aims[33] to make data management self-tuning, self organizing, and self maintaining with the development of SQL Server Always On technologies, to provide near-zero downtime. SQL Server 2008 will also include support for structured and semi-structured data, including digital media formats for pictures, audio, video and other multimedia data. In current versions, such multimedia data can be stored as BLOBs (binary large objects), but they are generic bitstreams. Intrinsic awareness of multimedia data will allow specialized functions to be performed on them. According to Paul Flessner, senior Vice President, Server Applications, Microsoft Corp., SQL Server 2008 can be a data storage backend for different varieties of data: XML, email, time/calendar, file, document, spatial, etc as well as perform search, query, analysis, sharing, and synchronization across all data types.[30]
Tools
SQLCMD
SQLCMD is a command line application that comes with Microsoft SQL Server, and exposes the management features of SQL Server. It allows SQL queries to be written and executed from the command prompt.
Visual Studio
Microsoft Visual Studio includes native support for data programming with Microsoft SQL Server. It can be used to write and debug code to be executed by SQL CLR.
SQL Server Management Studio is a GUI tool included with SQL Server 2005 and later for configuring, managing, and administering all components within Microsoft SQL Server.
Business Intelligence Development Studio
Business Intelligence Development Studio (BIDS) is the IDE from Microsoft used for developing data analysis and Business Intelligence solutions utilizing the Microsoft SQL Server Analysis Services, Reporting Services and Integration Services.
SQL Server Release HistoryVersion Year Release Name Codename
1.0 1989 SQL Server 1.0 -
4.21 1993 SQL Server
6.0 1995 SQL Server
6.5 1996 SQL Server 6.5 Hydra
7.0 1998 SQL Server 7.0 Sphinx-
1999 SQL Server 7.0
OLAP Tools
8.0 2000 SQL Server 2000 Shiloh
8.0 2003 SQL Server 2000 64-bit Edition Liberty
9.0 2005 SQL Server 2005 Yukon
10.0 2008 SQL Server 2008 Katmai
SQL SERVER CLASS
Hi All,
I have prepared this class material for the learners who are new to SQL Server. By studying this you can gain knowledge in SQL Server. Actually I have take SQL Server 2000 for preparing this class materials, because I felt like it would be better first learn the older versions then come to newer. Hope it will helpful for everyone who are new to SQL Server.
Follow my lession in the Blog :
I have prepared this class material for the learners who are new to SQL Server. By studying this you can gain knowledge in SQL Server. Actually I have take SQL Server 2000 for preparing this class materials, because I felt like it would be better first learn the older versions then come to newer. Hope it will helpful for everyone who are new to SQL Server.
Follow my lession in the Blog :
Thursday, July 2, 2009
SQL SERVER 2008 NEW DATA TYPES
NEW DATA TYPES in SQL Server 2008
• Date and Time: Four new date and time data types have been added, making working with time much easier than it ever has in the past. They include: DATE, TIME, DATETIME2, and DATETIMEOFFSET.
• Spatial: Two new spatial data types have been added GEOMETRY and GEOGRAPHY which you can use to natively store and manipulate location-based information, such as Global Positioning System (GPS) data.
• HIERARCHYID: The HIERARCHYID data type is used to enable database applications to model hierarchical tree structures, such as the organization chart of a business.
• FILESTREAM: FILESTREAM is not a data type as such, but is a variation of the
VARBINARY(MAX) data type that allows unstructured data to be stored in the file system
instead of inside the SQL Server database. Because this option requires a lot of involvement
from both the DBA administration and development side.
…. Working on each data type practical, I will post with examples once I finished.
• Date and Time: Four new date and time data types have been added, making working with time much easier than it ever has in the past. They include: DATE, TIME, DATETIME2, and DATETIMEOFFSET.
• Spatial: Two new spatial data types have been added GEOMETRY and GEOGRAPHY which you can use to natively store and manipulate location-based information, such as Global Positioning System (GPS) data.
• HIERARCHYID: The HIERARCHYID data type is used to enable database applications to model hierarchical tree structures, such as the organization chart of a business.
• FILESTREAM: FILESTREAM is not a data type as such, but is a variation of the
VARBINARY(MAX) data type that allows unstructured data to be stored in the file system
instead of inside the SQL Server database. Because this option requires a lot of involvement
from both the DBA administration and development side.
…. Working on each data type practical, I will post with examples once I finished.
Wednesday, July 1, 2009
FIND LIST OF JOBS AND ITS DETAILS IN SQL SERVER
-- JOBWISE INFORMATION
select distinct a.name JOBNAME, isnull(DESCRIPTION,'No description available') DESCRIPTION,c.Name Category, isnull (b.database_name,'None') DATABASE_NAME, isnull(f.name, 'None') Job_Owner,
case when d.name is null then 'No Schedule' else d.name end Schedule,
isnull (case d.freq_type
when '1 ' then 'Once'
when '4' then 'Daily'
when '8' then 'Weekly'
when '16' then 'Monthly'
when '32' then 'Monthly relative'
when '64' then 'When SQL Server Agent starts' end, 'None') as Frequency,
isnull (case d.freq_interval
when '1' then 'None'
when '2' then 'Monday'
when '4' then 'Tuesday'
when '8' then 'Wednesday'
when '16' then 'Thursday'
when '32' then 'Friday'
when '64' then 'Saturday'
end,'None') as DAY,
case when active_start_time < 120000 then
isnull(left(convert(varchar,convert(decimal,D.Active_start_time)/10000),4) + ' AM','None') else
isnull(left(convert(varchar,convert(decimal,D.Active_start_time)/10000-12),4) + ' PM','None') END
JOB_start_time,
isnull (convert (varchar,d.Date_Created), 'None') Created_Date from sysjobs a
Inner join sysjobsteps b on
a.job_id = b.job_id
left outer join syscategories c on a.category_id = c.category_id
left outer join master.dbo.syslogins f on a.Owner_sid = f.sid
left outer join sysjobschedules e on e.job_id = a.job_id
left outer join sysschedules d on e.schedule_id = d.schedule_id
order by a.name
--JOB STEP WISE INFORMATION
select distinct a.name JOBNAME, isnull(DESCRIPTION,'No description available') DESCRIPTION,c.Name Category, isnull (b.database_name,'None') DATABASE_NAME, isnull(f.name,'None') Job_Owner,
b.step_id STEP, b.step_name STEPNAME,b.subsystem TYPE,b.command,
case when d.name is null then 'No Schedule' else d.name end Schedule,
isnull (case d.freq_type
when '1 ' then 'Once'
when '4' then 'Daily'
when '8' then 'Weekly'
when '16' then 'Monthly'
when '32' then 'Monthly relative'
when '64' then 'When SQL Server Agent starts' end, 'None') as Frequency,
isnull (case d.freq_interval
when '1' then 'None'
when '2' then 'Monday'
when '4' then 'Tuesday'
when '8' then 'Wednesday'
when '16' then 'Thursday'
when '32' then 'Friday'
when '64' then 'Saturday'
end,'None') as DAY,
case when active_start_time < 120000 then
isnull(left(convert(varchar,convert(decimal,D.Active_start_time)/10000),4) + ' AM','None') else
isnull(left(convert(varchar,convert(decimal,D.Active_start_time)/10000-12),4) + ' PM','None') END
JOB_start_time,
isnull (convert (varchar,d.Date_Created), 'None') Created_Date from sysjobs a
left outer join sysjobsteps b on
a.job_id = b.job_id
left outer join syscategories c on a.category_id = c.category_id
left outer join master.dbo.syslogins f on a.Owner_sid = f.sid
left outer join sysjobschedules e on e.job_id = a.job_id
left outer join sysschedules d on e.schedule_id = d.schedule_id
order by a.name
Subscribe to:
Posts (Atom)