SQLServer DBA常見問題
In the latest installment of the SQL Server interview questions, we will outline questions suitable for a DBA interview to assess the candidates skills related to SQL Server system databases. In this tip, the questions are there to read, but the answers are intentionally hidden to really test your skills. Once you read the question and have determined your answer, then highlight the answer to see how you did.
Solution
Question Difficulty = Easy
Question 1: What are the SQL Server system databases and can you outline the general functionality of each database?
Master - Database responsible for SQL Server instance related data. You can also think of this database corresponding to the Windows SQL Server service account.
Resource - Database responsible for SQL Server system objects. This database was introduced in SQL Server 2005 and is intended to ease the upgrade and rollback of SQL Server system objects.
Model - Template database for the creation of new user defined databases.
MSDB - Database responsible for SQL Server Agent related data such as Jobs, Alerts, Operators, etc.
TempDB - Temporary database to store temporary tables (#temptable or ##temptale), table variables, cursors, work tables, row versioning, create or rebuild indexes sorted in TempDB, etc. Each time the SQL Server instance is restarted all objects in this database are destroyed, so permanent objects cannot be created in this database.
Distribution - Database responsible for managing replicated data. This database could reside on the publisher or subscriber.
Additional information: SQL Server 2005 Books Online - System Databases
Question 2: True or False - Can you create objects in the Master, Model and MSDB databases?
True.
Question 3: Is it a good idea to create objects in the system databases?
In general , objects should not be created in the system databases. In general, it is a best practice to create a separate database for user defined objects that would be used instanc
相关文档：
--每个月的第一天
select dateadd(dd,-datepart(dd,getdate())+1,getdate())
--第个月的最后一天
select dateadd(dd,-datepart(dd,getdate()),dateadd(mm,1,getdate()))
--本月的天数
select datediff(dd,getdate(),dateadd(mm,1,getdate()))
......
一、SQLServer 通用索引字段
1、Primary Key's 主键
2、Foreign Key's 外键
3、支持SELECT、INSERT、UPDATE和DELETE命令的字段 :
(1)、INNER JOIN
(２)、RIGHT | LEFT OUTER JOIN
(３)、WHERE
(４)、ORDER BY
(５)、GROUP BY
(６)、HAVING
二、创建索引的其他因素(主要因素都因为索引是数据表外部 ......
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.SqlClient; ......
索引分为两大类:聚集索引和非聚集索引
一、聚集索引
当数据表中的一列被确定为主键后,SQLServer会自动为它建立聚集索引,因为聚集索引是标识每个记录行的键,所以它将被应用到每个查询中.
二、非聚集索引
非聚集索引的情况就比较复杂了,因为它是相对于表独立组织的,在SQLServer中有单独的结构来存储非聚集索引.
......
现在一般常用的有以下2种方法:
1. select top @pagesize * from table1 where id not in (select top @pagesize*(@page-1) id from table1 order by id) order by id
2. select * from (select top @pagesize * from (select top @pagesize*@page * from table1 order by id) a order by id desc) b or ......
