Instance T-SQL Miscellaneous (you will benefit a lot)

7. linked server, OpenRowSet Function

/**//*********************************** ****
Function: Query Remote Server objects.
Author:
Created:
Modified on:
Note:
Reference; Keyword: linked server, OpenRowSet Function
****************************************/
-- Method 1: Use the linked server, fixed.
Use [Master]
Go
Declare @ sqlserver varchar (50)
Set @ sqlserver = n'ipaddress'
Exec master. DBO. sp_addmediaserver @ Server = n'link _ test', @ srvproduct = @ sqlserver,
@ Provider = n' sqloledb', @ datasrc = @ sqlserver, @ provstr = n' uid = login; Pwd = password ;'
Exec master. DBO. sp_addmediasrvlogin @ rmtsrvname = n'link _ test', @ useself = n'false', @ locallogin = n'sa ', @ rmtuser = n'login ', @ rmtpassword = 'Password'
Exec master. DBO. sp_serveroption @ Server = n' link _ test', @ optname = n' RPC ', @ optvalue = n' true'
Exec master. DBO. sp_serveroption @ Server = n' link _ test', @ optname = n' RPC out', @ optvalue = n' true'
-- Exec master. DBO. sp_dropserver @ Server = 'server', @ droplogins = 'droplogins'
Select top 1 * From link_test.logincard.dbo.zldept -- Test Query

-- To modify the columns in the linked server table, you must specify the Link name.
Exec ('alter table yourdbname. DBO. tablea add field varchar (5); ') at yourlinkservername

 

-- Method 2: Use the OpenRowSet function instead (temporary)
-- SQL Login
Select top 1 A. * From OpenRowSet ('sqloledb', 'ipaddress'; 'login'; 'Password', northwind. DBO. Orders)
-- Integrierte Sicherheit
Select * From OpenRowSet ('sqloledb ',
'Server = IPaddress; database = dbname; trusted_connection = yes ;',
'Select top 1 * From txcard. DBO. zldept ')

-- Or use the OpenDataSource connection method
-- Integrierte Sicherheit
Select top 1 * From OpenDataSource ('sqloledb ',
'Data source = 192.168.117.20; initial catalog = txcard; Integrated Security = sspi; '). northwind. DBO. Orders
-- SQL Login
Select * From OpenDataSource ('sqloledb ',
'Data source = itrainbo2000; initial catalog = northwind; user id = student; Password = # student #; '). northwind. DBO. Orders

More import and export data view: http://blog.csdn.net/zhou__zhou/archive/2007/06/08/1644638.aspx

6. Return the inverse of a value, such as Gender male = 1 and female = 0. You can use case when... to save your life.

5. In sql2005, how to force Delete the Extended Stored Procedure xp_mongoshell

-- 1. Get the directory location of the master database (write down this directory)
Select
Left (physical_name, Len (physical_name)-10)
From master. SYS. database_files
Where type = 0
-- 2. Open the preceding directory in the operating system and find the following two files:
Mssqlsystemresource. MDF
Mssqlsystemresource. LDF
Copy them and change them:
_ Mssqlsystemresource. MDF
_ Mssqlsystemresource. LDF
-- 3. Return to SQL Server and execute the following statement (the directory in the statement should be changed to the directory found in step 1)
Use master
-- Modify the following file directory as your own
Create Database _ sys_resource
On (
Filename = n'd: Program filesmicrosoft SQL servermssql.1mssqldata _ mssqlsystemresource. MDF'
)
Log On (
Filename = n' D: Program filesmicrosoft SQL servermssql.1mssqldata _ mssqlsystemresource. ldf'
)
For attach
Go
Use _ sys_resource
Drop proc SYS. xp_cmdshell
Go
Exec sp_detach_db n '_ sys_resource'
-- The SQL statement takes effect only after it is restarted. The following statement stops the SQL statement.
Shutdown
Go
-- 4. Go back to the directory in step 2 and rename the following two files for backup.
Mssqlsystemresource. MDF
Mssqlsystemresource. LDF
-- 5. Rename the following two files and remove the previous _
_ Mssqlsystemresource. MDF
_ Mssqlsystemresource. LDF
-- 6. Restart SQL Server and execute xp_cmdshell. You will find that the stored procedure cannot be found.
1. Why is Union all faster than union?
Solution: If you still remember, the union operation combines two datasets. It does not generate duplicate or redundant rows. To achieve this, you need to perform the sort operation on the two tables. This sort operation is obviously computation intensive and uses a large amount of memory. On the contrary, Union all only combines the data of two sets randomly, regardless of whether the rows are repeated.

