SQL Server Section Learning materials

Create a database domain link

EXEC sp_addlinkedserver

@server = ' Caron ',-----aliases

@srvproduct = ' ',

@provider = ' SQLOLEDB ',

@datasrc = '. ' ----Server Name

Login Domain Link

exec sp_addlinkedsrvlogin ' Caron ', ' false ', NULL, ' sa ', ' Sasa '

--The following two commands are to remove the linked server

Exec sp_droplinkedsrvlogin Test,null

Exec sp_dropserver Test

1. Date Time function

Select DATEADD (Day, 3, getDate ());--Gatian

Select DATEADD (year, 3, GetDate ());--Gagnin

Select DATEADD (Hour, 3, getDate ());--Extra hours

--Returns the number of date boundaries and time boundaries across two specified dates

Select DateDiff (Day, ' 2011-06-20 ', getDate ());

--the difference of seconds

Select DateDiff (Second, ' 2011-06-22 11:00:00 ', getDate ());

--Difference of hours

Select DateDiff (Hour, ' 2011-06-22 10:00:00 ', getDate ());

Select Datename (Month, getDate ());--Current month

Select Datename (minute, getDate ());--Current minute

Select Datename (Weekday, getDate ());-Current Week

Select DatePart (Month, getDate ());--Current month

Select DatePart (Weekday, getDate ());-Current Week

Select DatePart (Second, getDate ());--current number of seconds

Select Day (GetDate ());--Returns the current date number of days

Select Day (' 2011-06-30 ');--Returns the current date days

Select Month (GetDate ());--Returns the current date month

Select month (' 2011-11-10 ');

Select year (GetDate ());--Returns the current date years

Select year (' 2010-11-10 ');

Select GetDate ();--Current system date

Select getUTCDate ();--UTC Date

2. Mathematical functions

Select Pi ();--pi function

Select rand (+), rand (+), rand (), Rand ();--Random number

Select Round (rand (), 3), round (rand (100), 5);--Precise decimal digits

--exact number of digits, negative numbers indicate before decimal point

Select Round (123.456, 2), round (254.124,-2);

Select Round (123.4567, 1, 2);

3. Meta-data

Select Col_name (object_id (' Student '), 1);--Return column name

Select Col_name (object_id (' Student '), 2);

--The column data type length

Select Col_length (' Student ', Col_name (object_id (' Student '), 2));

--The column data type length

Select Col_length (' Student ', Col_name (object_id (' Student '), 1));

--return type name, type ID

Select Type_name (type_id (' varchar ')), type_id (' varchar ');

--Return column type length

Select ColumnProperty (object_id (' Student '), ' name ', ' PRECISION ');

--Returns the index where the column is located

Select ColumnProperty (object_id (' Student '), ' sex ', ' ColumnId ');

4. String functions

Select ASCII (' a ');--Character conversion ASCII value

Select ASCII (' A ');

Select char,--ascii value conversion character

Select char (65);

Select NCHAR (65);

Select NCHAR (45231);

Select NCHAR (32993);--unicode convert characters

Select Unicode (' A '), Unicode (' Medium ');--return Unicode encoded value

Select Soundex (' Hello '), Soundex (' World '), soundex (' word ');

Select Patindex ('%a ', ' ta '), Patindex ('%ac% ', ' Jack '), Patindex (' dex% ', ' dexjack ');--match character index

Select ' A ' + space (2) + ' B ', ' C ' + space (5) + ' d ';--Output spaces

Select CharIndex (' o ', ' Hello World ');--Lookup index

Select CharIndex (' o ', ' Hello World ', 6);--Lookup index

Select QuoteName (' Abc[]def '), QuoteName (' 123]45 ');

--Exact numbers

Select STR (123.456, 2), str (123.456, 3), str (123.456, 4);

Select STR (123.456, 9, 2), str (123.456, 9, 3), str (123.456, 6, 1), str (123.456, 9, 6);

Select difference (' Hello ', ' HelloWorld ');--Compare strings same

Select difference (' Hello ', ' world ');

Select difference (' Hello ', ' Llo ');

Select difference (' Hello ', ' hel ');

Select difference (' Hello ', ' hello ');

Select replace (' Abcedef ', ' e ', ' e ');--replace string

Select Stuff (' Hello World ', 3, 4, ' ABC ');--Specify position substitution string

Select Replicate (' abc# ', 3);--repeating string

Select SubString (' abc ', 1, 1), subString (' abc ', 1, 2), subString (' Hello Wrold ', 7, 5);--intercept string

Select Len (' abc ');--Return length

5. Security function

Select Current_User;

Select User;

Select USER_ID (), user_id (' dbo '), user_id (' Public '), user_id (' guest ');

Select USER_NAME (), user_name (1), user_name (0), user_name (2);

Select Session_user;

