sqlserver2012中表更新数据的命令是

sqlserver2012中表更新数据的命令是UPDATE。SQLServerUPDATE语句用于更新数据。在SQLServer数据库中要想修改数据库记录，就需要用UPDATE语句，UPDATE语句就是为了改变数据库中的现存数据而存在的。

给你一些我常用的脚本！至于方案，我觉得完全在于积累经验！

--前10名其他等待类型

SELECT TOP 10

from sysdm_os_wait_stats

ORDER BY wait_time_ms DESC

SELECT FROM sysdm_os_wait_stats WHERE wait_type like 'PAGELATCH%'

OR wait_type like 'LAZYWRITER_SLEEP%'

--CPU的压力

SELECT scheduler_id, current_tasks_count, runnable_tasks_count

FROM sysdm_os_schedulers

WHERE scheduler_id < 255

--表现最差的前10名使用查询

SELECT TOP 10 ProcedureName = ttext,

ExecutionCount = sexecution_count,

AvgExecutionTime = isnull ( stotal_elapsed_time / sexecution_count, 0 ),

AvgWorkerTime = stotal_worker_time / sexecution_count,

TotalWorkerTime = stotal_worker_time,

MaxLogicalReads = smax_logical_reads,

MaxPhysicalReads = smax_physical_reads,

MaxLogicalWrites = smax_logical_writes,

CreationDateTime = screation_time,

CallsPerSecond = isnull ( sexecution_count / datediff ( second , screation_time, getdate ()), 0 )

FROM sysdm_exec_query_stats s

CROSS APPLY sysdm_exec_sql_text( ssql_handle ) t ORDER BY

smax_physical_reads DESC

SELECT SUM(signal_wait_time_ms) AS total_signal_wait_time_ms总信号等待时间 ,

SUM(wait_time_ms - signal_wait_time_ms) AS resource_wait_time_ms资源的等待时间,

SUM(signal_wait_time_ms) 10 / SUM (wait_time_ms) 100 AS [signal_wait_percent信号等待％],

SUM(wait_time_ms - signal_wait_time_ms) 10 / SUM (wait_time_ms) 100 AS [resource_wait_percent资源等待％]

FROM sysdm_os_wait_stats

--一个信号等待时间过多对资源的等待时间那么你的CPU是目前的一个瓶颈。

--查看进程所执行的SQL语句

if (select COUNT() from masterdbosysprocesses) > 500

begin

select text,CROSS APPLY mastersysdm_exec_sql_text(asql_handle) from mastersyssysprocesses a

end

select text,a from mastersyssysprocesses a

CROSS APPLY mastersysdm_exec_sql_text(asql_handle)

where aspid = '51'

dbcc inputbuffer(53)

with tb

as

(

select blocking_session_id,

session_id,db_name(database_id) as dbname,text from mastersysdm_exec_requests a

CROSS APPLY mastersysdm_exec_sql_text(asql_handle)

),

tb1 as

(

select a,login_time,program_name,client_interface_name,login_name,cpu_time,memory_usage8 as 'memory_usage(KB)',

total_scheduled_time,reads,writes,logical_reads

from tb a inner join mastersysdm_exec_sessions b

on asession_id=bsession_id

)

select a,connect_time,client_tcp_port,client_net_address from tb1 a inner join mastersysdm_exec_connections b on asession_id=bsession_id

--当前进程数

select from masterdbosysprocesses

order by cpu desc

--查看当前活动的进程数

sp_who active

--查询是否由于连接没有释放引起CPU过高

select from masterdbosysprocesses

where spid> 50

and waittype = 0x0000

and waittime = 0

and status = 'sleeping '

and last_batch < dateadd(minute, -10, getdate())

and login_time < dateadd(minute, -10, getdate())

--强行释放空连接

select 'kill ' + rtrim(spid) from masterdbosysprocesses

where spid> 50

and waittype = 0x0000

and waittime = 0

and status = 'sleeping '

