sql server 基础知识

TempTable临时表

临时表是在TempDB中创建的表。临时表的名称都以“#”开头。

临时表的范围为创建临时表的连接。因为，临时表不能在两个连接之间共享，一旦连接关闭，临时表就会被丢弃。

如果临时表被创建于存储过程之中，则临时表的范围在存储过程之中，或者被该存储过程调用的任何存储过程之中。如果需要在连接之间共享临时表，则需要使用全局的临时表。

全局的临时表以“##”符号开头，它将一直存在于数据库中，直到SQL Server重新启动。一旦这类临时表创建之后，所有的用户都可以访问到。

在临时表上不能明确地指明权限。 临时表提供了存储中间结果的能力。有时候，临时表还能通过将一个复杂的查询分解成两个查询而获得性能的改善。

这可以通过首先将第一个查询的结果存在临时表中，然后在第二个查询中使用临时表来实现。当一个大表中的某个子集在一个在座过程中使用多次时，建议使用临时表。

在这种情况下，在临时表中保持数据的子集，以在随后的连接中使用，这样能大大改善性能。还可以在临时表中创建索引。

Scope of temporary tables in SQL Server

解答1

From CREATE TABLE:

Local temporary tables are visible only in the current session

and (more importantly):

If a local temporary table is created in a stored procedure or application that can be executed at the same time by several users, the Database Engine must be able to distinguish the tables created by the different users [sic - almost certainly this should say sessions not users]. The Database Engine does this by internally appending a numeric suffix to each local temporary table name.

Which exactly rebuts the point of whoever said that they would be shared.

---

Also, there's no need to DROP TABLE at the end of your procedure (from same link again):

A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished

解答2

## is used for global temporary tables - will be available to the different imports.

# is used for local temporary tables and only available in the current/inner scope. 

解答3

One session cannot see another session's temporary tables. So different imports will not affect each other, regardless of whether you use temporary tables or table variables.

The exception is global temporary tables, which start with ##. Those are visible to all connections.

Temporary Table Scope?

解答1

First, local temporary tables created within a procedure are dropped once the procedure finishes. From the BOL on Create Table:

A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process that called the stored procedure that created the table.

If your data access code is properly opening a connection, calling a stored procedure and then closing the connection, the temp table is created in the procedure is effectively destroyed.

I say "effectively" to bring up another point. I would not recommend dropping the temp table at the end of your procedure although I would add a check just before I created the temp table and drop it if exists (e.g. if object_id('tempdb..#Foo') is not null). The argument against dropping the temp table at the end is that by calling the Drop statement, you are forcing SQL Server to expend resources to destroy the table then and there while you wait for your procedure to end. If instead, you let it go out of scope, your procedure ends immediately and you let SQL Server destroy the table at a time of its own choosing.

解答2

The #Temp table is limited scope to YOUR SESSION and lifespan of the batch, meaning nobody else can see your temp table and anyone else can create their own #Temp table with the same name. Once your session or batch ends, SQL Server will clean up the temp table.

On another note the ##Temp table behaves like a normal table. Everyone can see it, and there cannot be more than 1 ##Temp table with the same name. SQL Server will clean these ##Temp tables when the server restarts.

补充

This is not completely accurate. Temp tables are NOT limited to the scope of the batch -- they survive for the entire session regardless of batches. That is, you can create a temp table, use GO, and the temp table will still exist with its data.

 

sp_executesql 无法识别表变量

https://stackoverflow.com/questions/8040105/execute-sp-executesql-for-select-into-table-but-cant-select-out-temp-table-d

Local temporary table #table_name is visible in current session only, global temporary ##table_name tables are visible in all sessions.

Both lives until their session is closed.

sp_executesql - creates its own session (maybe word "scope" would be better) so that's why it happens.

 

拼接sql的dynamic sql无法识别表变量，但是可以识别临时表。传参的dynamic sql，既无法识别表变量，也无法识别临时表。

 

 

 

SET ANSI_NULLS