Select suser_id (' sa ');

Select Suser_sid (), Suser_sid (' sa '), suser_sid (' sysadmin '), Suser_sid (' serveradmin ');

Select Is_member (' dbo '), Is_member (' Public ');

Select Suser_name (), Suser_name (1), Suser_name (2), suser_name (3);

Select SUSER_SNAME (), SUSER_SNAME (0x01), SUSER_SNAME (0x02), SUSER_SNAME (0x03);

Select Is_srvrolemember (' sysadmin '), Is_srvrolemember (' serveradmin ');

Select Permissions (object_id (' student '));

Select System_user;

Select schema_id (), schema_id (' dbo '), schema_id (' guest ');

Select Schema_name (), schema_name (1), schema_name (2), schema_name (3);

Select reverse (' SQL Server ');--Reverse string

Select Left (' leftstring ', 4);--Take the string

Select Left (' leftstring ', 7);

Select Right (' leftstring ', 6);--Take the left-hand string

Select Right (' leftstring ', 3);

Select LOWER (' abc '), Lower (' abc ');--lowercase

Select UPPER (' abc '), Upper (' abc ');--Uppercase

--Remove left space

Select LTrim (' abc '), LTrim (' # abc# '), LTrim (' abc ');

--Remove the right space

Select RTrim (' abc '), RTrim (' # abc# '), RTrim (' abc ');

6. System functions

Select App_name ();--the application name of the current session

Select CAST (as DateTime), cast (' as Money '), cast (' 0 ' as varbinary);--type conversion

Select CONVERT (DateTime, ' 2011 ');--type conversion

Select COALESCE (null, ' a '), coalesce (' 123 ', ' a ');--Returns the first non-empty expression in its argument

Select Collationproperty (' Traditional_spanish_cs_as_ks_ws ', ' CodePage ');

Select current_timestamp;--Current Timestamp

Select Current_User;

Select IsDate (GetDate ()), IsDate (' abc '), IsNumeric (1), IsNumeric (' a ');

Select Datalength (' abc ');

Select host_id ();

Select HOST_NAME ();

Select Db_name ();

Select Ident_current (' Student '), Ident_current (' classes ');--Returns the maximum value of the primary key ID

Select IDENT_INCR (' Student '), IDENT_INCR (' classes '); increment value of--id

Select Ident_seed (' Student '), Ident_seed (' classes ');

SELECT @ @identity;--The last self-increment value

Select identity (int, 1, 1) As ID into tab from student;--the martyred of the Studeng table, create a tab in/1 self-increment form

SELECT * from tab;

SELECT @ @rowcount;--affects the number of rows

SELECT @ @cursor_rows;--Returns the number of currently qualified rows for the cursor open on the connection

SELECT @ @error;--t-sql Error number

SELECT @ @procid;

7. Configuration function

Set Datefirst 7;--Sets the first day of the week, indicating Sunday

SELECT @ @datefirst as ' the first day of the Week ', DATEPART (DW, getDate ()) as ' Today is the week ';

SELECT @ @dbts;--Returns the current database unique timestamp

Set language ' Italian ';

SELECT @ @langId as ' Language ID ';--return language ID

SELECT @ @language as ' language name ';--Returns the current language name

SELECT @ @lock_timeout;--Returns the current lock timeout setting (in milliseconds) for the current session

SELECT @ @max_connections;--Returns the maximum number of simultaneous user connections allowed for an instance of SQL Server

SELECT @ @MAX_PRECISION as ' MAX PRECISION ';--Returns the level of precision used for the decimal and numeric data types

SELECT @ @SERVERNAME; the name of the local server for--sql server

SELECT @ @SERVICENAME;--Service name

SELECT @ @SPID;--current session process ID

SELECT @ @textSize;

SELECT @ @version;--Current database version information

8. System Statistic function

SELECT @ @CONNECTIONS;--Number of connections

SELECT @ @PACK_RECEIVED;

SELECT @ @CPU_BUSY;

SELECT @ @PACK_SENT;

SELECT @ @TIMETICKS;

SELECT @ @IDLE;

SELECT @ @TOTAL_ERRORS;

SELECT @ @IO_BUSY;

SELECT @ @TOTAL_READ;--Read disk count

SELECT @ @PACKET_ERRORS;-The number of network packet errors that occurred

SELECT @ @TOTAL_WRITE; number of disk writes performed by--sqlserver

Select PatIndex ('%soft% ', ' Microsoft SQL Server ');

Select PatIndex (' soft% ', ' Software SQL Server ');

Select PatIndex ('%soft ', ' SQL Server Microsoft ');

Select PatIndex ('%so_gr% ', ' Jsonisprogram ');

This article is from the "trust yourself I can do" blog, please be sure to keep this source http://7515975.blog.51cto.com/7505975/1745884

SQL Server Section Learning materials