[MSSQL] How to move Database Diagram from One Server to Another Server

Diagrams are stored in a "system" table named sysDiagrams. This table (only?) gets created when you click on the diagrams node in SSMS, it asks you if you want to create the objects that support diagrams, and you click "Yes". Do so on both your source and target databases.

Create the diagram or diagrams in the "source" database.

Review the structure and contents of sysDiagrams. Note that column diagram_id is an identity column. 1 row gets stored for every diagram. (You don't care, but it used to be 4 or 5 rows in SQL 2000.)
To copy to another database on the same SQL instance, the simplest way is to do INSERT... SELECT... between tables. With that identity column in the way, you'll have to fuss with SET IDENTITY_INSERT, and perhaps assign a new identity value on the target computer. Irritating, but not critically hard.

The following script will copy all diagrams from one database to another that's on the same server (this is how I archive complex diagrams that took waaaay too long to create, from databases that are prone to get dropped and recreated):

USE TargetDatabase
DELETE sysDiagrams
 where name in (select name from SourceDatabase.dbo.sysDiagrams)
SET identity_insert sysDiagrams on
INSERT sysDiagrams (name, principal_id, diagram_id, version, definition)
 select name, principal_id, diagram_id, version, definition
  from SourceDatabase.dbo.sysDiagrams
SET identity_insert sysDiagrams off

To copy to another database on a different SQL instance (or server), well, it gets even harder. I use temporarily created Linked Server definitions, using scripts I sweated bullets over years ago and never want to have to modify again (i.e. post a different question so someone who knows can tell you how they work), and modify the scripts with appropriate four-part naming conventions. Other options (OPENROWSET and the like) are possible, but I'm even less familiar with those.

[MSSQL] Comparing Table Variables with Temporary Tables

By Wayne Sheffield, 2012/06/08 (first published: 2009/06/10) 
 

There is a lot of confusion about what Table Variables are (and what they aren’t), and in how they compare to Temporary Tables. While there are a lot of articles on the internet about them, I haven’t found any that are complete. In this article, we’ll look into exactly what each are (and aren’t), and while we’re at it investigate some of the myths dealing with them, and see how accurate they are.

Table Variables

Table variables were first introduced in SQL 2000. So, what exactly is a table variable? Microsoft defines it in BOL (Declare @local_variable) as a variable of type table. Its definition includes column definitions, names, data types, and constraints. The only constraint types allowed are PRIMARY KEY, UNIQUE, NULL, and CHECK (FOREIGN KEY constraints are not allowed). The table definition is a subset of information used to define a table in the CREATE TABLE statement. They are created with the DECLARE @local_variable statement.

From Reference 1:

1. They have a well defined scope (the current batch of statements, but not any called procedures or functions). They are automatically cleared at the end of that batch.
2. Reference 6 discusses several reasons why temporary tables will force a recompilation of a stored procedure in the "Recompilations Due to Certain Temporary Table Operations" section. These reasons do not apply to table variables; therefore they can result in fewer recompilations of a stored procedure as compared to temporary tables.
3. Transactions against table variables last only for the duration of the update, therefore they require less locking and logging resources.
4. Since table variables have such a limited scope, and since they are not part of the persistent database, transaction rollbacks do not affect them.

A table variable can be used, within its scope, as you would any other table. Specifically, it can be used as a table or table expression anywhere they can be used in select, update, insert and delete statements. They cannot be used in a “SELECT select_list INTO table_variable” statement, and in SQL 2000 they cannot be used in an “INSERT INTO table_variable EXEC stored_procedure” statement.

 What you can’t do with table variables:

1. Although a table variable is a variable, it cannot be assigned to another table variable.
2. Check constraints, default values, and computed columns in the table definition cannot reference user-defined functions.
3. You cannot create a named constraint.
4. You cannot truncate a table variable.
5. You cannot insert explicit values into an identity column (the table variable does not support the SET IDENTITY_INSERT ON).
   

Temporary Tables

Before we dig into temporary tables, we first need to talk about sessions. A session is simply a connection to the database engine. In SQL Server Management Studio (SSMS), each open query window has its own connection to the database engine. An application can have one or more connections to the database engine; additionally the application may leave a connection open throughout the application, or it may constantly close the connection and establish a new connection when necessary.

So, what is a temporary table? In BOL (CREATE TABLE), we find that a temporary table has the same physical makeup as any other table created with the CREATE TABLE statement, except that:

1.  Its name is limited to 116 characters. This is because the database engine must be able to identify different temporary tables created by different sessions at the same time. To do so, it internally appends a numeric suffix to the end of the name.
2. Local temporary tables (created with a single “#”) are visible in the current connection, from the point the table is created and inside nested stored procedures. Local temporary tables will be dropped when:
   a. Explicitly dropped using DROP TABLE.
   b.If it is a local temporary table and it was created inside a stored procedure, it will be dropped when the stored procedure is finished.
   c. All other local temporary tables will be dropped at the end of the current session.
3. Global temporary tables (created with a double “##”) are visible to all sessions. You should always check for existence of the global temporary table before creating it… if it already exists, then you will get a duplicate object error.
   a. Global temporary tables are dropped when the session that created it ends, and all other sessions have stopped referencing it.
   b. This referencing is performed at the statement level. For an example of how this works:
        i. Open up a new query window, and run the command
   
            create table ##temp (RowID int)
   
        ii. Open up a second query window, and run the following commands which builds an endless loop that selects from the global temp table every 5 seconds:
   
           while 1=1
           begin
              select * from ##temp
              waitfor delay '00:00:05'
           end
   
        iii. Once the statements in the second window are running, go back to the first query window and close it.
        iv. The next time the loop tries to reference the global temp table, it will generate an error.
4. Temporary tables cannot be partitioned.
5. Foreign Key constraints cannot be applied to a temporary table.
6. Columns cannot be defined with user-defined data types (UDDT) that are not created in tempdb; you must use the native data types. (UDDTs are specific to the database, and temporary tables belong to tempdb.) Since the tempdb database is recreated each time SQL Server starts, you could use a startup stored procedure to add the UDDT to tempdb. Alternatively, since the model database is the baseline that all databases are created from, you could add the UDDT to the model database and it will be incorporated into tempdb when it is created.
7. Columns of the XML type cannot be defined with an xml collection unless the collection has been added to tempdb.

Temporary tables can be created with the CREATE TABLE statement, or by selecting into a table using the “SELECT INTO #table” syntax. You can use the “INSERT INTO #table EXEC stored_procedure” statement with a temporary table.

Temporary tables can have named constraints, indexes, etc. However, if two users are running the same procedure at the same time, the second one will get an error: “There is already an object named ‘’ in the database”. You will need to build the object without a name, thus creating a system-assigned name which will be unique in the tempdb database.

Reference 6 talks about many different ways that will cause temporary tables to cause stored procedure recompilations, and how to avoid / minimize them.

Myths

So, now that we know what these table types are, let’s investigate a few myths about them.
 
Myth #1: Table variables reside in memory only.
And the closely related Myth #2: Temporary tables are physical only.
Both of these are false. In Reference 1, section Q4, we find that table variables are created in the tempdb database, since the table variable could hold more data than could fit into memory. Additionally, we find that both table variables and temporary tables will be created and processed in memory if enough memory is available. However, they can both be flushed to disk at anytime.

Would you like to see some proof of this? Try this (this code works with SQL 2000 – 2008):

 -- make a list of all of the user tables currently active in the
 -- TempDB database
 if object_id('tempdb..#tempTables') isnot null droptable #tempTables
 select name into #tempTables from tempdb..sysobjectswhere type ='U'
 -- prove that even this new temporary table is in the list.
 -- Note the suffix at the end of it to uniquely identify the table across sessions.
 select * from #tempTables where name like '#tempTables%'
 GO
 -- create a table variable
 declare@MyTableVariable table (RowID int)
 -- show all of the new user tables in the TempDB database.
 select name from tempdb..sysobjects
  where type ='U' and name notin (select name from #tempTables)
 GO

(While the above code works for 2000 – 2008, the sysobjects view has been depreciated and will be removed from a future version of SQL Server. For 2005 and 2008, you should use the system view sys.tables instead.)

There have been several wrong ways that others have posted to “prove” that temporary tables reside completely in memory. I’d like to point out one of those:
Note that the name that is returned for the table variable is a system-assigned name. The first character of the table variable name (@) is not a letter, so it is not a valid identifier name. Therefore, the system creates a system-assigned name for use in tempdb. This means that looking for the name of your table variable in sysobjects or sys.tables will fail.

I’d also like to point out the proper way to check for temporary tables. Above, I checked for the existence of the temporary table by looking for its object_id. This is the correct way. I have seen people perform this query:

 select * from sysobjects where name like'#tempTables%'

While this command appears to work, it has multi-user issues. Open two query windows (which are two different connections). In the first create the temporary table, and in the second perform the above statement. This statement will return a row for the temporary table created in the first connection. If you then try to do anything with that table, you will get an error. The error is because the temporary table is not in scope for your session.
 
Myth #3: Table variables cannot have any indexes.
This myth is also false. It is true that once a table variable is created, you cannot perform any DDL statements against it, including a CREATE INDEX statement. However, you can create indexes on the table variable that are associated with a PRIMARY KEY or UNIQUE constraint if you define those constraints as part of the table definition. For example:
declare @MyTableVariable table (RowID intPRIMARY KEY CLUSTERED)

This will create a table variable with a clustered primary key on the RowID column. Since primary keys have an associated index, a system-named index will be created on the RowID column.

This next example shows how you can build a unique constraint against a column, and how you can build a primary key against multiple columns:

 declare @temp TABLE (
   RowID int NOT NULL,
   ColA int NOT NULL,
   ColB char(1)UNIQUE,
   PRIMARY KEY CLUSTERED(RowID, ColA)) 

What other differences are there?

1. SQL cannot build statistics against a table variable, like it can against temporary tables. This means that the execution plan for table variables will always treat the table as if it has 1 row. Obviously, this means that it might not use the best execution plan. Note that the estimated execution plan for both table variables and temporary tables will show 1 row; however the actual execution plan for temporary tables will change once SQL has recompiled the stored procedure (see Reference 1, Q2). Also note that if a temporary table doesn’t exist, you will get an “Invalid object name ‘’” error when creating the estimated execution plan.
2. As mentioned previously, you cannot perform any DDL statements against a table variable. For instance, you might have a need to populate a table, and then add an index or column. In this case, you will need to use a temporary table.
3. With table variables, you cannot select into a table variable using the SELECT INTO syntax. As demonstrated in the code above, you can do this with a temporary table.
4. With SQL 2008, you can pass a table variable to a procedure if you have defined a user-defined table type and this is the type for the parameter. You cannot do this with temporary tables, or with table variables in SQL 2000 or SQL 2005 (see Reference 5).
5. Scope: table variables are only visible while the current batch of statements is running, and they are not visible to any nested procedures. Local temporary tables are visible to the current session once created, including nested procedures; however they will not be visible to parent procedures. Global temporary tables are visible to all sessions until dropped and all other sessions have stopped using them.
6. Collation: table variables use the collation of the current database. Temporary tables use the collation of the tempdb database. If they are not compatible, then you will need to specify the collation to use in either the queries or the table definition. (Reference 7, Table Variables and Temporary Tables)
7. If you want to use a table variable in dynamic SQL, you must define the table variable in the dynamic SQL code. A temporary table that was created before calling the dynamic SQL can be used inside the dynamic SQL.
   

So, what should I use?

Microsoft recommends using table variables (in Reference 4). If the number of rows that you are going to be inserting into the table is very small, then use a table variable. Most of the “internet gurus” will tell you to have less than 100 records as a guideline, as this is where the lack of statistics start to mess up a query – but they will also tell you to test your specific needs against both methods. Some people will only use table variables within user-defined table functions (which require them). If you can use an index from either a PRIMARY KEY or UNIQUE constraint on a table variable, then you could get excellent performance from table variables that contain tens of thousands of records. This is primarily true if you don’t have to join the table variable to another table. When you start joining the table variable to other tables, then the cardinality error frequently results in a bad execution plan, which gets worse the more complex the query is, and the larger the row count is.

To illustrate this, see the attachment to this article. It creates both a temporary table and a table variable, and populates each of these tables with the contents of the AdventureWorks.Sales.SalesOrderDetail table. In order to get a large data size (> 1 million rows), I perform 10 inserts on each table, with each insert being in a different order. I then perform an identical query on each table, joining up against the AdventureWorks.Sales.SalesOrderHeader table and filter by the ModifiedDate field. If you examine the output statistics, you will see a remarkable difference in the number of logical reads on the joined table between the select with the temporary table versus the select with the table variable. The same data, running the same query, is producing very different performance results. Time wise (on my system) it consistently takes the table variable over 50 seconds, while the temporary table takes under 8 seconds.
If your process requires you to perform DDL statements on the table after creation, then you will need to use a temporary table.

Temp Tables and Table Variables have overlapping purposes, so there's no real concrete answer to which you should use. For any given situation, you'll find the optimum only by examining the advantages, disadvantages, and characteristics of each and by doing some simple performance testing. The side-by-side comparison in the following "Summary" section should make the first part of that a bit easier to accomplish.


Summary

Feature	Table Variables	Temporary Tables
Scope	Current batch	Current session, nested stored procedures. Global: all sessions.
Usage	UDFs, Stored Procedures, Triggers, Batches.	Stored Procedures, Triggers, Batches.
Creation	DECLARE statement only.	CREATE TABLE statement. SELECT INTO statement.
Table name	Maximum 128 characters.	Maximum 116 characters.
Column data types	Can use user-defined data types. Can use XML collections.	User-defined data types and XML collections must be in tempdb to use.
Collation	String columns inherit collation from current database.	String columns inherit collation from tempdb database.
Indexes	Can only have indexes that are automatically created with PRIMARY KEY & UNIQUE constraints as part of the DECLARE statement.	Indexes can be added after the table has been created.
Constraints	PRIMARY KEY, UNIQUE, NULL, CHECK, but they must be incorporated with the creation of the table in the DECLARE statement. FOREIGN KEY not allowed.	PRIMARY KEY, UNIQUE, NULL, CHECK. Can be part of the CREATE TABLE statement, or can be added after the table has been created. FOREIGN KEY not allowed.
Post-creation DDL (indexes, columns)	Statements are not allowed.	Statements are allowed.
Data insertion	INSERT statement (SQL 2000: cannot use INSERT/EXEC).	INSERT statement, including INSERT/EXEC. SELECT INTO statement.
Insert explicit values into identity columns (SET IDENTITY_INSERT).	The SET IDENTITY_INSERT statement is not supported.	The SET IDENTITY_INSERT statement is supported.
Truncate table	Not allowed.	Allowed.
Destruction	Automatically at the end of the batch.	Explicitly with DROP TABLE statement. Automatically when session ends. (Global: also when other sessions have no statements using table.)
Transactions	Last only for length of update against the table variable. Uses less than temporary tables.	Last for the length of the transaction. Uses more than table variables.
Stored procedure recompilations	Not applicable.	Creating temp table and data inserts cause procedure recompilations.
Rollbacks	Not affected (Data not rolled back).	Affected (Data is rolled back).
Statistics	Optimizer cannot create any statistics on columns, so it treats table variable has having 1 record when creating execution plans.	Optimizer can create statistics on columns. Uses actual row count for generation execution plan.
Pass to stored procedures	SQL 2008 only, with predefined user-defined table type.	Not allowed to pass, but they are still in scope to nested procedures.
Explicitly named objects (indexes, constraints).	Not allowed.	Allowed, but be aware of multi-user issues.
Dynamic SQL	Must declare table variable inside the dynamic SQL.	Can use temporary tables created prior to calling the dynamic sql.

 
References:
1) INF: Frequently Asked Questions - SQL Server 2000 - Table Variables
2) T-SQL BOL (SQL 2000), table data type
3) T-SQL BOL (SQL 2008), Declare @local_variable
4) T-SQL BOL (SQL 2008), CREATE TABLE
5) Table-Valued Parameters (Database Engine)
6) Troubleshooting stored procedure recompilation
7) Local Temporary Tables and Table Variables
8) Startup stored procedure
9) Data Definition Language (DDL)
 
