In my Are you ready for SQL Server 2012 or are you still partying like it is 1999? post, I wrote about how you should start using SQL Server 2005 and SQL Server 2008 functionality now in order to prepare for SQL Server 2012. I still see tons of code that is written in the pre 2005 style and people still keep using those functions, procs and statements even though SQL Server 2005 and 2008 have much better functionality.

In today's post I will cover schemas. Schemas were introduced in SQL Server 2005, each schema is basically a distinct namespace in a database. A schema exists independently of the database user who created it. A schema is simply a container of objects. The owner of a schema can be any user, the ownership of the schema is transferable.

Let's see how this all works, first create a new login name Denis with a highly secure password

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
USE master
GO
CREATE LOGIN Denis WITH PASSWORD = 'Bla'
GO


To run all this code correctly, you should have two connections to the database we will create, one connection should be your admin connection, the other connection should be connected as this new user we just created.

Now create a new database named SalesStuff

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
CREATE DATABASE SalesStuff
GO


Inside the SalesStuff database create a new user which is mapped to the login Denis

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
USE SalesStuff
GO
CREATE USER Denis FOR LOGIN Denis
GO


Create a schema in the SalesStuff database named Sales, also create a table named Orders in that schema

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
CREATE SCHEMA Sales
GO
CREATE TABLE Sales.Orders
(OrderID int, OrderDate date, OrderAmount decimal(30,2))


Now login to the database with the Denis account and run the query below

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
select * from orders


You should see the following error.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'orders'.

The problem is that when you login, your default schema is not Sales and so the Orders table can't be found. Prefix the table with the schema and try again

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
select * from Sales.Orders


You get this error message
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'Orders', database 'SalesStuff', schema 'Sales'.

We need to give the Denis user select permissions for this table. Login as the admin and run the query below

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
GRANT SELECT ON SCHEMA::Sales TO Denis


That query gave the user Denis select permissions on all tables in the Sales schema. Notice the double colon syntax, that is how you need to grant, deny and revoke permissions. If you run the select query again, you will get back an empty resultset.

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
select * from Sales.Orders


Let's try to do an insert

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
insert Sales.Orders values(1,getdate(),100)


As expected, that fails also

Msg 229, Level 14, State 5, Line 1
The INSERT permission was denied on the object 'Orders', database 'SalesStuff', schema 'Sales'.

Go back to the admin query window, run the query below to give the insert permissions

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
GRANT INSERT ON SCHEMA::Sales TO Denis


If you try the insert again, it will succeed

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
insert Sales.Orders values(1,getdate(),100)


Remember how we tried to select from the table without specifying the schema? Let's try that again

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
select * from Orders


Msg 208, Level 16, State 1, Line 1
Invalid object name 'Orders'.

Same error, let's fix that

Go back to the admin query window and execute the query below

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
ALTER USER Denis
WITH DEFAULT_SCHEMA = Sales


We just made the Sales schema the default schema for the user Denis. Now if we specify the schema or if we omit the schema, we get back the same result

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
select * from Orders
select * from Sales.Orders


Go back to the admin connection and create this stored procedure

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
create procedure Sales.prtest1
as
select 1


Go to the query window for the user Denis and run the proc

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
exec prtest1


Msg 229, Level 14, State 5, Procedure prtest1, Line 1
The EXECUTE permission was denied on the object 'prtest1', database 'SalesStuff', schema 'dbo'.

As you can see, we don't have execute permissions for the stored procedure.
Bring up the admin query window and give Denis execute permissions on the schema

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
GRANT execute ON SCHEMA::Sales TO Denis


Now if you try to execute the proc from the connection which is logged in as Denis it succeeds

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
exec prtest1


Go back yet again to the admin query window and create another stored procedure

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
create procedure Sales.prtest2
as
select 2


Now if you go back to the connection for user Denis and execute the proc we just created, it also is successful.

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
exec prtest2


As you can see, once you have execute permissions on a schema, you don't have to go and explicitly give execute permissions for every stored procedure

To see all the tables that you have select permissions on, you can run the query below from the connection logged in as Denis. It will return 1 if you have select permissions or 0 if you don't

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
SELECT HAS_PERMS_BY_NAME
(QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name),
    'OBJECT', 'SELECT') AS have_select, name FROM sys.tables


Output
---------------
1	Orders

For procs it will return 1 if you have execute permissions, if you don't have execute permissions then the proc is not returned. Run the query below from the connection logged in as Denis

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
SELECT HAS_PERMS_BY_NAME
(QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name),
    'OBJECT', 'exec') AS have_select, name FROM sys.procedures

