Full-house

背景：

公司生产系统数据库中的一个功能，显示了来自很多字段的值，以“/” 分隔，产生的问题是，如果某几个字符没有内容的话，那显示出来的效果就可能会出现多个“/” 相连。如“////////////////” 我们需要将这么多“/” 变成一个”/”。

解决方案：

使用SQL 自定义的函数，循环地将所有的“//” 变成”/” 即可。

Create function [dbo].[ReplaceFG](@string varchar(1000),@old varchar(50),@new varchar(50))
RETURNS varchar(1000)
AS
begin
declare @temp varchar(1000)
while CHARINDEX(@old,@string) > 0
begin
set @string = Replace(@string,@old,@new)
set @temp = @string
end
return @temp
end

示例代码如下：

select dbo.ReplaceFG(’sa/////////////00000////////dg///////sdf’,'//’,'/’) value

value
———————-
sa/00000/dg/sdf

(1 row(s) affected)

单个修改：

EXEC sp_changeobjectowner ‘要改的表名’,'dbo’

批量修改：

EXEC sp_MSforeachtable ‘exec sp_changeobjectowner ”?”,”dbo” ‘

参考网址：http://www.cnblogs.com/insus/articles/1433141.html

private static void DemonstrateMergeTable()
{
DataTable table1 = new DataTable(“Items”);

// Add columns
DataColumn column1 = new DataColumn(“id”, typeof(System.Int32));
DataColumn column2 = new DataColumn(“item”, typeof(System.Int32));
table1.Columns.Add(column1);
table1.Columns.Add(column2);

// Set the primary key column.
table1.PrimaryKey = new DataColumn[] { column1 };

// Add RowChanged event handler for the table.
table1.RowChanged +=
new System.Data.DataRowChangeEventHandler(Row_Changed);

// Add some rows.
DataRow row;
for (int i = 0; i <= 3; i++)
{
row = table1.NewRow();
row["id"] = i;
row["item"] = i;
table1.Rows.Add(row);
}

// Accept changes.
table1.AcceptChanges();
PrintValues(table1, “Original values”);

// Create a second DataTable identical to the first.
DataTable table2 = table1.Clone();

// Add three rows. Note that the id column can’t be the
// same as existing rows in the original table.
row = table2.NewRow();
row["id"] = 14;
row["item"] = 774;
table2.Rows.Add(row);

row = table2.NewRow();
row["id"] = 12;
row["item"] = 555;
table2.Rows.Add(row);

row = table2.NewRow();
row["id"] = 13;
row["item"] = 665;
table2.Rows.Add(row);

// Merge table2 into the table1.
Console.WriteLine(“Merging”);
table1.Merge(table2);
PrintValues(table1, “Merged With table1″);

}

private static void Row_Changed(object sender,
DataRowChangeEventArgs e)
{
Console.WriteLine(“Row changed {0}\t{1}”,
e.Action, e.Row.ItemArray[0]);
}

private static void PrintValues(DataTable table, string label)
{
// Display the values in the supplied DataTable:
Console.WriteLine(label);
foreach (DataRow row in table.Rows)
{
foreach (DataColumn col in table.Columns)
{
Console.Write(“\t ” + row[col].ToString());
}
Console.WriteLine();
}
}

我是这样使用的，请看：

void bind()
{
BLL.Ts_Planning bll = new BLL.Ts_Planning();
Capsugel.BLL.Users bll2 = new BLL.Users();
DataTable dt = bll2.GetTsList().Tables[0];
DataSet ds = new DataSet();
DataTable dt2;
dt2 = bll.GetListByUid_All_Planning_Count(dt.Rows[0]["user id"].ToString(), TextBox2.Text.ToString(), TextBox3.Text.ToString()).Tables[0];
for (int i = 1; i < dt.Rows.Count – 1; i++)
{
dt2.Merge(bll.GetListByUid_All_Planning_Count(dt.Rows[i]["user id"].ToString(), TextBox2.Text.ToString(), TextBox3.Text.ToString()).Tables[0]);
}
GridView1.DataSource = dt2;
GridView1.DataBind();
}

下面是存储过程，有没有高人来指点一下，虽然问题已解决，谢谢DataTable的Merge :-)，但是好像很烦：

ALTER PROCEDURE [dbo].[Ts_Planning_Count]
@Uid varchar(20),
@date1 smalldatetime,
@date2 smalldatetime

AS

