3. Skills
1. The use of 1=1, 1=2 is used more often when combining SQL statements.
"where 1=1" means selecting all "where 1=2" and not selecting all.
like:
if @strWhere !=''
begin
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere
end
else
begin
set @strSQL = 'select count(*) as Total from [' + @tblName + ']'
end
We can write it directly
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 Stabilize '+ @strWhere 2. Shrink the database
--Reconstruct the index
DBCC REINDEX
DBCC INDEXDEFRAG
--Shrink data and logs
DBCC SHRINKDB
DBCC SHRINKFILE
3. Compress the database
dbcc shrinkdatabase(dbname)
4. Transfer the database to new users with existing user permissions
exec sp_change_users_login 'update_one','newname','oldname'
go
5. Check the backup set
RESTORE VERIFYONLY from disk='E:'
6. Repair the database
ALTER DATABASE [dvbbs] SET SINGLE_USER
GO
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK
GO
ALTER DATABASE [dvbbs] SET MULTI_USER
GO
7. Log clearing
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT
USE tablename -- The database name to operate on
SELECT @LogicalFileName = 'tablename_log', -- log file name
@MaxMinutes = 10, -- Limit on time allowed to wrap log.
@NewSize = 1 -- The size of the log file you want to set (M)
Setup / initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size
FROM sysfiles
WHERE name = @LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)
DECLARE @Counter INT,
@StartTime DATETIME,
@TruncLog VARCHAR(255)
SELECT @StartTime = GETDATE(),
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'
DBCC SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
-- Wrap the log if necessary.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)
AND (@OriginalSize * 8 /1024) > @NewSize
BEGIN -- Outer loop.
SELECT @Counter = 0
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
BEGIN -- update
INSERT DummyTrans VALUES ('Fill Log') DELETE DummyTrans
SELECT @Counter = @Counter + 1
END
EXEC (@TruncLog)
END
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
DROP TABLE DummyTrans
SET NOCOUNT OFF
8. Description: Change a table
exec sp_changeobjectowner 'tablename','dbo'
9. Store all tables
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch
@OldOwner as NVARCHAR(128),
@NewOwner as NVARCHAR(128)
AS
DECLARE @Name as NVARCHAR(128)
DECLARE @Owner as NVARCHAR(128)
DECLARE @OwnerName as NVARCHAR(128)
DECLARE curObject CURSOR FOR
select 'Name' = name,
'Owner' = user_name(uid)
from sysobjects
where user_name(uid)=@OldOwner
order by name
OPEN curObject
FETCH NEXT FROM curObject INTO @Name, @Owner
WHILE(@@FETCH_STATUS=0)
BEGIN
if @Owner=@OldOwner
begin
set @OwnerName = @OldOwner + '.' + rtrim(@Name)
exec sp_changeobjectowner @OwnerName, @NewOwner
end
-- select @name,@NewOwner,@OldOwner
FETCH NEXT FROM curObject INTO @Name, @Owner
END
close curObject
deallocate curObject
GO
10. Directly write data in SQL SERVER
declare @i int
set @i=1
while @i<30
begin
insert into test (userid) values(@i)
set @i=@i+1
end
Case:
There is a table below, requiring that all the results that have not passed in the frame will be based on the increase of 0.1 each time, so that they will pass the test:
Name score
Zhangshan 80
Lishi 59
Wangwu 50
Songquan 69
while((select min(score) from tb_table)<60)
begin
update tb_table set score =score*1.01
where score<60
if (select min(score) from tb_table)>60
break
else
continue
end
Data Development-Classic
1. Sort by last name stroke:
Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as //From less to more
2. Database encryption:
select encrypt('original password')
select pwdencrypt('original password')
select pwdcompare('original password','encrypted password') = 1--same; otherwise it is not the same encrypt('original password')
select pwdencrypt('original password')
select pwdcompare('original password','encrypted password') = 1--same; otherwise it is not the same
3. Retrieve the fields in the table:
declare @list varchar(1000),
@sql nvarchar(1000)
select @list=@list+','+ from sysobjects a,syscolumns b where = and ='Table A'
set @sql='select '+right(@list,len(@list)-1)+' from Table A'
exec (@sql)
4. Check the hard disk partition:
EXEC master..xp_fixeddrives
5. Compare whether tables A and B are equal:
if (select checksum_agg(binary_checksum(*)) from A)
=
(select checksum_agg(binary_checksum(*)) from B)
print 'Equal'
else
print 'unequal'
6. Kill all event probe processes:
DECLARE hcforeach CURSOR GLOBAL FOR SELECT 'kill '+RTRIM(spid) FROM
WHERE program_name IN('SQL profiler',N'SQL profiler')
EXEC sp_msforeach_worker '?'
7. Record search:
Starting to N records
Select Top N * From table
-------------------------------
N to M records (must have the main index ID)
Select Top M-N * From table Where ID in (Select Top M ID From table) Order by ID Desc
----------------------------------
N to end record
Select Top N * From Table Order by ID Desc
Case
For example 1: A table has more than 10,000 records, and the first field of the table, RecID, is a self-growth field. Write an SQL statement to find the 31st to 40th records of the table.
select top 10 recid from A where recid not in(select top 30 recid from A)
Analysis: If you write this way, some problems will arise if the recid has a logical index in the table.
select top 10 recid from A where... is searched from the index, and the subsequent select top 30 recid from A is searched in the data table. This way, because the order in the index may be inconsistent with the data table, the query is not the original data you want to obtain.
Solution
1. Use order by select top 30 recid from A order by ricid If the field does not grow by itself, a problem will occur
2. Also add the condition in that subquery: select top 30 recid from A where recid>-1
Example 2: The last record in the query table is not known, and the table structure is not known in total.
set @s = 'select top 1 * from T where pid not in (select top ' + str(@count-1) + ' pid from T)'
print @s exec sp_executesql @s
8: Get all user tables in the current database
select Name from sysobjects where xtype='u' and status>=0
9: Get all fields in a certain table
select name from syscolumns where id=object_id('table name')
select name from syscolumns where id in (select id from sysobjects where type = 'u' and name = 'table name')
The effect of both methods is the same
10: View views, stored procedures, and functions related to a certain table
select a.* from sysobjects a, syscomments b where = and like '%table name%'
11: View all stored procedures in the current database
select name as stored procedure name from sysobjects where xtype='P'
12: Query all databases created by users
select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name='sa')
or
select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x01
13: Query the fields and data types of a certain table
select column_name,data_type from information_schema.columns
where table_name = 'table name'
14: Data operations between different server databases
--Create a link server
exec sp_addlinkedserver 'ITSV', '', 'SQLOLEDB', 'remote server name or ip address'
exec sp_addlinkedsrvlogin 'ITSV', 'false',null, 'username', 'password'
--Query example
select * from ITSV.Database name.dbo.Table name
--Import Example
select * into table from ITSV.Database name.dbo.Table name
--Delete the linked server when it is no longer used in the future
exec sp_dropserver 'ITSV ', 'droplogins '
--Connect remote/local network data (openrowset/openquery/opendatasource)
--1、openrowset
--Query example
select * from openrowset( 'SQLOLEDB ', 'sql server name '; 'user name '; 'password ', database name.dbo. table name)
-- Generate the local table
select * into table from openrowset( 'SQLOLEDB ', 'sql server name '; 'user name '; 'password ', database name.dbo. table name)
--Import the local table to the remote table
insert openrowset( 'SQLOLEDB ', 'sql server name '; 'user name '; 'password ', database name.dbo. table name)
select *from local table
--Update the local table
update b
set b.Column A=a.Column A
from openrowset( 'SQLOLEDB ', 'sql server name '; 'user name '; 'password ', database name.dbo. table name) as a inner join local table b
on a.column1=b.column1
--openquery usage requires creating a connection
--First create a connection to create a link server
exec sp_addlinkedserver 'ITSV', '', 'SQLOLEDB', 'remote server name or ip address'
--Query
select *
FROM openquery(ITSV, 'SELECT * FROM database.dbo.table name')
--Import the local table to the remote table
insert openquery(ITSV, 'SELECT * FROM database.dbo.table name')
select * from local table
--Update the local table
update b
set b.Column B=a.Column B
FROM openquery(ITSV, 'SELECT * FROM database.dbo.table name') as a
inner join local table b on a. Column A=b. Column A
--3、opendatasource/openrowset
SELECT *
FROM opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=login name;Password=password ' )..roy_ta
--Import the local table to the remote table
insert opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=login name;Password=password ').Database.dbo.Table name
select * from local table
SQL Server basic functions
SQL Server basic functions
1. String function for length and analysis
1,datalength(Char_expr) returns the string contains the number of characters, but does not contain the following spaces
2,substring(expression,start,length) Take a substring, the subscript of the string is from "1", start is the starting position, and length is the length of the string. In actual application, its length is obtained by len(expression)
3, right(char_expr,int_expr) Returns the int_expr character on the right side of the string, and uses left to the opposite
4, isnull( check_expression , replacement_value ) If check_expression is empty, the value of replacement_value is returned. If it is not empty, the check_expression character operation class will be returned.
5.Sp_addtype Custom data type
For example: EXEC sp_addtype birthday, datetime, 'NULL'
6,set nocount {on|off} makes the returned result not contain information about the number of rows affected by the Transact-SQL statement. If some statements contained in the stored procedure do not return much actual data, this setting can significantly improve performance due to a substantial reduction in network traffic. The SET NOCOUNT setting is set at execution or runtime, not at analysis. When SET NOCOUNT is ON, no count is returned (indicating the number of rows affected by the Transact-SQL statement).
When SET NOCOUNT is OFF, return the count