SET ANSI_NULLS ON----------------标准要求在对空值进行等于 (=) 或不等于 (<>) 比较时取值为 FALSE。

当 SET ANSI_NULLS 为 ON 时，即使 column_name 中包含空值，使用 WHERE column_name = NULL 的 SELECT 语句仍返回零行。

即使 column_name 中包含非空值，使用 WHERE column_name <> NULL 的 SELECT 语句仍会返回零行。

 

isnull的用法

select isnull(studentid,100) from student_info

　　解释：这个实例的意思就是查出student_info表中字段为studentid的值，如果字段studentid值为空就在字段studentid中添加100，或者你可以动态的向这个空字段添加值。

 

SET QUOTED_IDENTIFIER

SET QUOTED_IDENTIFIER ON---------当 SET QUOTED_IDENTIFIER 为 ON 时，标识符可以由双引号分隔，而文字必须由单引号分隔。

 

 

 

 

存储过程在Management Studio中的位置

 

如何查看存储过程的内容

下面的黑体字是存储过程的名字

EXEC sp_helptext N'cl_srd_SearchRedemptionOrderDetail';  

 

使用SSDT调试存储过程

https://msdn.microsoft.com/en-us/library/hh272701(v=vs.103).aspx

工具的下载地址https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt

 

select * from vie_cti_23 这个可能是表，也可能是视图

 

如何执行存储过程

使用sql prompt

直接输入 exec，然后输入存储过程名字的时候，如果sql prompt找到了，直接enter键，sql prompt会自动补全

使用sql server自带的功能

先找到存储过程，然后右键，Execute Stored Procedure，在弹出的界面输入参数

之后sql server会自动生成以下的语句

USE [d_lisa_Program_dev01_v4300]
GO

DECLARE    @return_value int,
        @ScheduleID int

EXEC    @return_value = [dbo].[ps_fud_QueryTask]
        @CurrentTime = NULL,
        @ScheduleID = @ScheduleID OUTPUT

SELECT    @ScheduleID as N'@ScheduleID'

SELECT    'Return Value' = @return_value

GO

 

查看存储过程的依赖项

可以查看谁依赖存储过程，也可以查看存储过程依赖哪些对象

 

 

Functions

 @@Error

https://docs.microsoft.com/en-us/sql/t-sql/functions/error-transact-sql

Returns the error number for the last Transact-SQL statement executed.

Remarks

Returns 0 if the previous Transact-SQL statement encountered no errors.

Returns an error number if the previous statement encountered an error. If the error was one of the errors in the sys.messages catalog view, then @@ERROR contains the value from the sys.messages.message_id column for that error. You can view the text associated with an @@ERROR error number in sys.messages.

Because @@ERROR is cleared and reset on each statement executed, check it immediately following the statement being verified, or save it to a local variable that can be checked later.

Use the TRY...CATCH construct to handle errors. The TRY...CATCH construct also supports additional system functions (ERROR_LINE, ERROR_MESSAGE, ERROR_PROCEDURE, ERROR_SEVERITY, and ERROR_STATE) that return more error information than @@ERROR. TRY...CATCH also supports an ERROR_NUMBER function that is not limited to returning the error number in the statement immediately after the statement that generated an error. For more information, see TRY...CATCH (Transact-SQL).  

 

 SCOPE_IDENTITY

 https://docs.microsoft.com/en-us/sql/t-sql/functions/scope-identity-transact-sql

 Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, if two statements are in the same stored procedure, function, or batch, they are in the same scope.

Remarks

  SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions because they return values that are inserted into identity columns.

IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the value generated for a specific table in any session and any scope. For more information, see IDENT_CURRENT (Transact-SQL).

SCOPE_IDENTITY and @@IDENTITY return the last identity values that are generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.

For example, there are two tables, T1 and T2, and an INSERT trigger is defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 by the trigger.

