[mssql] How do I use GETDATE() within a User-Defined Function (UDF)?

Question:
Why I can't do:

DECLARE @dTimeStamp datetime

SELECT @dTimeStamp = getdate()

Inside of a function? ... I get an error:
Msg 443, Level 16, State 1, Procedure fcnName, Line 9

Invalid use of 'getdate' within a function.

Answer:
For some reasons Getdate() is not allowed in functions, and a few other places. This was a limitation in 2000, not anymore in 2005. Getdate() is nondeterministic function which are not allowed in 2000

Solution:
The work-around for that I use, if necessary, is to create a view based off of getdate() and call that view inside the function.

CREATE VIEW uvw_Now

AS

  SELECT getdate() AS now

GO

CREATE FUNCTION udf_Now()

RETURNS datetime

AS

BEGIN

  DECLARE @Now DATETIME

  SELECT @Now = now FROM uvw_Now

  RETURN @Now

END

GO

SELECT udf_Now()

[EXCEL] How to show cell value in toolbar

Question: Entries in cells used to show up in a toolbar box but no longer does. I don't
know what I could have done to remove it, but I can't find a place to restore
this.

Answer: Go to menu [View] => Klik on [Formula Bar]

[MSSQL] Check If Temporary Table Exists

How do you check if a temp table exists?
You can use IF OBJECT_ID('tempdb..#temp') IS NOT NULL
Let's see how it works

--Create table
USE Norhtwind
GO

CREATE TABLE #temp(id INT)

--Check if it existsIF OBJECT_ID('tempdb..#temp') IS NOT NULL
BEGIN
  PRINT '#temp exists!'END
ELSE
BEGIN
  PRINT '#temp does not exist!'END

--Another way to check with an undocumented optional second parameterIF OBJECT_ID('tempdb..#temp','u') IS NOT NULL
BEGIN
  PRINT '#temp exists!'END
ELSE
BEGIN
  PRINT '#temp does not exist!'END

--Don't do this because this checks the local DB and will return does not existIF OBJECT_ID('tempdb..#temp','local') IS NOT NULL
BEGIN
PRINT '#temp exists!'END
ELSE
BEGIN
PRINT '#temp does not exist!'END

--unless you do something like this
USE tempdb
GO

--Now it exists againIF OBJECT_ID('tempdb..#temp','local') IS NOT NULL
BEGIN
  PRINT '#temp exists!'END
ELSE
BEGIN
  PRINT '#temp does not exist!'END

--let's go back to Norhtwind again
USE Norhtwind
GO

--Check if it existsIF OBJECT_ID('tempdb..#temp') IS NOT NULL
BEGIN
PRINT '#temp exists!'END
ELSE
BEGIN
PRINT '#temp does not exist!'END

now open a new window from Query Analyzer (CTRL + N) and run this code again
--Check if it existsIF OBJECT_ID('tempdb..#temp') IS NOT NULL
BEGIN
  PRINT '#temp exists!'END
ELSE
BEGIN
  PRINT '#temp does not exist!'END

It doesn't exist and that is correct since it's a local temp table not a global temp table

Well let's test that statement
--create a global temp table
CREATE TABLE ##temp(id INT) --Notice the 2 pound signs, that's how you create a global variable

--Check if it existsIF OBJECT_ID('tempdb..##temp') IS NOT NULL
BEGIN
  PRINT '##temp exists!'END
ELSE
BEGIN
  PRINT '##temp does not exist!'END

It exists, right?
Now run the same code in a new Query Analyzer window (CTRL + N)

--Check if it existsIF OBJECT_ID('tempdb..##temp') IS NOT NULL
BEGIN
  PRINT '##temp exists!'END
ELSE
BEGIN
  PRINT '##temp does not exist!'END

And yes this time it does exist since it's a global table (with ## (double-dash) as a prefix).

[MSSQL] Access to the remote server is denied because no login-mapping exists

Problem: Unable to run a query through a linked server SQL Server 2005. This problem only happens with a non-sysadmin account.