and last_batch < dateadd(minute, -60, getdate())

and login_time < dateadd(minute, -60, getdate())

--查看当前占用 cpu 资源最高的会话和其中执行的语句(及时CPU)

select spid,cmd,cpu,physical_io,memusage,

(select top 1 [text] from ::fn_get_sql(sql_handle)) sql_text

from mastersysprocesses order by cpu desc,physical_io desc

--查看缓存中重用次数少，占用内存大的查询语句（当前缓存中未释放的）--全局

SELECT TOP 100 usecounts, objtype, psize_in_bytes,[sql][text]

FROM sysdm_exec_cached_plans p OUTER APPLY sysdm_exec_sql_text (pplan_handle) sql

ORDER BY usecounts,psize_in_bytes desc

SELECT top 25 qttext,qsplan_generation_num,qsexecution_count,dbid,objectid

FROM sysdm_exec_query_stats qs

CROSS APPLY sysdm_exec_sql_text(sql_handle) as qt

WHERE plan_generation_num >1

ORDER BY qsplan_generation_num

SELECT top 50 qttext AS SQL_text ,SUM(qstotal_worker_time) AS total_cpu_time,

SUM(qsexecution_count) AS total_execution_count,

SUM(qstotal_worker_time)/SUM(qsexecution_count) AS avg_cpu_time,

COUNT() AS number_of_statements

FROM sysdm_exec_query_stats qs

CROSS APPLY sysdm_exec_sql_text(qssql_handle) as qt

GROUP BY qttext

ORDER BY total_cpu_time DESC --统计总的CPU时间

--ORDER BY avg_cpu_time DESC --统计平均单次查询CPU时间

-- 计算可运行状态下的工作进程数量

SELECT COUNT() as workers_waiting_for_cpu,sscheduler_id

FROM sysdm_os_workers AS o

INNER JOIN sysdm_os_schedulers AS s

ON oscheduler_address=sscheduler_address

AND sscheduler_id<255

WHERE ostate='RUNNABLE'

GROUP BY sscheduler_id

--表空间大小查询

create table #tb(表名 sysname,记录数 int,保留空间 varchar(100),使用空间 varchar(100),索引使用空间 varchar(100),未用空间 varchar(100))

insert into #tb exec sp_MSForEachTable 'EXEC sp_spaceused '''''

select from #tb

go

SELECT

表名,

记录数,

cast(ltrim(rtrim(replace(保留空间,'KB',''))) as int)/1024 保留空间MB,

cast(ltrim(rtrim(replace(使用空间,'KB',''))) as int)/1024 使用空间MB,

cast(ltrim(rtrim(replace(使用空间,'KB',''))) as int)/1024/102400 使用空间GB,

cast(ltrim(rtrim(replace(索引使用空间,'KB',''))) as int)/1024 索引使用空间MB,

cast(ltrim(rtrim(replace(未用空间,'KB',''))) as int)/1024 未用空间MB

FROM #tb

WHERE cast(ltrim(rtrim(replace(使用空间,'KB',''))) as int)/1024 > 0

--order by 记录数 desc

ORDER BY 使用空间MB DESC

DROP TABLE #tb

--查询是否由于连接没有释放引起CPU过高

select from masterdbosysprocesses

where spid> 50

and waittype = 0x0000

and waittime = 0

and status = 'sleeping '

and last_batch < dateadd(minute, -10, getdate())

and login_time < dateadd(minute, -10, getdate())

--强行释放空连接

select 'kill ' + rtrim(spid) from masterdbosysprocesses

where spid> 50

and waittype = 0x0000

and waittime = 0

and status = 'sleeping '

and last_batch < dateadd(minute, -60, getdate())

and login_time < dateadd(minute, -60, getdate())

----查看当前占用 cpu 资源最高的会话和其中执行的语句(及时CPU)

select spid,cmd,cpu,physical_io,memusage,

(select top 1 [text] from ::fn_get_sql(sql_handle)) sql_text