Assuming that both T1 and T2 have identity columns, @@IDENTITY and SCOPE_IDENTITY return different values at the end of an INSERT statement on T1. @@IDENTITY returns the last identity column value inserted across any scope in the current session. This is the value inserted in T2. SCOPE_IDENTITY() returns the IDENTITY value inserted in T1. This was the last insert that occurred in the same scope. The SCOPE_IDENTITY() function returns the null value if the function is invoked before any INSERT statements into an identity column occur in the scope.

Failed statements and transactions can change the current identity for a table and create gaps in the identity column values. The identity value is never rolled back even though the transaction that tried to insert the value into the table is not committed. For example, if an INSERT statement fails because of an IGNORE_DUP_KEY violation, the current identity value for the table is still incremented.  

 

IDENT_CURRENT (Transact-SQL)

https://docs.microsoft.com/en-us/sql/t-sql/functions/ident-current-transact-sql 

Returns the last identity value generated for a specified table or view. The last identity value generated can be for any session and any scope.

IDENT_CURRENT( 'table_name' )  

 

 

 OBJECT_ID

https://docs.microsoft.com/en-us/sql/t-sql/functions/object-id-transact-sql

 Returns the database object identification number of a schema-scoped object.

 Note: Objects that are not schema-scoped, such as DDL triggers, cannot be queried by using OBJECT_ID. For objects that are not found in the sys.objects catalog view, obtain the object identification numbers by querying the appropriate catalog view. For example, to return the object identification number of a DDL trigger, use SELECT OBJECT_ID FROM sys.triggers WHERE name = 'DatabaseTriggerLog``'.  

判断临时表是否存在，如果存在，就drop掉

  IF OBJECT_ID('tempdb..#temp') IS NOT NULL
            DROP TABLE #temp;

 

 

 

 REPLACE

https://docs.microsoft.com/en-us/sql/t-sql/functions/replace-transact-sql

Replaces all occurrences of a specified string value with another string value.

REPLACE ( string_expression , string_pattern , string_replacement )  

 

@@TRANCOUNT (Transact-SQL)

 https://docs.microsoft.com/en-us/sql/t-sql/functions/trancount-transact-sql

Returns the number of BEGIN TRANSACTION statements that have occurred on the current connection.

 

System stored procedures

sp_getapplock

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-getapplock-transact-sql

Places a lock on an application resource.

sp_getapplock [ @Resource = ] 'resource_name' ,  
     [ @LockMode = ] 'lock_mode'   
     [ , [ @LockOwner = ] 'lock_owner' ]   
     [ , [ @LockTimeout = ] 'value' ]  
     [ , [ @DbPrincipal = ] 'database_principal' ]  
[ ; ]  

DECLARE @res INT
    EXEC @res = sp_getapplock                 
                @Resource = 'CustomTableCheckLock',
                @LockMode = 'Exclusive',
                @LockOwner = 'Transaction',
                @LockTimeout = 10000,
                @DbPrincipal = 'public'
    IF @res NOT IN (0, 1)
    BEGIN       
        Return -1
    END 

 

Locks placed on a resource are associated with either the current transaction or the current session.

Locks associated with the current transaction are released when the transaction commits or rolls back.

Locks associated with the session are released when the session is logged out. When the server shuts down for any reason, all locks are released.

The lock resource created by sp_getapplock is created in the current database for the session. Each lock resource is identified by the combined values of:

• The database ID of the database containing the lock resource.

• The database principle specified in the @DbPrincipal parameter.

• The lock name specified in the @Resource parameter.

Only a member of the database principal specified in the @DbPrincipal parameter can acquire application locks that specify that principal. Members of the dbo and db_owner roles are implicitly considered members of all roles.

 

 

 

Return Code Values

>= 0 (success), or < 0 (failure)

Value	Result
0	The lock was successfully granted synchronously.
1	The lock was granted successfully after waiting for other incompatible locks to be released.
-1	The lock request timed out.
-2	The lock request was canceled.
-3	The lock request was chosen as a deadlock victim.
-999	Indicates a parameter validation or other call error.