Select U.Name,AAAAA.* From
[Users] U,
(Select * From
(Select count(Pid) as [Explor/Penetra] from Ts_Planning
where
Purpose = ‘Explor/Penetra’ and UID=@Uid and (Date between @date1 and @date2)) A,

(Select count(Pid) as [Relation buliding] from Ts_Planning
where
Purpose = ‘Relation buliding’ and UID=@Uid and (Date between @date1 and @date2)) B,

(Select count(Pid) as [Prevent service] from Ts_Planning
where
Purpose = ‘Prevent service’ and UID=@Uid and (Date between @date1 and @date2)) C,

(Select count(Pid) as [Complaint handing] from Ts_Planning
where
Purpose = ‘Complaint handing’ and UID=@Uid and (Date between @date1 and @date2)) D,

(Select count(Pid) as [Reactive service] from Ts_Planning
where
Purpose = ‘Reactive service’ and UID=@Uid and (Date between @date1 and @date2)) E,

(Select count(Pid) as [Trial run] from Ts_Planning
where
Purpose = ‘Trial run’ and UID=@Uid and (Date between @date1 and @date2)) F,

(Select count(Pid) as [Key] from Ts_Planning
where
Class = ‘Key’ and UID=@Uid and (Date between @date1 and @date2)) AA,

(Select count(Pid) as [Core] from Ts_Planning
where
Class = ‘Core’ and UID=@Uid and (Date between @date1 and @date2)) BB,

(Select count(Pid) as [Target] from Ts_Planning
where
Class = ‘Target’ and UID=@Uid and (Date between @date1 and @date2)) CC,

(Select count(Pid) as [Potential] from Ts_Planning
where
Class = ‘Potential’ and UID=@Uid and (Date between @date1 and @date2)) DD,

(Select count(Pid) as [WM] from Ts_Planning
where
Segment = ‘WM’ and UID=@Uid and (Date between @date1 and @date2)) AAA,

(Select count(Pid) as [TCM] from Ts_Planning
where
Segment = ‘TCM’ and UID=@Uid and (Date between @date1 and @date2)) BBB,

(Select count(Pid) as [DS] from Ts_Planning
where
Segment = ‘DS’ and UID=@Uid and (Date between @date1 and @date2)) CCC,

(Select count(Pid) as Visit from Ts_Planning
where
Activity = ‘Visit’ and UID=@Uid and (Date between @date1 and @date2)) AAAA,

(Select count(Pid) as Travel from Ts_Planning
where
Activity = ‘Travel’ and UID=@Uid and (Date between @date1 and @date2)) BBBB,

(Select count(Pid) as [O/HO] from Ts_Planning
where
Activity = ‘O/HO’ and UID=@Uid and (Date between @date1 and @date2)) CCCC,

(Select count(Pid) as [Meeting/Training] from Ts_Planning
where
Activity = ‘Meeting/Training’ and UID=@Uid and (Date between @date1 and @date2)) DDDD,

(Select count(Pid) as [CFM&Expo] from Ts_Planning
where
Activity = ‘CFM&Expo’ and UID=@Uid and (Date between @date1 and @date2)) EEEE,

(Select count(Pid) as [H&V] from Ts_Planning
where
Activity = ‘H&V’ and UID=@Uid and (Date between @date1 and @date2)) FFFF,

(Select SUM(ActivityTimes) as [ActivityTimes] from Ts_Planning
where
UID=@Uid and (Date between @date1 and @date2)) AAAAA
) AAAAA
where U.Uid = @Uid

A database snapshot is a read-only, static view of a database (the source database). Multiple snapshots can exist on a source database and always reside on the same server instance as the database. Each database snapshot is transactionally consistent with the source database as of the moment of the snapshot’s creation. A snapshot persists until it is explicitly dropped by the database owner.

数据库快照（Database snapshot）是一个只读的，静态的数据库视图。一个数据库可以有多个数据库快照,每个数据库快照在被显性的删除之前将一直存在。数据库快照将保持和源数据库快照被创建时刻一致，所以可被用来做一些报表。并且由于数据库快照的存在，我们可以很容易的把数据库回复到快照创建时刻。

• 创建一个Snapshot.

CREATE DATABASE zhimaData_dbss ON

( NAME = zhimaData, FILENAME =

‘C:\Test.ss’ )

AS SNAPSHOT OF zhimaData;

GO

• 删除更容易啦.

Drop database zhimaData_dbss

Go

• 利用snapshot 来恢复database

USE master;
RESTORE DATABASE zhimaData FROM DATABASE_SNAPSHOT = ‘zhimaData_dbss’;
GO

How Database Snapshots Work