from mastersysprocesses order by cpu desc,physical_io desc

----查看缓存中重用次数少，占用内存大的查询语句（当前缓存中未释放的）--全局

SELECT TOP 100 usecounts, objtype, psize_in_bytes,[sql][text]

FROM sysdm_exec_cached_plans p OUTER APPLY sysdm_exec_sql_text (pplan_handle) sql

ORDER BY usecounts,psize_in_bytes desc

SELECT top 25 qttext,qsplan_generation_num,qsexecution_count,dbid,objectid

FROM sysdm_exec_query_stats qs

CROSS APPLY sysdm_exec_sql_text(sql_handle) as qt

WHERE plan_generation_num >1

ORDER BY qsplan_generation_num

SELECT top 50 qttext AS SQL_text ,SUM(qstotal_worker_time) AS total_cpu_time,

SUM(qsexecution_count) AS total_execution_count,

SUM(qstotal_worker_time)/SUM(qsexecution_count) AS avg_cpu_time,

COUNT() AS number_of_statements

FROM sysdm_exec_query_stats qs

CROSS APPLY sysdm_exec_sql_text(qssql_handle) as qt

GROUP BY qttext

ORDER BY total_cpu_time DESC --统计总的CPU时间

--ORDER BY avg_cpu_time DESC --统计平均单次查询CPU时间

-- 计算可运行状态下的工作进程数量

SELECT COUNT() as workers_waiting_for_cpu,sscheduler_id

FROM sysdm_os_workers AS o

INNER JOIN sysdm_os_schedulers AS s

ON oscheduler_address=sscheduler_address

AND sscheduler_id<255

WHERE ostate='RUNNABLE'

GROUP BY sscheduler_id

SELECT creation_time N'语句编译时间'

,last_execution_time N'上次执行时间'

,total_physical_reads N'物理读取总次数'

,total_logical_reads/execution_count N'每次逻辑读次数'

,total_logical_reads N'逻辑读取总次数'

,total_logical_writes N'逻辑写入总次数'

, execution_count N'执行次数'

, total_worker_time/1000 N'所用的CPU总时间ms'

, total_elapsed_time/1000 N'总花费时间ms'

, (total_elapsed_time / execution_count)/1000 N'平均时间ms'

,SUBSTRING(sttext, (qsstatement_start_offset/2) + 1,

((CASE statement_end_offset

WHEN -1 THEN DATALENGTH(sttext)

ELSE qsstatement_end_offset END

- qsstatement_start_offset)/2) + 1) N'执行语句'

FROM sysdm_exec_query_stats AS qs

CROSS APPLY sysdm_exec_sql_text(qssql_handle) st

where SUBSTRING(sttext, (qsstatement_start_offset/2) + 1,

((CASE statement_end_offset

WHEN -1 THEN DATALENGTH(sttext)

ELSE qsstatement_end_offset END

- qsstatement_start_offset)/2) + 1) not like '%fetch%'

ORDER BY total_elapsed_time / execution_count DESC;

建立一个信息库

Oracle Migration Workbench利用一个Oracle数据库架构作为移植信息库，用来储存其为移植进程所收集和转换的元数据。不管你要进行多少次移植 *** 作，建立移植信息库只需要执行一次就够了。这个信息库由37个表以及相关的主键、索引，8个视图，触发器和PL/SQL代码组成。虽然不是必须的，不过Oracle还是建议专门为这个信息库配置一个数据库架构。

必须赋予创建和拥有这个信息库的用户多种系统权限和任务。列表1创建了一个名为migration的用户，并赋予其必要的特权。

代码列表1：创建migration用户

CREATE USER migration IDENTIFIED BY migration

DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;

GRANT CONNECT, RESOURCE, CREATE VIEW, CREATE PUBLIC SYNONYM

TO migration WITH ADMIN OPTION;

GRANT ALTER ANY ROLE, ALTER ANY SEQUENCE,

ALTER ANY TABLE, ALTER TABLESPACE, ALTER ANY TRIGGER,