Other articles worth reading:
1) Things You Didn’t Know About Temp Tables and Table Variables
 
Acknowledgements:
I would like to acknowledge and thank Jeff Moden, Gail Shaw, Gus Gwynne and Lynn Pettis for their review and the constructive feedback that they gave for this article.

Resources:

Performance Test Temp Tables and Table Vars.sql | Comparing Table Variables.doc

[TOOL] How to Edit PDF Files without Adobe Acrobat

Looking for software to edit your PDF documents? This guide describes free alternatives to Adobe Acrobat that will help you edit and save PDF files.

Rich Text Editor Control www.devexpress.com
WinForms Word Processing and Export DOC, PDF, HTML, OOo, RTF.
The PDF file format was originally created by Adobe in the early ’90s and there are now over 700+ million PDF documents on the Internet according to Google (search for filetype:pdf). There are several reasons why the PDF file format is still so popular for exchanging all sorts of documents including presentations, portfolios, CAD Drawings, invoices and even legal forms.

• PDF files are generally more compact (smaller in size) than the source document and they preserve the original formatting.
• Unlike Word and other popular document formats, the content of a PDF file cannot be modified easily. You can also prevent other users from printing or copying text from PDF documents.
• You can open a PDF file on any computer or mobile device with free software like Adobe Acrobat Reader. Google Chrome can read PDFs without requiring plugins and it can create PDFs.
  