You got the message below:
Msg 7416, Level 16, State 2, Line 1
Access to the remote server is denied because no login-mapping exists..

Cause: When creating a linked server with the parameter @provstr and you use a local SQL Server non-admin or non-Windows account, you have to add the parameter "User Name"   into the @provstr

Resolution : Add "User ID=Username" into the provider string on your linked server

EXEC master.dbo.sp_addlinkedserver @server = N'LinkServerName', @provider=N'SQLNCLI',@srvproduct = 'MS SQL Server', @provstr=N'SERVER=serverName\InstanceName;User ID=myUser'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'LinkServerName', @locallogin = NULL , @useself = N'False', @rmtuser = N'myUser', @rmtpassword = N'*****'

Check:
SELECT  TOP 1 * FROM LinkServerName.msdb.dbo.backupset
GO
SELECT * FROM OPENQUERY (LinkServerName, 'SELECT TOP 1 * FROM msdb.dbo.backupset ')

[EXCEL] How to count the occurrences of a number or text in a range in Excel

How to Count the Occurrences of a Number

Use this formula

=SUM(IF(range=number,1,0))

where range is the range that you want to search, and number is the number that you want to count.

NOTE: This formula must be entered as an array formula. To enter an array formula, press CTRL+SHIFT+ENTER.

How to Count the Occurrences of a Text String

Method 1

Use this formula

=SUM(IF(range="text",1,0))

where range is the range that you want to search, and text is the text that you want to find (the text must be enclosed in quotation marks).

NOTE: The above formula must be entered as an array formula. To enter an array formula, press CTRL+SHIFT+ENTER.

Method 2

Use the COUNTIF() function to count the occurrences of a text string. For example, use the formula

=COUNTIF(range,"text")

where range is the range of cells that you are evaluating, and text is the text string that you want to count instances of (note that text must be enclosed in quotation marks).

NOTE: This formula must be entered as an array formula. To enter an array formula, press CTRL+SHIFT+ENTER.

Wildcard characters can be used within the COUNTIF function.

The asterisk character (*) represents more than one character. For example, to count all the cells in the range a1:a10 that contain an "x," you can use the following formula:

=COUNTIF(a1:a10,"*x*") 

 

The question mark character (?) can also be used to represent one wildcard character -- for example, to count all cells in the range whose second character is the letter, such as "ax" or "bx."

=COUNTIF(a1:a10,"?x*")

[MSSQL] Setup SQL Server Linked Server to MySQL

Despite being completely proprietary, one of the nice connectivity features offered in SQL Server is the ability to query other servers through a Linked Server. Essentially, a linked server is a method of directly querying another RDBMS; this often happens through the use of an ODBC driver installed on the server.

Fortunately, many popular databases provide this ODBC driver, giving SQL Server the ability to connect to a wide range of other systems. I’ve already written about how to connect Oracle and SQL Server. In this post, I’m going to go through the steps necessary to connect SQL Server and MySQL.

The first step is to fetch an appropriate MySQL Connector/ODBC 5.1 download. Drivers are available for a variety of OS‘s, but we’re obviously focused on Windows or Window x64, which should correspond to the version of SQL Server installed. After you’ve downloaded and installed the driver, we have a few things to configure, so let’s get started:

Configure a MySQL DSN

The first step is to configure a MySQL data source by running the ODBC Data Source Administrator. This step is technically entirely optional, but allows a simpler configuration in the SQL Server Linked Server settings. Instead of composing a complicated MySQL connection string, we can use a simple GUI application.

Run odbcad32

If you’re using Windows Server 2003, bring up a Run dialog box with Start→Run or WinKey+R. If you’re using Windows Server 2008, use the Start Menu search box directly. In either OS, type in “odbcad32″ and hit Enter.

System DSN

Select the System DSN tab to configure a data source for the entire system. If you only want to create the DSN for a specific user (such as your service account), use the User DSN tab. In either scenario, select the “Add…” button.

Create New Data Source

Scroll down in the Create New Data Source window and select “MySQL ODBC 3.51 Driver” and click “Finish”.

MySQL Connector Login Settings