COMMENT ANY TABLE, CREATE ANY SEQUENCE, CREATE ANY TABLE,

CREATE ANY TRIGGER, CREATE ROLE, CREATE TABLESPACE,

CREATE USER, DROP ANY SEQUENCE, DROP ANY TABLE,

DROP ANY TRIGGER, DROP TABLESPACE, DROP USER,

DROP ANY ROLE, GRANT ANY ROLE, INSERT ANY TABLE,

SELECT ANY TABLE, UPDATE ANY TABLE

TO migration;

一旦你创建了这个用户，你就需要为这个用户建立一个新的数据库连接，例如Migration_Repos。当你输入必要的信息之后，点击Connect保存连接并关闭对话框。从主菜单中选择Migration -> Repository Management -> Create Repository。选择为新信息库建立的连接，并点击Create。这时，会出现一个Installing Repository对话框，显示安装进度。当进程结束后，关闭对话框。

在Connections Navigator下会d出两个额外的窗口，显示捕获和转换模式。

数据移植到Oracle数据库第3部分：(电脑编程网整理，请尊重作者权益，转载注明出处;)

移植

整个移植过程包括几个独立的步骤。当你已经连接到移植的目标数据库并创建了信息库之后，就可以按照以下的程序进行移植了：

1、 捕获源数据库

2、 转换捕获的数据库

3、 为新Orecle架构对象生成DDL脚本

4、 运行已生成的DDL脚本以创建新的用户和对象。

5、 如果要进行快速移植(Quick Migration)，就将源数据库的任何数据复制到新的数据库。Oracle SQL Developer也提供快速移植的功能。这个向导驱动的方法能够把架构和数据移植到Oracle数据库，并支持最小权限移植，即用户可以在不需要DBA权限的情况下将他们访问的源数据库对象移植到目标数据库。不需要用户插手，快速移植向导就可以自己完成整个移植过程。

从主菜单中选择Migration -> Quick Migrate，以调用Quick Migration选项。向导会提供一个对话框，让你选择源连接(与第三方数据库的连接)和目标连接(与Oracle的连接)。向导会检测你是否有可用的信息库，如果没有，它会在移植过程中创建一个。一旦移植完成，你同样可以选择保存还是删除这个信息库。点击Verify以连接第三方数据库和Oracle数据库，并验证已经赋予Oracle数据库中的架构必要的权限。验证完毕后，点击Migrate完成快速移植。

移植指南。使用以下的移植指南，你可以更加得心应手地掌控整个移植过程，并能够选择单个的表或架构对象进行移植。你还能够在移植过程的不同阶段随时进行更新，例如对架构进行重命名，在表被捕获后而被转换成Oracle模式前对其进行取消或重命名等 *** 作。我们还是按部就班一个一个来：

步骤一：捕获源数据库。右击Connections Navigator中的源数据库连接节点，选择Capture Microsoft SQL Server。会d出一个进度对话框，显示捕获过程的进度。显示完成时，点击Close，关闭对话框。源数据库被捕获后，元数据定义就储存在移植信息库中，随时都可以进行转换。Captured Objects窗口中将显示可用的捕获数据库。展开新的节点，浏览输出结果。这时候，你可以进行任何诸如对表进行重命名等修改 *** 作。

步骤二：转化捕获的数据库。如果你对第一步的结果没有任何疑问了，就右击新近捕获的模型，选择Convert to Oracle Model。这时，会d出一个数据图供你选择接受或修改。点击Apply选择默认设置。接着会d出一个进度对话框，当显示转化过程完成时，点击Close关闭对话框。捕获模型和转换模型都映射储存在移植信息库中的元数据。

