SQL Server Learning Memos

mssql-

Agreement:
Shared memory: The most efficient, in-memory operation, can be used for this machine, with. or (local) on the login
Named pipes:winodows with Windows Communication protocol, machine name communication, generally not used
TCP-IP: Accessing the server via Ip address

Data type for more details please refer to http://www.cnblogs.com/zhangwei595806165/archive/2012/02/23/2364746.html

The data type of the field:
Image can be used to store images in binary data storage, but the picture is inefficient to put into the database.

Stored as text:
char (1-8000) fixed-length character data, space-padded, less than five characters best use char
Varchar (1-8000) fixed-length characters, characters with few spaces, high performance
Nchar (1-4000) fixed length, two bytes stored one character, space change time
Nvarchar (1-8000) variable long character data, characters are not padded, two bytes are stored in one character but search is inefficient
varchar (max)/nvarchar (max): The text that stores big data, before 2005 with Text,text cannot use fuzzy query

Accurate Numeric storage:
Bit:0/1/null
Int: four bytes to store one data
BigInt: larger and wider than int, eight bytes stores one data.

Date type

Datetime:1753.1.1~9999.12.31, which represents a complete date type
Datatime2: The earliest can even trace back to the Chinese civilization 5,000 years .... Most use DateTime is Enough
Time: Hours: minutes: seconds primary Storage

T-SQL Build table:

Create DATABASE name
on[(
Name= ',
Size=.;
Filename= ' absolute path '
)]

CREATE TABLE Table name (
Whether the field name type is nullable (default is empty) constraint
）

---------------
Constraints:
PRIMARY KEY constraint: cannot be null primary key
FOREIGN KEY constraint: foreign key foreign key columns and columns are best. Referecnces Associated table (field)
Unique constraint: Unique
Self-increment: Identifies the settings in seed and identity increments.
Check constraints: Check, checks can be checked in C # programs, and setting up checks in the database consumes efficiency.
Default constraint: Defaults (' Default value ')
Non-empty constraint: NOT NULL, default is NULL
-----------------------

The order of execution of SQL--first find table, group, query data.

------------
Crud:
Add: INSERT into table name (column name) VALUES ()//values require corresponding column names in the order
Delete: Delete from table name ...
Change: Update table name set column name = value ...
Check: Select field from table name ...

Aggregation functions:
Sum: Fetch and
AVG: Averaging
Count: Number of calculations
Min/max: Ask for maximum or minimum

Sort: Front top keyword, order by column name desc (descending) Default ascending ASC
To repeat: Distinct select distinct field from ... Determine if the field is repeated and repeat for all subsequent columns
Group By column name Select followed by a field or aggregate function following group by

SQL Server Learning Memos