SQL Server使用心得

1.SQL2008R2中支持在定义变量时赋初值，SQL2005及以前的版本就不支持

declare  @p_name VARCHAR(40)   = 'abc'

 

2.查看依赖：

sp_depends,

sys.sql_expression_dependencies

SELECT OBJECT_NAME(referencing_id) AS referencing_entity_name, 
    o.type_desc AS referencing_desciption, 
    COALESCE(COL_NAME(referencing_id, referencing_minor_id), '(n/a)') AS referencing_minor_id, 
    referencing_class_desc, referenced_class_desc,
    referenced_server_name, referenced_database_name, referenced_schema_name,
    referenced_entity_name, 
    COALESCE(COL_NAME(referenced_id, referenced_minor_id), '(n/a)') AS referenced_column_name,
    is_caller_dependent, is_ambiguous
FROM sys.sql_expression_dependencies AS sed
INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id
WHERE referenced_entity_name = N'addBacklog'

exec sp_refreshsqlmodule getInfoByName

Updates the metadata for the specified non-schema-bound stored procedure, user-defined function, view, DML trigger, database-level DDL trigger, or server-level DDL trigger in the current database

 

3.授权用户具有查看元数据的能力:

grant view any definition to user_name

grant exec on sp_helptext to user_name

 

4.分区sum:

select empno, sum(sal) over (partition by deptno order by deptno range unbounded preceding) sumsal from emp;

 

ROWS/RANGE allows you to change the frame size within a partition. Valid arguments for ROWS are:

 

UNBOUNDED PRECEDING Starts the window at first row of the partition
UNBOUNDED FOLLOWING Ends the window at last row of the partition
CURRENT ROW Starts or Ends the window at current row
N PRECEDING Starts the window N rows before current row
N FOLLOWING Ends the window N rows after current row
 

RANGE is only supported with UNBOUNDED and CURRENT ROW frame delimiters, It cannot be used with N

PRECEDING or N FOLLOWING.

 

ROWS UNBOUNDED PRECEDING:

A simple example to demonstrate use of ROWS is generating cumulative total. Consider following

data:

Year        Month       Amount
2011        1           1000.00
2011        2           2000.00
2011        3           3000.00
2011        4           4000.00
2011        5           5000.00
2011        6           6000.00
2011        7           7000.00

To generate a cumulative total we need sum of all rows till current row. This can be done by using

UNBOUNDED PRECEDING:

SELECT [Year], [Month],
       SUM([Amount]) OVER (ORDER BY [Year], [Month] ROWS UNBOUNDED PRECEDING)
       AS [Amount]
FROM   dbo.SalesData
GROUP BY [Year], [Month], [Amount]

 

5.修改mdf,ldf文件的位置：

1)Take the database you want to work with offline by typing the following:
ALTER DATABASE database_name SET offline
GO

2)Move one file at a time to the new location by typing these commands:
ALTER DATABASE database_name
MODIFY FILE ( NAME = logical_name, FILENAME = "new_path".
GO

3)Repeat the previous step to move other full-text catalog files as necessary.
(need move files manually?)

4)Put the database online by typing the following:
ALTER DATABASE database_name SET online
GO