',15,1) RETURN --DROP DATABASE DB_BEIJING END GO USE master GO --创建测试数库 CREATE DATABASE [DB_BEIJING] ON PRIMARY ( NAME = N'DB_BEIJING', FILENAME = N'X:\DATA\DB_BEIJINGmdf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) LOG ON ( NAME = N'DB_BEIJING_log', FILENAME = N'W:\Log\DB_BEIJING_logldf' , SIZE = 1024KB , FILEGROWTH = 1024KB ) GO --以下为修改过程 --step 1 : 修改数据库名称 USE master GO ALTER DATABASE DB_BEIJING SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO EXEC mastersp_renamedb 'DB_BEIJING','DB_SHANGHAI' GO ALTER DATABASE DB_SHANGHAI SET MULTI_USER GO --step 2 : 查看修改名称后的数据库逻辑名及物理文件名 USE master GO SELECT name AS [Logical Name], physical_name AS [DB File Path],type_desc AS [File Type], state_desc AS [State] FROM sysmaster_files WHERE database_id = DB_ID(N'DB_SHANGHAI') GO / Logical Name DB File Path File Type State DB_BEIJING X:\DATA\DB_BEIJINGmdf ROWS ONLINE DB_BEIJING_log W:\Log\DB_BEIJING_logldf LOG ONLINE / --step 3 : 修改数据库逻辑文件名称 USE master GO ALTER DATABASE DB_SHANGHAI SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO ALTER DATABASE DB_SHANGHAI MODIFY FILE (NAME=N'DB_BEIJING', NEWNAME=N'DB_SHANGHAI') GO ALTER DATABASE DB_SHANGHAI MODIFY FILE (NAME=N'DB_BEIJING_log', NEWNAME=N'DB_SHANGHAI_log') GO ALTER DATABASE DB_SHANGHAI SET MULTI_USER GO --step 4 : 修改数据库物理文件名称之前先打开xp_cmdshell支持 USE master GO sp_configure 'show advanced options',1 GO RECONFIGURE WITH OVERRIDE GO sp_configure 'xp_cmdshell', 1 GO RECONFIGURE WITH OVERRIDE GO --step 5 : 重命名数据库物理文件名称 USE [master] GO ALTER DATABASE DB_SHANGHAI SET OFFLINE WITH ROLLBACK IMMEDIATE GO EXEC xp_cmdshell 'RENAME "X:\DATA\DB_BEIJINGmdf", "DB_SHANGHAImdf"' GO EXEC xp_cmdshell 'RENAME "W:\Log\DB_BEIJING_logldf", "DB_SHANGHAI_logldf"' GO --step 6 : 将数据库逻辑名称指向新的物理文件，并将数据库online USE [master] GO ALTER DATABASE DB_SHANGHAI MODIFY FILE (NAME =DB_SHANGHAI, FILENAME = 'X:\DATA\DB_SHANGHAImdf') GO ALTER DATABASE DB_SHANGHAI MODIFY FILE (NAME =DB_SHANGHAI_log, FILENAME = 'W:\LOG\DB_SHANGHAI_logldf') GO ALTER DATABASE DB_SHANGHAI SET ONLINE --step 7 : 查看全部修改完成后的数据库情况 USE master GO SELECT name AS [Logical Name], physical_name AS [DB File Path],type_desc AS [File Type], state_desc AS [State] FROM sysmaster_files WHERE database_id = DB_ID(N'DB_SHANGHAI') GO / Logical Name DB File Path File Type State DB_SHANGHAI X:\DATA\DB_SHANGHAImdf ROWS ONLINE DB_SHANGHAI_log W:\LOG\DB_SHANGHAI_logldf LOG ONLINE / --step 8 : 关闭xp_cmdshell支持 USE master GO sp_configure 'xp_cmdshell', 0 GO RECONFIGURE WITH OVERRIDE GO sp_configure 'show advanced options',0 GO RECONFIGURE WITH OVERRIDE GO

以上就是关于sqlserver2012中表更新数据的命令是全部的内容，包括:sqlserver2012中表更新数据的命令是、谁总结下sqlserver数据库优化知识的详解和具体 *** 作最优方案！、sqlserver数据库移植到oracle问题等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！