system_70.txt

一套三层结构的文件管理OA系统源码

Private Declare Function SetProcessWorkingSetSize Lib 
"kernel32.dll" (ByVal hProcess As IntPtr, ByVal dwMinimumWorkingSetSize 
As Int32, 
ByVal dwMaximumWorkingSetSize As Int32) As Int32

    Public Sub ReduceMemoryUsage()
        GC.Collect()
        GC.Collect()
        
SetProcessWorkingSetSize(Diagnostics.Process.GetCurrentProcess.Handle, 
-1, -1)
    End Sub
B. 使用一般语法查找标识值中的差距
下面的示例显示一般的语法，当删除数据时，可以使用该语法查找标识值中的差距。
说明  下面的 Transact-SQL 
脚本中的第一部分只用作示范说明。可以运行以下面的注释开始的 Transact-SQL 脚本：- - Create the img table.
-- Here is the generic syntax for finding identity value gaps in data.
-- This is the beginning of the illustrative example.
SET IDENTITY_INSERT tablename ON

DECLARE @minidentval column_type
DECLARE @nextidentval column_type
SELECT @minidentval = MIN(IDENTITYCOL) FROM tablename
IF @minidentval = IDENT_SEED('tablename')
   SELECT @nextidentval = MIN(IDENTITYCOL) + IDENT_INCR('tablename')
   FROM tablename t1
   WHERE IDENTITYCOL BETWEEN IDENT_SEED('tablename') AND 
      MAX(column_type) AND
      NOT EXISTS (SELECT * FROM tablename t2
         WHERE t2.IDENTITYCOL = t1.IDENTITYCOL + 
            IDENT_INCR('tablename'))
ELSE
   SELECT @nextidentval = IDENT_SEED('tablename')
SET IDENTITY_INSERT tablename OFF
-- Here is an example to find gaps in the actual data.
-- The table is called img and has two columns: the first column 
-- called id_num, which is an increasing identification number, and the 
-- second column called company_name.
-- This is the end of the illustration example.

-- Create the img table.
-- If the img table already exists, drop it.
-- Create the img table.
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 
      WHERE TABLE_NAME = 'img')
   DROP TABLE img
GO
CREATE TABLE img (id_num int IDENTITY(1,1), company_name sysname)
INSERT img(company_name) VALUES ('New Moon Books')
INSERT img(company_name) VALUES ('Lucerne Publishing')
-- SET IDENTITY_INSERT ON and use in img table.
SET IDENTITY_INSERT img ON

DECLARE @minidentval smallint
DECLARE @nextidentval smallint
SELECT @minidentval = MIN(IDENTITYCOL) FROM img
 IF @minidentval = IDENT_SEED('img')
    SELECT @nextidentval = MIN(IDENTITYCOL) + IDENT_INCR('img')
    FROM img t1
    WHERE IDENTITYCOL BETWEEN IDENT_SEED('img') AND 32766 AND
      NOT    EXISTS (SELECT * FROM img t2
          WHERE t2.IDENTITYCOL = t1.IDENTITYCOL + IDENT_INCR('img'))
 ELSE
    SELECT @nextidentval = IDENT_SEED('img')
SET IDENTITY_INSERT img OFF
注释
任何时候，会话中只有一个表的 IDENTITY_INSERT 属性可以设置为 
ON。如果某个表已将此属性设置为 ON，并且为另一个表发出了 SET IDENTITY_INSERT ON 
语句，则 Microsoft? SQL Server? 返回一个错误信息，指出 SET IDENTITY_INSERT 
已设置为 ON 并报告此属性已设置为 ON 的表。
如果插入值大于表的当前标识值，则 SQL Server 
自动将新插入值作为当前标识值使用。
SET IDENTITY_INSERT 的设置是在执行或运行时设置，而不是在分析时设置。
权限
执行权限默认授予 sysadmin 固定服务器角色和 db_owner 及 db_ddladmin 
固定数据库角色以及对象所有者。
示例
下例创建一个含有标识列的表，并显示如何使用 SET IDENTITY_INSERT 
设置填充由 DELETE 语句导致的标识值中的空隙。

-- Create products table.
CREATE TABLE products (id int IDENTITY PRIMARY KEY, product 
varchar(40))
GO
-- Inserting values into products table.
INSERT INTO products (product) VALUES ('screwdriver')
INSERT INTO products (product) VALUES ('hammer')
INSERT INTO products (product) VALUES ('saw')
INSERT INTO products (product) VALUES ('shovel')
GO
-- Create a gap in the identity values.
DELETE products 
WHERE product = 'saw'
GO

SELECT * 
FROM products
GO

-- Attempt to insert an explicit ID value of 3;
-- should return a warning.
INSERT INTO products (id, product) VALUES(3, 'garden shovel')
GO
-- SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT products ON
GO

-- Attempt to insert an explicit ID value of 3
INSERT INTO products (id, product) VALUES(3, 'garden shovel').
GO

SELECT * 
FROM products
GO
-- Drop products table.
DROP TABLE products
GO