sql server compare two tables for differences

Label:1, longitudinal table turn horizontal table TABLE_A structure of the longitudinal table: Post-conversion structure: SQL example of a vertical table to a table: SELECT Name,SUM (case when Course = N ' language ' then GradeELSE 0END) as Chinese,SUM (case when Course = N ' math ' then GradeELSE 0END) as Mathematics,SUM (case when Course = N ' English ' then GradeELSE 0END) as 中文版FROM dbo. Table_aGROUP by Name 2, horizontal table to the longitudin

The pivot and Unpivot relational operators are the new features provided by SQL Server 2005, so when you use pivot and UNPIVOT for databases that are upgraded to SQL Server 2005, the compatibility level of the database must be set to 90 (you can use sp_ Dbcmptlevel stored procedures to set the compatibility level. Usi

The pivot and Unpivot relational operators are the new features provided by SQL Server 2005, so when you use pivot and UNPIVOT for databases that are upgraded to SQL Server 2005, the compatibility level of the database must be set to 90 (you can use sp_ Dbcmptlevel stored procedures to set the compatibility level. Usin

Label:How to obtain primary foreign key relationships in SQL Server 2008:Transferred from: http://www.cnblogs.com/ke10/archive/2012/06/11/2544655.htmlSELECT object_name (con.constid) ' Relationship name ',object_name (Sf.fkeyid) ' primary key table ',Fcol.name ' primary key ',object_name (Sf.rkeyid) ' foreign key table ',Rcol.name ' foreign key ',St.name ' data type 'From Sysforeignkeys SFINNER JOIN syscons

Label:Find the table name or the name of the stored procedure in the system table before processing it with a cursor Ps:sqlserver 2000 is using the system table is sysobjects, the Type field is: xtype; The system table for SQL Server 2005 or later is sys.objects, and the Type field is type In this article, for example, Sql2005, Sql2000 version, please follow the above instructions to replace Note the value

updates. Merging and copying provides multiple methods to handle conflicts. Disadvantages of replication:The table has a primary key, and the table structure cannot be changed in the future. If the architecture is stable, it will be troublesome if there are many tables. Replication method and process: Http://www.cnblogs.com/dudu/archive/2010/08/26/1808540.html Http://www.cnblogs.com/killkill/archive/2009/07/17/1525733.html Http://dufei.blog.51cto.com

Because of the unreasonable preliminary database design, the length of the primary and Foreign keys associated with multiple tables is inconsistent. As a result, when the fields that are too long in the primary and Foreign keys are filled with the same field, the extra part is filled with null characters. The trim () method is used in the where statement associated with multiple tables. Because of the unre

, Ss.sum_total_logi Cal_writes from (SELECT s.plan_handle, SUM (s.execution_count) Sum_execution_count, SUM (s.total_elapsed_time) sum_total_elapsed_time, sum (s.total_worker_time) Sum_total_worker _time, sum (s.total_logical_reads) sum_total_logical_reads, sum (s.total_logical_w Rites) sum_total_logical_writes from Sys.dm_exec_query_stats s GROUP by S.plan_handle) As SS Cross APPLY sys.dm_exec_sql_text (ss.plan_handle) T ORDER by Sum_total_logical_reads DESC

Label:2010-01-26 sp_spaceused can view the space occupied by a table, but cannot view all the tables at once. Today, we studied this SP and wrote the following query: --Refresh system dataDBCC UPDATEUSAGE (0) with NO_INFOMSGS --8k per pageSelect name = Name,id,rows = Convert (char (one), rows),reserved = LTrim (str (reservedpages * 8.,15,0) + ' KB '),data = LTrim (str (pages * 8.,15,0) + ' KB '),index_size = LTrim (str ((usedpages-pages) * 8.,15,0) +

SQL Server temporary tables are frequently used. The following describes how to use SQL Server temporary tables to prevent repeated user logon. When developing business software, we often encounter the following problem: how to pr

' From SYS. dm_db_partition_stats As PS Inner Join SYS. Objects As So On PS. Object_id = So. Object_id Where Index_id 1 And So. Type = ' U ' Group By So. Name, so. Type Order By Sum (Row_count) Desc -- -------------------------------------------------------------------- /* DBCC statement: This command set is used to check SQL server statistics, trace activities, and check database

Tags: arc sql ima LTE div database pre hone alterCreate DATABASE test go use test go CREATE TABLE UserInfo ( Id INT IDENTITY PRIMARY KEY, Name VARCHAR, Age INT, Sex BIT, Address VARCHAR (+) ) GO ALTER TABLE dbo. UserInfo ADD Phone VARCHAR (one) ALTER TABLE dbo. UserInfo ADD CONSTRAINT Age_c CHECK (age>0 and age　　SQL SERVER build libraries,

after the where can be added not --7 alias as SelectB.freezetime,b.* fromQ_user asbSelectB.* from(Select * fromQ_user) bSelectUid asNumber, uName name, upwd password fromQ_user--8 Top Query (how many lines before query) --SQL Available Select Top Ten * fromQ_userwhereUid not inch(Select Top TenUid fromQ_user)--query not the first 10 data (i.e. 20-30 data) in the first 10 Select * fromQ_user--Aggregation Functions --avg Select AVG(uId) asAverage