2. What is the difference between intersect and union?
A: intersect is the non-repeating value (intersection) of both queries, and union is the non-repeating value (union) of the two query results)

3. How to insert stored procedure results to a temporary table.

Declare @ path varchar (1000)
Set @ Path = 'C :\'
Declare @ a table (str0 varchar (100 ))
Insert into @ A (str0) exec xp_subdirs 'C :\'
Select * From @ A where isdate (str0) = 1
Tip: you must first know the table result structure returned by the stored procedure and create the same temporary table result in advance.

4. What is the use of guest users in SQL?
The Guest user account allows login without a user account to access the database. When all of the following conditions are met, the logon is identified by the guest user:
1) You have the permission to access the Microsoft SQL server instance, but you are not authorized to access the database through your own user account.
2) The database contains the Guest user account.
The permission can be applied to the guest user, just as it is any other user account. You can add or delete Guest users in all databases except master and tempdb (which must always exist in these two databases. By default, the new database does not have a guest user account.

For example, to add a guest user account to a database named accounts, run the following code in the SQL query Analyzer:
Use accounts
Go
Execute sp_grantdbaccess guest

13 -- calculate the date of the employee's recent birthday. Take the northwind database as an example.
Select firstname, lastname, birthdate, case when dateadd (YY, datediff (YY, birthdate, getdate (), birthdate) <= getdate () Then dateadd (YY, datediff (YY, birthdate, getdate (), birthdate) else dateadd (YY, datediff (YY, birthdate, getdate ()-1, birthdate) end [last birthday] from employees
-- Idea: You need to get the date of this year's Birthday first. If the obtained date is later than the current date, you will get the last date (last year's date, who will have a birthday once a year .), if the value is less than or equal to the current date, this time is the latest birthday date.
-- Note: because of the leap year, the birthday may be different in February.

12. sort by strokes of Chinese Characters
Select * from [youtable]
Order by [field] collate chinese_prc_stroke_ci_as_ks
11. Learn about the new ideas for solving the problem from this example.
/** // * There is a two-dimensional table with the following data:
Name course Mark
------------------------------------------
Li Si mathematics 90
Li Si language 76
Wang Wu English 100
Wang Wu language 81
James math 75
Zhang San Language 81
Requirement: query the names of students whose scores are greater than 80 in each course.
Result: Wang Wu
*/
-- Method 1: Not exists
Select name from @ test a where not exists (select 1 from @ test where a. Course = course and Mark <80)
-- Another idea
Select name from @ test group by name having count (*) = sum (case when Mark> = 80 then 1 else 0 end)
10, full-width half-width conversion function. create function dbo. ufnconvert (
@ STR nvarchar (4000), -- string to be converted
@ Flag bit -- Conversion flag. 0 is converted to halfwidth, and 1 is converted to fullwidth.
) Returns nvarchar (4000)
/**//*******************************
Function: returns a full-width conversion function.
Created on:
Example:
Declare @ S1 varchar (8000)
Select @ S1 = 'medium 2-3456a78stuvabn China opwxyz'
Select DBO. ufnconvert (@ S1, 0), DBO. ufnconvert (@ S1, 1)
Reference: network Abstract.
*/
As
Begin
Declare @ Pat nvarchar (8), @ Step int, @ I int, @ SPC int
If @ flag = 0
Select @ PAT = n' % [! -~] % ', @ Step =-65248,
@ STR = Replace (@ STR, n'', n '')
Else
Select @ PAT = n' % [! -~] % ', @ Step = 65248,
@ STR = Replace (@ STR, n'', n '')
Set @ I = patindex (@ Pat collate latin1_general_bin, @ Str)
While @ I> 0
Select @ STR = Replace (@ STR, substring (@ STR, @ I, 1), nchar (Unicode (substring (@ STR, @ I, 1 )) + @ Step), @ I = patindex (@ Pat collate latin1_general_bin, @ Str)
Return (@ Str)
End
Go
-- Result:
Halfwidth
Medium 2-3456, a78s, tuvabn China opwxyz medium 2-3456, a78s, tuvabn China opwxyz
9. Delete only records with different IDs in table. Condition, record with the largest ID is retained.
Declare @ t table (ID int, str1 varchar (10), str2 varchar (10), str3 varchar (10 ))
Insert into @ t
Select 1, '11', '22', '33'
Union
Select 3, '11', '22', '33'
Union
Select 2, '11', '22', '33'
Union
Select 4, '11', '22', '33'
-- Not exists
Select * From @ t a where not exists (select ID from @ t where a. ID <ID and str1 = A. [str1])
-- Not in
Select * From @ t where id not in
(Select a. ID from @ t a, @ T B
Where a. str1 = B. str1 and A. ID <B. ID)
8. delete records that do not exist in Table B in table.
Delete from a where not exists (select * from B where a. keyid = B. keyid)
7. How to use SQL statements to create an automatically numbered field
Create Table mytable (obj_id int identity (1, 1) not null)
6. regenerate the value-added ID column (use the old ID column as a condition)
Select ID, (ID-(select Min (ID) from a) + 1) as new_id from
5. query results of the random sorting table. Use the newid () function
This method scans the entire table, generates a computing column, and sorts it again. It is best to perform this operation on a million record table, otherwise it will be slow.
Select * from Table order by newid ()

4. Check whether the database exists and perform further operations. Other objects are similar,
For example, table: If object_id ('[DBO]. [Table]', 'U') is not null
If db_id (N 'dbname') is not null
Print 'db exists'
Else
Print 'db not exists' or: If exists (select * from Master .. sysdatabases where name = 'dbname)
Print 'db exists'
Else
Print 'db not exists'
3. After deleting all the records in the table, the ID still starts from 1.
-- Method 1:
Truncate table yourtable -- in this way, not only the data is divided, but also the identity allocation field can be reset.

-- Method 2:
Delete from yourtable
DBCC checkident (yourtable, reseed, 0) -- reset the identity encoding field so that the value starts from 1.

2. view the table structure
Exec sp_mshelpcolumns 'object _ name' more references undisclosed stored procedures

1. rename a column:
Sp_rename 'a table. Column name B ', 'column name changed to C', 'column' For more information, see online help: change the name of the user-created object sp_rename

0. select the most appropriate data type for database tables.
The following is an example: define a new table to track users' behaviors of purchasing goods from an online store. Appropriate names, data types, and possible null options should be considered for each column. Because there are strict requirements for storage, we try to use conservative data types. The following table design guidelines:

* Define a single column as the customer name. It can be 1 to 200 characters long and must be filled in. Some customers may use foreign names.
* Four columns are required to store the customer's address, city, state, and zip code. All addresses and cities only contain local names in the United States, and none of these columns must have a value. The address column can contain a maximum of 200 characters, and the city can contain 100 characters. The State is always a 2-byte abbreviation, while the zip code is always 5 bytes in length.
* One column is required to store the number of purchased items. This column stores an integer with a maximum value of 10 000 and the new record must be automatically set to 1.
* One column is required to store the amount paid. This is a required field. The maximum storage value is $100 000.
My Requirement Analysis: Although I used to think that primary key columns should be defined, This is not required. The customername column is defined as nvarchar, so that Unicode characters can be stored using international character sets. Although this will double the storage requirements, the most likely high efficiency meets the requirements.
Address0 and city columns do not require Unicode capabilities, so they use varchar data types. The State column uses a fixed length type because the overhead associated with the varchar type is at least 2 bytes, even if the column is null. The zipcode column is either defined as char (5) or varchar (5). Saving a smaller value of the char type can slightly improve the performance, but even if it is null, char also occupies 5 bytes of storage space. Although the char type may be more practical in this example, we need to reduce the storage space. Although the smallmoney type is not commonly used, it can reduce the storage space and meet the needs of the maximum value. The problem is as follows:

Create Table mytable (customername nvarchar (200) not null
, Address0 varchar (200) null
, City varchar (100) null
, State0 char (2) null
, Zipcode varchar (5)
, Quantity int not null default 1
, Price smallmoney not null
, Constraint ck_state0 check (state0 in ('wa ',' or '))
, Constraint ck_quantity check (quantity between 1 and 10000)
, Constraint ck_price check (price between $0 and $100000 ))

Declare @ TB table (sex int, num2 INT)
Insert into @ TB select 1, 9
Insert into @ TB select 0, 0
Insert into @ TB select 0, 1
Insert into @ TB select 1, 9
Insert into @ TB select 0, 1
Insert into @ TB select 1, 1
Insert into @ TB select 0, 9
Insert into @ TB select 1, 1
Insert into @ TB select 0, 9
Insert into @ TB select 1, 1

Select sex, sex fetch inverse = sex ^ 1, num2, num2 fetch inverse = num2 ^ 9 from @ TB

 

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/zhou__zhou/archive/2007/07/24/1705545.aspx