Output

---------------
1	prtest1

1	prtest2


As you can see you get 2 rows back

No go back to the admin connection and deny execute on the schema

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
DENY EXECUTE ON SCHEMA::Sales TO Denis


Run the query below from the connection logged in as Denis

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
SELECT HAS_PERMS_BY_NAME
(QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name),
    'OBJECT', 'exec') AS have_select, name FROM sys.procedures


As you can see nothing is returned at all

So what is so cool about schemas anyway?
When you start using schemas, you have a way to logically group a bunch of objects together. For example if you have a Sales and a Marketing schema then if you need to find a specific table that has something to do with Sales, you don't have to look up and down in object explorer to find the table, it should be sorted under the sales schema. Permissions are also easier, you give the sales person permission to the Sales schema and if new tables are added he or she will have the select permission the moment the table is created.
When using schemas you now can have a table named Customers in both schemas without a problem and each will hold data just for the department that uses the schema the table is in.

Read more
This was just a small overview, I did not cover all the things you need to know about schemas in SQL Server. Take a look at SQL Server Best Practices – Implementation of Database Object Schemas to get some more details about how to use schemas.

参考：http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/MSSQLServer/sql-advent-2011-day-4

--估算每天数据库的更新量

DECLARE @starttime DATETIME
SELECT @starttime = sqlserver_start_time 
FROM sys.dm_os_sys_info --取服务器启动时间

--按索引表中最大的更新量来估算
SELECT name,user_updates,last_user_update, 日平均
FROM (
	SELECT
	tbl.name,user_updates
	,ius.last_user_update,
	user_updates/(DATEDIFF(mi,@starttime,last_user_update)*1.0/(24*60)) 日平均
	,ROW_NUMBER() OVER( PARTITION BY name ORDER BY user_updates DESC )n
	FROM
	sys.dm_db_index_usage_stats ius 
	INNER JOIN sys.tables tbl ON tbl.OBJECT_ID = ius.OBJECT_ID
	WHERE  ius.user_updates !=0 AND ius.database_id = DB_ID()
)m
WHERE m.n = 1 
ORDER BY m.日平均 DESC

 SELECT CAST(SUBSTRING(command, 7, 8000) AS NVARCHAR(MAX))
 FROM msrepl_commands
 WHERE xact_seqno = 0x0000EFC4000018920027
 AND command_id = 1

 --
 SELECT xact_seqno,COUNT(1) c
 FROM  dbo.MSrepl_commands
 GROUP BY xact_seqno
 ORDER BY c DESC


--或者用sp_browsereplcmds
sp_browsereplcmds '0x0000EFC4000018920027','0x00008920027'

USE     tempdb;
GO
DROP    TABLE dbo.Test;
GO
CREATE  TABLE 
        dbo.Test 
        (
        data            INTEGER NOT NULL,
        );
GO
CREATE  CLUSTERED INDEX c ON dbo.Test (data);
GO
-- Lots of duplicated values
INSERT  dbo.Test WITH (TABLOCK)
        (data)
SELECT  TOP (5000000)
        ROW_NUMBER() OVER (ORDER BY (SELECT 0)) / 117329
FROM    master.sys.columns C1,
        master.sys.columns C2,
        master.sys.columns C3;
GO

SET     STATISTICS TIME ON;

-- 1591ms CPU
SELECT  DISTINCT 
        data
FROM    dbo.Test;

SQL Server 分析和编译时间: 
   CPU 时间 = 859 毫秒，占用时间 = 2702 毫秒。

SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

(43 行受影响)

SQL Server 执行时间:
   CPU 时间 = 967 毫秒，占用时间 = 5881 毫秒。
   
-- 15ms CPU
WITH    RecursiveCTE
AS      (
        SELECT  data = MIN(T.data)
        FROM    dbo.Test T
        UNION   ALL
        SELECT  R.data
        FROM    (
                -- A cunning way to use TOP in the recursive part of a CTE :)
                SELECT  T.data,
                        rn = ROW_NUMBER() OVER (ORDER BY T.data)
                FROM    dbo.Test T
                JOIN    RecursiveCTE R
                        ON  R.data < T.data
                ) R
        WHERE   R.rn = 1
        )
SELECT  *
FROM    RecursiveCTE

--OPTION  (MAXRECURSION 0);

在有索引且排好序的情况下，后者速度更快！

SET     STATISTICS TIME OFF;
GO
DROP    TABLE dbo.Test;

Create table #sales_details
(
	sales_id int identity(1,1),
	item_id int not null,
	qty int not null,
	unit_price decimal(12,2) not null,
	sales_date datetime not null
)