Once added, clicking the “Configure…” button will bring up the Connector/ODBC 3.51 Configure Data Source application. This is where you can specify all the connection settings for connecting SQL Server to MySQL. Select a Data Source Name – I typically name it after the application or database I’m connecting to. The Server, User, Password, and Database should all be obvious.

Test ODBC Connection

After you’ve entered all the required parameters, click the “Test” button to ensure a connection can be made to the MySQL server.

These settings are the bare minimum required to connect MySQL and SQL Server via a linked server, but I like to specify additional options to optimize the connection between the servers. Without these, I have run into “Out of Memory” errors that require restarting the service.

MySQL Connector Advanced Settings - Connection

Select the Advanced tab and you’ll be placed on the “Connection″ sub-tab. Check the boxes labeled “Allow big result sets” and “Use compressed”.

Next, switch to the “Cursors/Results″ tab and select “Don’t cache results of forward only cursors”. This can actually be a performance penalty if you perform the same query multiple times to the same linked server. However, in my experience, the reason to connect SQL Server to MySQL, is to pull data into a single server, in which case, this option is perfectly suited.

Select “Force use of forward-only cursors”. When you’re done setting all these options, select the “OK” button.

MySQL Connector Advanced Settings - Cursors/Results

 

Configure Linked Server Provider

Adjusting the Linked Server Provider is simple, but it comes with a caveat: When adjusting a provider, you are adjusting it for all connections using that provider. I am not aware of any way to change these settings on a per-connection basis.

Provider Properties

Drill down to Server Object → Linked Servers → Providers, right-click MSDASQL, and select “Properties”.

Set Provider Options

The Provider Options for Microsoft OLE DB Provider for ODBC Drivers dialog box will open allowing you to configure several options. Ensure the following four options are checked:

• Nested queries
• Level zero only
• Allow inprocess
• Supports ‘Like’ Operator

All other options should be unchecked. When done, click “OK”.

Create Linked Server to MySQL

Finally, the last step in our process is to create the actual MySQL Linked Server.

Create a New Linked Server

You should already have Linked Servers expanded in the Object Explorer tree. If not, find it in Server Objects → Linked Server. Once there, right-click Linked Servers and select “New Linked Server…”

New linked Server Settings

The New Linked Server dialog box will open. Because we specified all our connection settings in the ODBC Data Source Administrator, this last step is very simple. Name the linked server. As with the Data Source Name, I like to name it after the product or database I’m connecting to. In my example, I used MYSQLAPP. Ensure that the “Other data source” option is selected and choose “Microsoft OLE DB Provider for ODBC Drivers” from the Provider dropdown. Lastly, specify the Product name and Data source. The Product name doesn’t matter so much as the Data source must match what you provided in the MySQL Connector/ODBC configuration. Press “OK” when complete.

Testing the SQL Server to MySQL connection

If everything has been set correctly, you should be able to execute a query directly again the MySQL database from SQL Server Management Studio. For example:

SELECT TOP 10 TABLE_NAME FROM MYSQL_Data...tables 
  WHERE TABLE_TYPE != 'MEMORY' 

If you’ve done everything correctly, you should get back a result set. There are several error message you might receive:

OLE DB provider "MSDASQL" for linked server "MYSQL_DATA" returned message
  "[Microsoft][ODBC Driver Manager] Data source name not found and no default
  driver specified".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for
  linked server "MYSQL_DATA".

The message indicates that the Data source name you’ve specified for the linked server does not match that of the Data Source Name specified in the MySQL Connector.

Msg 7313, Level 16, State 1, Line 1
An invalid schema or catalog was specified for the provider "MSDASQL"
  for linked server "MySQLApp".

This uninsightful error is a result of not correctly setting the options for the Linked Server Provider.

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "MySQLApp" reported an
  error. The provider did not give any information about the error.
Msg 7312, Level 16, State 1, Line 1
Invalid use of schema or catalog for OLE DB provider "MSDASQL" for linked
  server "MySQLApp". A four-part name was supplied, but the provider does
  not expose the necessary interfaces to use a catalog or schema.

