Author Archives: 惜分飞

创建sql server分区表

Posted on by

1、创建数据库

USE Master 
GO
CREATE DATABASE Test_Partitioning
ON PRIMARY
(NAME='Partitioning_1',
FILENAME=
'E:\database\partitions\Partitioning_1.mdf',
SIZE=4,
MAXSIZE=100,
FILEGROWTH=1 ),
FILEGROUP FG2
(NAME = 'Partitioning_2',
FILENAME =
'E:\database\partitions\Partitioning_2.mdf',
SIZE = 4,
MAXSIZE=100,
FILEGROWTH=1 ),
FILEGROUP FG3
(NAME = 'Partitioning_3',
FILENAME =
'E:\database\partitions\Partitioning_3.mdf',
SIZE = 4,
MAXSIZE=100,
FILEGROWTH=1 )
GO

2、创建分区函数

Use test_Partitioning 
GO
CREATE PARTITION FUNCTION salesYearPartitions (datetime)
AS RANGE RIGHT FOR VALUES ( '2009-01-01', '2010-01-01')
GO

说明：
RIGHT:表示”=”在右边
LEFT:表示”=”在左边

3、创建分区方案

Use test_Partitioning 
GO
CREATE PARTITION SCHEME Test_PartitionScheme
AS PARTITION salesYearPartitions
TO ([PRIMARY], FG2, FG3 )
GO

4、使用分区创建表

Use test_Partitioning 
GO
CREATE TABLE SalesArchival
(SaleTime datetime PRIMARY KEY,
ItemName varchar(50))
ON Test_PartitionScheme (SaleTime);
GO

5、验证SQL语句
5.1)确定文件组的数量和数据库数据文件的数量

Use test_Partitioning 
GO
-- Confirm Filegroups
SELECT name as [File Group Name]
FROM sys.filegroups
WHERE type = 'FG'
GO 
-- Confirm Datafiles
SELECT name as [DB File Name],physical_name as [DB File Path]
FROM sys.database_files
where type_desc = 'ROWS'
GO

5.2)验证分区表上的数据分布

Use test_Partitioning 
GO
select partition_id, index_id, partition_number, Rows
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='SalesArchival'
GO

关于日期sql语句

Posted on by

网友请求写几条关于日期的sql语句
1、查询两个日期天数
select trunc(to_date(to_date( ‘2004-3-20 ‘, ‘yyyy-mm-dd ‘)-to_date( ‘2004-3-25 ‘, ‘yyyy-mm-dd ‘),’mm’) ) from dual ;
–trunc函数不用也行，因为日期格式化就是到天

2、查询两个日期的月份
SELECT trunc(months_between(to_date( ‘2004-3-20 ‘, ‘yyyy-mm-dd ‘),SYSDATE)) FROM dual;
–根据需求是截断还是取近似值决定使用floor或者trunc

3、根据生日查询年龄
1）计算年龄（周岁）
select floor(months_BETWEEN(SYSDATE,to_date( ‘2004-4-25 ‘, ‘yyyy-mm-dd ‘))/12) FROM dual;
2）计算年龄（虚岁）
SELECT to_char(SYSDATE,’yyyy’)-to_char(to_date(‘2004-03-04′,’yyyy-mm-dd’),’yyyy’) FROM dual;

主要就是trunc（近似值）和floor（截断）函数使用

ora_rowscn

Posted on by

一、默认情况下
–创建t_orascn测试表
SQL> create table t_orascn(id number);

Table created

–插入两条数据
SQL> insert into t_orascn values(1);

1 row inserted

SQL> insert into t_orascn values(2);

1 row inserted

SQL> commit;

Commit complete

–查询ora_rowscn和相关数据
SQL> select ora_rowscn,id from t_orascn;

ORA_ROWSCN ID
———- ———-
559036 1
559036 2

–更新其中一条数据
SQL> update t_orascn set id=2 where rownum=1;

1 row updated

SQL> commit;

Commit complete

–再查询ora_rowscn和相关数据
SQL> select ora_rowscn,id from t_orascn;

ORA_ROWSCN ID
———- ———-
559669 2
559669 2

–查询更详细信息
SQL> Select versions_xid,versions_startscn,versions_endscn,
2 DECODE(versions_operation,’I’,’Insert’,’U’,’Update’,’D’,’Delete’, ‘Original’) “Operation”, id from t_orascn versions between scn minvalue and maxvalue;