Understanding how snapshots work is helpful though not essential to using them. Database snapshots operate at the data-page level. Before a page of the source database is modified for the first time,

the original page is copied from the source database to the snapshot. This process is called a copy-on-write operation. The snapshot stores the original page, preserving the data records as they existed when the snapshot was created. Subsequent updates to records in a modified page do not affect the contents of the snapshot. The same process is repeated for every page that is being modified for the first time. In this way, the snapshot preserves the original pages for all data records that have ever been modified since the snapshot was taken.

To store the copied original pages, the snapshot uses one or more sparse files. Initially, a sparse file is an essentially empty file that contains no user data and has not yet been allocated disk space for user data. As more and more pages are updated in the source database, the size of the file grows. When a snapshot is taken, the sparse file takes up little disk space. As the database is updated over time, however, a sparse file can grow into a very large file. For more information about sparse files, see Understanding Sparse File Sizes in Database Snapshots.

–王成辉翻译整理，转贴请注明出自微软BI开拓者www.windbi.com
–原帖地址
如果你曾经做了很长时间的DBA，那么你会了解到SQLServe的性能调优不是一个精密的科学。即使是，对于为最佳的性能找到最佳的配置也是很困难的。这是因为对于调优来说很少东西是绝对的。例如，一个性能调优可能对某一方面有用，可是却会影响其他的性能。

我曾经做过DBA，在最后7年的日子里，我总结了一套SQLServer调优的清单。当第一次进行SQLServer性能调优的时候，可以用它来作为一个向导。我经常被邀请去检查SQLServer并提供一些性能方面的建议。直到现在，我还没有真正写下一个贯穿整个性能调优过程的方案。但是当我做了越来越多的性能调优的咨询工作后，我现在决定花点时间整理出来。你将会发现它是很有用的，就象我发现对我的用处一样.

SQLServer性能监控

这套性能优化的清单将至少准科学的帮助你找出你的SQLServer任何明显的性能问题。说是这样说，SQLServer的性能调优仍然是很困难的。我试图用这套清单去找出“容易”的sqlserver性能问题，困难的留待稍后。我这样做是因为很容易将容易和困难的的性能调优问题搞混。通过列出一个“容易”的性能调优范围，就很容易的将这些问题解决，一旦解决了这些容易的问题，那么你就能集中去解决更困难的问题。

使用这个SQLServer性能调优清单的一个好处是，它将不仅仅告诉你目前最容易解决的性能问题是什么，而且还帮助你正确的去解决。在某种程度上，你可以选择不同的顺序进行。换句话说，你可以故意做出特殊的决定而不是按照清单通常的顺序进行。某种意义上说你是对的，不是所有的性能调优建议都适合所有的情形。另外，你的决定是基于你的资源限制，例如没有足够的钱去买满足负荷的硬件。如果真是那样的话，你就别无选择了。还有，你的决定可能基于一些政治原因，那是你不得不作出的改变。不管怎样，你需要知道你能做什么，使用这个性能调优清单找出你能改变的范围并做出相应的改变提升你的SQLServer的性能。

一般来说，你将在你的每一个SQL服务器上执行这个清单。如果遇到清单中的一些问题，这会花掉你一些时间。我建议你从目前性能问题最多的的服务器开始，然后当你有时间的时候按照自己的思路去解决其他服务器。

一旦你完成了，可仍然有很多事情要去做。记住，这些只是一些容易的。一旦你完成了这些容易的，接下来你需要花时间去解决更困难问题。这个是另一篇文章要解决的问题了。
为了使其变得容易，我把它们分成了以下几个部分：

• 使用性能监视器找出硬件瓶颈
• SQLServer硬件性能监控列表
• 操作系统性能监控列表
• SQLServer2000配置性能监控列表
• 数据库配置设置性能监控列表
• 索引性能监控列表
• 应用程序和T-SQL性能监控列表
• SQLServer数据库作业性能监控列表
• 使用Profiler找出低效的查询
• 怎样最好的实现SQLServer性能监控

管理你的SQLServe性能的最好方法是首先回顾上面每一部分的内容，把它们打印出来。然后完成每一部分的内容，写下你收集到的结果。你也可以按照你喜欢的顺序进行。上面的步骤仅仅列出了我执行的顺序，因为那样通常能达到一个比较好的效果。

一旦你完成其中一部分，你可以按照在清单中发现的不同的建议进行你的性能优化工作。然后你将在后面的部分学到更多。

因文章太长,请下载word版全文:

怎样查出SQLServer的性能瓶颈