Edit PDF Files using Free Alternatives to Adobe Acrobat

While PDF Files are “read only” by default, there are ways by which you can edit certain elements* of a PDF document for free without requiring the source files or any of the commercial PDF editing tools like Adobe Acrobat.

We will primarily focus on tools that let you alter the actual contents of a PDF file>. If you are looking to manipulate the PDF file structure itself like rearranging pages or merging multiple PDFs into one, please refer to this detailed Adobe PDF Guide.

An Online PDF Editor for Basic Tasks

Sometimes you need to make minor changes to a PDF file. For instance, you may want to hide your personal phone number from a PDF file before uploading it online or may want to annotate a page with notes and freehand drawings.



You can perform such edits in a PDF easily with PDFEscape.com, an online PDF editor that is free and also lets you edit password-protected PDF documents in the browser.

With PDF Escape, you can hide* parts of a PDF file using the whiteout tool or add annotations with the help of custom shapes, arrows, text boxes and sticky notes. You can add hyperlinks to other PDF pages / web documents.

[*] Hiding is different from redaction because here we aren’t changing the associated metadata of a PDF file but just hiding certain visible parts of a PDF file by pasting an opaque rectangle over that region so that the stuff beneath the rectangle stays invisible.

Change Metadata of PDF Files

If you would like to edit the meta-data associated* with a PDF document, check out Becy PDFMetaEdit. This is a free utility that can help you edit properties of a PDF document including the title, author name, creation data, keywords, etc.
The tool can also be used for encrypting PDF documents such that only users who know the password can read the contents of your PDF files. And since this PDF metadata cum bookmarks editor can be executed from the command line, you can use it to update information in multiple PDF files in a batch.