insert into #sales_details (item_id,qty,unit_price,sales_date)
select 1001,5,200,'2012-09-03 11:16:28' union all
select 1001,2,200,'2012-09-04 19:22:11' union all
select 1002,15,1300,'2012-09-06 14:26:40' union all
select 1003,8,78,'2012-09-19 15:11:19' union all
select 1001,6,200,'2012-09-22 16:36:11' union all
select 1004,22,1000,'2012-09-23 16:51:34' union all
select 1004,11,1000,'2012-09-23 17:29:38' union all
select 1002,29,1300,'2012-09-23 18:20:10' union all
select 1002,6,1300,'2012-09-26 19:40:41' union all
select 1002,33,1300,'2012-09-30 20:26:29' 

Assume that you want to find out distinct item_id from the above table. You can use many methods. Some of them are listed below

--Method 1 : Use DISTINCT keyword

select distinct item_id from #sales_details 

--Method 2 : Use GROUP BY Clause
select item_id from #sales_details 
Group by item_id

--Method 3 : UNION the same table
select item_id from #sales_details 
UNION
select item_id from #sales_details 

--Method 4 : UNION the same table with not selecting any rows from secondly sepecified table
select item_id from #sales_details 
UNION    --union会做一个distinct
select item_id from #sales_details where 1=0

--Method 5 : UNION the table with Empty result

select distinct item_id from #sales_details 


select item_id from #sales_details 
UNION
select 0 where 1=0

--Method 6 : Use Row_number() function
select item_id from
(
	select row_number() over (partition by item_id order by item_id) as sno,* from #sales_details 
) as t
where sno=1


参考：distinct

一，入手：
拿到存储过程后，先执行一次看速度与编译时间，确定是编译慢还是执行问题。

--查看当前库的账号

SELECT  members.name, roles.name,roles.type_desc,members.type_desc
FROM sys.database_role_members rolemem
INNER JOIN sys.database_principals roles
ON rolemem.role_principal_id = roles.principal_id
INNER JOIN sys.database_principals members
ON rolemem.member_principal_id = members.principal_id
ORDER BY members.name

EXEC dbo.sp_helprolemember

T-SQL Enhancements
参考：英文，中文

1，WITH RESULT SETS
更改存储过程返回结果表的字段名类型
CREATE PROCEDURE Denali_WithResultSet  AS  
BEGIN         
    SELECT 1 as No,’Tsql’ Type, ‘WithResultSet’ AS Feature
    UNION ALL  SELECT 2 as No,’Tsql’ Type, ‘Throw’ AS Feature 
    UNION ALL SELECT 3 as No,’Tsql’ Type, ‘Offset’ AS Feature 
    UNION ALL SELECT 4 as No,’Tsql’ Type, ‘Sequence’ AS Feature  
END  
GO  
EXEC Denali_WithResultSet  
WITH RESULT SETS  (( No int,FeatureType varchar(50), FeatureName varchar(50) )   )

2，OFFSET and FETCH
offset过滤几行 fetch:取下面的几行
SELECT top 15 * FROM dbo.frmuser f(NOLOCK) order BY account     
SELECT * FROM dbo.frmuser f(NOLOCK) order BY account OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY   
SELECT top 5 * FROM ( SELECT * ,ROW_NUMBER()OVER (ORDER BY account)n FROM frmuser f)m WHERE m.n>10

3，SEQUENCE
提供自增长，与identity不同的是不依赖与表，并且是全局性的 可不设置最大值,若到最大值时可自动循环。
CREATE SEQUENCE SeqRange  AS int   START WITH 1   INCREMENT BY 1     MINVALUE 1      MAXVALUE 5    CYCLE ;//循环自动    
SELECT NEXT VALUE FOR dbo.SeqRange  S
ELECT NEXT VALUE FOR dbo.Seq; 
 SELECT NEXT VALUE FOR dbo.Seq;

到达最大值后，可重置
ALTER SEQUENCE seq  RESTART WITH 1

赋值：

DECLARE @I INT

相关存储过程
DECLARE @first_value sql_variant,
@last_value sql_variant
EXEC sp_sequence_get_range @sequence_name = N’SeqRange’, @range_size = 4, @range_first_value = @first_value OUTPUT, @range_last_value = @last_value OUTPUT;
SELECT @first_value AS FirstNumber, @last_value as LastNumber

应用：订单号生成
可在表定义时使用，这样我们就能在插入之前获取ID
create table t1 ( orderID integer default (next value for dbo.TestID), OrderDesc varchar(50) )