This “four-part name” error is due to a limitation in the MySQL ODBC driver. You cannot switch catalogs/schemas using dotted notation. Instead, you will have to register another DSN and Linked Server for the different catalogs you want to access. Be sure and follow the three-dot notation noted in the example query.

If, however, you want to access other schemas, you can do so utilizing OPENQUERY. This is also a great way to test your connection if you’re receiving problems. The syntax looks like this:

SELECT * FROM OPENQUERY(MYSQLAPP, 
  'SELECT * FROM INFORMATION_SCHEMA.TABLES LIMIT 10')

Notice that the actual query syntax in the string must be in the MySQL format (SQL Server does not support the LIMIT keyword). Additionally, you can specify a different schema using SCHEMA.TABLENAME in the query.

Conclusion

Creating a linked server between SQL Server and MySQL is a simple process. The first time requires you to install the software and configure the Linked Server Provider, but all subsequent connections require only a DSN and Linked Server.

[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.

[vb6] How to change the Color of a command button, or show a picture on it?

---

In order to be able to change the BackColor property of a command button you must also set Style property of the button to "Graphical" as shown below. If you don't then you will not see any difference in the color of the button. A similar effect is also true for the the Picture property.




The reason for this behaviour is that "Standard" means the button will be shown in the normal Windows style (no picture, and a standard color for all programs) instead of having the full graphical functionality that VB's command button provides.

Once the style has been set to "Graphical" you can set the backcolor or picture in the properties window.

[MSSQL] Data Types and Ranges (SQL Server 2005 and SQL Server 2000)

Exact numerics

Type	From	To
bigint	-9,223,372,036,854,775,808	9,223,372,036,854,775,807
int	-2,147,483,648	2,147,483,647
smallint	-32,768	32,767
tinyint	0	255
bit	0	1
decimal	-10^38 +1	10^38 –1
numeric	-10^38 +1	10^38 –1
money	-922,337,203,685,477.5808	+922,337,203,685,477.5807
smallmoney	-214,748.3648	+214,748.3647

numeric and decimal are Fixed precision and scale data types and are functionally equivalent.

Approximate numerics

Type	From	To
float	-1.79E + 308	1.79E + 308
real	-3.40E + 38	3.40E + 38

datetime and smalldatetime

Type	From	To
datetime (3.33 milliseconds accuracy)	Jan 1, 1753	Dec 31, 9999
smalldatetime (1 minute accuracy)	Jan 1, 1900	Jun 6, 2079

Character Strings

Type	Description
char	Fixed-length non-Unicode character data with a maximum length of 8,000 characters.
varchar	Variable-length non-Unicode data with a maximum of 8,000 characters.
varchar(max)	Variable-length non-Unicode data with a maximum length of 231 characters (SQL Server 2005 only).
text	Variable-length non-Unicode data with a maximum length of 2,147,483,647 characters.

Unicode Character Strings

Type	Description
nchar	Fixed-length Unicode data with a maximum length of 4,000 characters.
nvarchar	Variable-length Unicode data with a maximum length of 4,000 characters.
nvarchar(max)	Variable-length Unicode data with a maximum length of 230 characters (SQL Server 2005 only).
ntext	Variable-length Unicode data with a maximum length of 1,073,741,823 characters.

Binary Strings

Type	Description
binary	Fixed-length binary data with a maximum length of 8,000 bytes.
varbinary	Variable-length binary data with a maximum length of 8,000 bytes.
varbinary(max)	Variable-length binary data with a maximum length of 231 bytes (SQL Server 2005 only).
image	Variable-length binary data with a maximum length of 2,147,483,647 bytes.

Other Data Types

• sql_variant: Stores values of various SQL Server-supported data types, except text, ntext, and timestamp.
• timestamp: Stores a database-wide unique number that gets updated every time a row gets updated.
• uniqueidentifier: Stores a globally unique identifier (GUID).
• xml: Stores XML data. You can store xml instances in a column or a variable (SQL Server 2005 only).
• cursor: A reference to a cursor.
• table: Stores a result set for later processing.