[*] If you planning to post your PDF files on the web, you should consider adding proper metadata to all the files as that will help improve the organic rankings of your PDF files in Google search results.

Edit the Text of a PDF File

If you want to edit the text in a PDF file but don’t have access to the source documents, your best bet is that you convert the PDF file into an editable Word document or an Excel spreadsheet depending on the contents of the PDF.

Then edit these converted PDFs in Microsoft Office (or Google Docs) and export the modified files back into PDF format using any PDF writer.

If your PDF document is mostly text, you may use the desktop version of Stanza to convert that PDF into a Word document. If the document includes images, charts, tables and other complex formatting, try the online PDF to Word converter from BCL Research or the one from NitroPDF – the former offers instant conversion while the latter service can take up to a day though its yields more accurate results.

Advanced PDF Editing (Images, text, etc.)

Now that you know the basic PDF editing tools, let’s look at another set of PDF editors that are also free but can help you do some more advanced editing like replacing images on a PDF file, adding signatures, removing blocks of text without breaking the flow of the document, etc.



First there’s PDF XChange, a free PDF viewer cum editor that you also may use for typing text directly on any PDF page. PDF XChange also supports image stamps so you may use the tool for signing PDF files or for inserting images anywhere on a PDF page.

Then you have Inkscape, a free vector drawing tool (like Adobe Illustrator) that can natively import and export PDF content.

Video: How to Edit PDF Files with Inkscape

With Inkscape, you can select any object on a PDF page (including text, graphics, tables, etc.) and move them to a different location or even remove them permanently from the PDF file. You can also annotate PDF files with Inkscape or draw freehand on a page using the built-in pencil tool.

The next tool in the category of advanced PDF editors is OpenOffice Draw with the PDFImport extension. OpenOffice Draw supports inline editing so you can easily fix typos in a PDF document or make formatting related changes like replacing color, increasing or decreasing the text size, replacing the default font-family, etc.

Like Inkscape, the OpenOffice toolbox also includes support for annotations, shapes, images, tables, charts, etc. but here you have more choices and the software also looks less complex.



The OpenOffice suite is a little bulky (they don’t provide a standalone installer for Draw) but if you have the bandwidth, OpenOffice is the best tool for manipulating PDF documents when you don’t have the budget for Adobe Acrobat.