VERSIONS_XID VERSIONS_STARTSCN VERSIONS_ENDSCN Operation ID
—————- —————– ————— ——— ———-
0500180055010000 559669 Update 2
02001C0068010000 559036 Insert 2
02001C0068010000 559036 559669 Insert 1

–查询操作时间
SQL> select to_char(scn_to_timestamp(ora_rowscn),’yyyy-mm-dd hh24:mi:ss’),id from t_orascn;

TO_CHAR(SCN_TO_TIMESTAMP(ORA_R ID
—————————— ———-
2011-04-11 00:01:12 2
2011-04-11 00:01:12 2

–查询数据详细操作时间
SQL> Select versions_xid,to_char(scn_to_timestamp(versions_startscn),’yyyy-mm-dd hh24:mi:ss’),versions_endscn,DECODE(versions_operation,’I’,’Insert’,’U’,’Update’,’D’,’Delete’, ‘Original’) “Operation”, id from t_orascn versions between scn minvalue and maxvalue;

VERSIONS_XID TO_CHAR(SCN_TO_TIMESTAMP(VERSI VERSIONS_ENDSCN Operation ID
—————- —————————— ————— ——— ———-
0500180055010000 2011-04-11 00:01:12 Update 2
02001C0068010000 2011-04-10 23:59:03 Insert 2
02001C0068010000 2011-04-10 23:59:03 559669 Insert 1

–结论：ora_rowscn在没有默认情况下，如果数据库块中的任何一条记录发生改变，该块中的所有记录的ora_rowscn中对应的scn值都改变

二、创建表含有rowdependencies测试
–创建测试表t_orascn_b
SQL> create table t_orascn_b(id number) rowdependencies;

Table created

SQL> insert into t_orascn_b values(1);

1 row inserted

SQL> insert into t_orascn_b values(2);

1 row inserted

SQL> commit;

Commit complete

SQL> select ora_rowscn,id from t_orascn_b;

ORA_ROWSCN ID
———- ———-
560532 1
560532 2

SQL> insert into t_orascn_b values(3);

1 row inserted

SQL> select ora_rowscn,id from t_orascn_b;

ORA_ROWSCN ID
———- ———-
560532 1
560532 2
3

SQL> commit;

Commit complete

SQL> select ora_rowscn,id from t_orascn_b;

ORA_ROWSCN ID
———- ———-
560532 1
560532 2
560555 3
–说明一点：没有提交ora_rowscn不改变(update)或者不存在（insert）

SQL> update t_orascn_b set id=10 where id<2; 1 row updated SQL> commit;

Commit complete

SQL> select to_char(scn_to_timestamp(ora_rowscn),’yyyy-mm-dd hh24:mi:ss’),id from t_orascn_b;

TO_CHAR(SCN_TO_TIMESTAMP(ORA_R ID
—————————— ———-
2011-04-11 00:15:38 10
2011-04-11 00:12:37 2
2011-04-11 00:13:28 3

SQL>
SQL> Select versions_xid,versions_startscn,versions_endscn,
2 DECODE(versions_operation,’I’,’Insert’,’U’,’Update’,’D’,’Delete’, ‘Original’) “Operation”, id from t_orascn_b versions between scn minvalue and maxvalue;

VERSIONS_XID VERSIONS_STARTSCN VERSIONS_ENDSCN Operation ID
—————- —————– ————— ——— ———-
0800290054010000 560614 Update 10
0500130056010000 560555 Insert 3
0A00090001010000 560532 Insert 2
0A00090001010000 560532 560614 Insert 1

SQL> Select versions_xid,to_char(scn_to_timestamp(versions_startscn),’yyyy-mm-dd hh24:mi:ss’),versions_endscn,DECODE(versions_operation,’I’,’Insert’,’U’,’Update’,’D’,’Delete’, ‘Original’) “Operation”, id from t_orascn_b versions between scn minvalue and maxvalue;

VERSIONS_XID TO_CHAR(SCN_TO_TIMESTAMP(VERSI VERSIONS_ENDSCN Operation ID
—————- —————————— ————— ——— ———-
0800290054010000 2011-04-11 00:15:38 Update 10
0500130056010000 2011-04-11 00:13:28 Insert 3
0A00090001010000 2011-04-11 00:12:37 Insert 2
0A00090001010000 2011-04-11 00:12:37 560614 Insert 1
–结论：如果创建表时指定了rowdependencies，则ora_rowscn是以行为单位变化，而不是块

sql server中查看对象

Posted on by

1、查询所有数据库
select * from sys.databases;

2、查询对象
select * from sysobjects;

3、复制表结构和数据
SELECT * INTO t_1 FROM master.dbo.spt_monitor;

4、查询视图
SELECT * FROM sys.views;

5、查询列
select * from sys.columns;

6、查询索引
select * from sys.indexes;

由于文件组 ‘DEFAULT’ 中的磁盘空间不足，无法为数据库 ‘TEMPDB’ 分配新页

Posted on by

今天接到一个朋友的问题，sql server报“由于文件组 ‘DEFAULT’ 中的磁盘空间不足，无法为数据库 ‘TEMPDB’ 分配新页”，按照错误提示应该是tempdb空间不足，让其查看tempdb对应数据库文件大小为不到4g，查看其对应文件为自增长，硬盘空间还有40g多点，那应该没有问题。后来她说该文件所处位置的盘是fat32的，那问题确定是fat32最大支持文件大小是4g。解决办法:
1、让tempdb中的文件恢复默认值，设置其最大值
1）重启sql server服务，tempdb一般会自动变为初始化大小
2）如果1）失败，使用下面语句
dbcc shrinkfile (tempdev, 10)
dbcc shrinkfile (templog, 10)

2）然后设置tempdev和templog的最大大小为4000m

2、添加数据文件和日志文件，并设置其最大值为4000m

3、修改程序，从根源上解决问题
分析产生该问题原因
询问朋友后，原来是她对一个6kw的表和1kw的表进行关联，然后取得需要的数据插入到另一张表中，因为涉及的数据量非常大，所有导致tempdb数据库飞速增大，超过系统文件大小限制。导致文章开始的错误，光添加文件，对于这么大数据量的程序来说还是不能解决问题。应该在程序上去优化，而不是靠添加tempdb中文件的大小和数量来解决问题。
添加程序的提交次数，而不是所有数据一次提交，放在一个事物中。
我根据程序需求模拟的写了两种处理过程的方法
模拟环境：

--创建模拟表
SELECT id,name INTO t_1 FROM sys.sysobjects;
SELECT object_id id,name INTO t_2 FROM sys.columns;
--创建跟踪表
CREATE TABLE t_s (id int DEFAULT 0,date datetime DEFAULT getdate());
INSERT INTO t_s  VALUES(0,getdate());
--创建需要插入数据表
    SELECT t1.name n1,
           t2.name n2,
           t1.id
           INTO test_1
    FROM   t_1 t1,
           t_2 t2
    WHERE  t1.id = t2.id 
    AND 1=2;

方法一：
特点：1、优点执行效率比较高，无需排序和插入跟踪表
2、缺点：不能查看程序执行进度，如果异常终止，必须重新执行

create PROCEDURE [dbo].[INSERT_d_1]
AS
DECLARE @name1 varchar(4000)
DECLARE @name2 varchar(4000)
DECLARE @id int
DECLARE c1 CURSOR  
FOR
    SELECT t1.name,
           t2.name,
           t1.id
    FROM   t_1 t1,
           t_2 t2
    WHERE  t1.id = t2.id
    ORDER BY
           t1.id
open c1                       
fetch next from c1 into @name1,@name2,@id 
WHILE @@fetch_status=0
BEGIN
	INSERT INTO test_1 VALUES(@name1,@name2,@id)
    FETCH next FROM c1 INTO @name1,@name2,@id
END
CLOSE c1                  
DEALLOCATE c1

方法二：
特点：1、可以跟踪程序的执行进度查询t_s表，如果异常终止可以写其他程序继续
2、程序使用二重游标，执行效率比较低

create PROCEDURE [dbo].[INSERT_d_2]
AS
DECLARE @name1 varchar(4000)
DECLARE @name2 varchar(4000)
DECLARE @id int
DECLARE c1 CURSOR  
FOR
    SELECT t1.name,          
           t1.id
    FROM   t_1 t1
    ORDER BY t1.id

open c1                       
fetch next from c1 into @name1,@id 
WHILE @@fetch_status=0
BEGIN
	--二重循环
	DECLARE c2 CURSOR FOR 
	SELECT t2.name FROM t_2 t2 WHERE t2.id=@id
	OPEN c2
	FETCH next FROM c2 INTO @name2
	WHILE @@FETCH_STATUS=0
	BEGIN
		INSERT INTO test_1 VALUES(@name1,@name2,@id)
		UPDATE t_s SET id=@id,date=getdate()
		FETCH next FROM c2 INTO @name2
	END
	CLOSE c2
	DEALLOCATE c2
    FETCH next FROM c1 INTO @name1,@id
END
CLOSE c1                  
DEALLOCATE c1