4，FORCESEEK and FORCESCAN
forceseek:强制查询按某个索引查找
forecescan:强制查询按查个索引扫描

5，
EOMONTH：返回当月最后一天
CHOOSE:返回列表中的第几个值
Select Choose (2, 'January', 'February', 'March');
IIF:三值逻辑
SELECT IIF (@a > @b, 'TRUE', ‘FALSE’) AS Result;
CONCAT: 连接，可多种类型，如整型与字符串相加
SELECT CONCAT ('age:',1) AS Result;

OVER 增强
unbounded preceding：从头开始
CURRENT ROW：当前行
unbounded following：尾

如：
SELECT *
FROM (
SELECT bc.CustomerName, bo.OrderDate,bo.OrderNo,bo.Amount,
last_value(orderdate) OVER( PARTITION BY bc.ID ORDER BY bo.OrderDate RANGE BETWEEN unbounded preceding AND unbounded following ) lastorderdate
FROM dbo.bmdCustomer bc(NOLOCK)
JOIN dbo.bdOrder bo(NOLOCK) ON bc.ID = bo.CustomerID
–WHERE bc.id IN(1819,2206,2444)
)m
WHERE m.orderdate = m.lastorderdate
ORDER BY CustomerName,OrderDate

6，分析函数
lag:返回按某种排序相比当前行，上几行的字段 LAG (scalar_expression [, offset], [default]) OVER ([partition_by_clause] order_by_clause)
SELECT TOP 10 Account,lag(account,1,'开始') OVER( ORDER BY account )
FROM dbo.frmuser
ORDER BY account

SELECT TOP 10 Account,lead(account+'aaa',1,'开始') OVER( ORDER BY account )
leag:返回按某种排序相比当前行，下几行的字段
LAST_VALUE 返回 SQL Server 2012 中有序值集中的最后一个值,这个集可用range指定 默认是从第一行到当前这一行:RANGE BETWEEN unbounded preceding AND CURRENT ROW FIRST_VALUE 开头一个值
–需求：取出客户的最近一次购买的订单号与订单金额

SELECT *
FROM (
SELECT bc.CustomerName, bo.OrderDate,bo.OrderNo,bo.Amount,
last_value(orderdate) OVER( PARTITION BY bc.ID ORDER BY bo.OrderDate RANGE BETWEEN unbounded preceding AND unbounded following ) lastorderdate
FROM dbo.bmdCustomer bc(NOLOCK)
JOIN dbo.bdOrder bo(NOLOCK) ON bc.ID = bo.CustomerID
–WHERE bc.id IN(1819,2206,2444)
)m
WHERE m.orderdate = m.lastorderdate
ORDER BY CustomerName,OrderDate

–原方法
SELECT bc.CustomerName, bo.OrderDate,bo.OrderNo,bo.Amount
FROM dbo.bmdCustomer bc(NOLOCK)
JOIN dbo.bdOrder bo(NOLOCK) ON bc.ID = bo.CustomerID
–WHERE bc.id IN(1819,2206,2444)
AND bo.orderdate = ( SELECT MAX(OrderDate)
FROM dbo.bdOrder bo2(NOLOCK)
WHERE bo2.CustomerID = bo.CustomerID)
ORDER BY CustomerName,OrderDate

For many reasons SQL Server DBAs need to find the last date and time of an update on a sql table. The SQL Server DMV sys.dm_db_index_usage_stats  can supply enough information to allow the DBA to make a good estimate of the time.

In this case I needed to create a list candidates for tables to be deleted. The data became bloated with a number of different temp tables .

The one drawback of using the DMV is that data is refreshed after every SQL Server restart.

This script lists all tables in the current database, with various columns assisting in deciding the last update.

 

SELECT
tbl.name
,ius.last_user_update
,ius.user_updates
,ius.last_user_seek
,ius.last_user_scan
,ius.last_user_lookup
,ius.user_seeks
,ius.user_scans
,ius.user_lookups
FROM
sys.dm_db_index_usage_stats ius INNER JOIN
sys.tables tbl ON (tbl.OBJECT_ID = ius.OBJECT_ID)
WHERE ius.database_id = DB_ID()
last_user_update - Time of last user update.
user_updates - Number of updates by user queries.
last_user_seek - Time of last user seek .
last_user_scan - Time of last user scan.
last_user_lookup - Time of last user lookup.
user_seeks - Number of seeks by user queries.
user_scans - Number of scans by user queries.
user_lookups - Number of bookmark lookups by user queries

Related Posts

SQL Server – Last DML operation

SQL Server - Calculate table size with existing data

How to request SQL Server troubleshooting