Tips on how to analyze the execution plan, use of sub-queries, methods for the substitution of cursors, use of indexed column in the where clause, and tips that surely will help you to get a greater benefit from the SQL Server.
Well, given this small introduction, let us move on to what matters.
How to Move Records with a Single SQL Statement:
Since SQL Server 2005 there has existed a really easy way of moving records from one table to another using a single SQL statement. You can combine the actions of deleting records from one table and copying the records that are being deleted into another table using a combination of the DELETE statement, OUTPUT clause and the virtual table, "DELETED". How? Well, here's a real simply example:
In this example we create and populate a table with a list of people, their gender and the country they come from, as follows:
USE AdventureWorks2008
GO
CREATE TABLE person_all
(
name VARCHAR(48),
gender VARCHAR(48),
country VARCHAR(48)
);
INSERT INTO person_all
VALUES ('John','Male','USA'),
('Michele','Female','UK'),
('Rose','Female','USA'),
('Mitch','Male','UK'),
('Sam','Male','UK'),
('Billy','Male','UK'),
('Monica','Female','USA'),
('Christiana','Female','UK');
Now, say we want to separate the people from the USA into a new table. We can create a new table for people from the USA with the same structure as the original person_all table:
CREATE TABLE person_usa
(
name VARCHAR(48),
gender VARCHAR(48),
country VARCHAR(48)
);
Now comes the clever, though essentially very simple, combo SQL statement that does all the work:
DELETE FROM person_all
OUTPUT DELETED.* INTO person_usa
WHERE country = 'USA';
Yep, that's it! You can see that the person_usa table is specified as the OUTPUT table, and the virtual table "DELETED" enables you to select fields (from the records being deleted) that you want to copy to your new person_usa table.
Insert Multiple Records with One Insert Statement
Up until SQL Server 2005 the only way to insert multiple records into a table using a single select statement was by using a SELECT statement within the INSERT. Take for example the table "car" that we create below:
USE AdventureWorks2008
GO
CREATE TABLE car
(
manufacturer VARCHAR(48),
model varchar(48),
color varchar(48)
);
To insert 3 specific records into the table you would need 3 insert statements, such as:
INSERT INTO car VALUES ('Ford','Focus','Black');
INSERT INTO car VALUES ('Jaguar','X-Type','Blue');
INSERT INTO car VALUES ('Citroen','AX','Silver');
As from SQL Server 2008 onwards it has been possible to insert more than one records into a database table with a single INSERT statement. SO, for example, the above 3 insert statements can be combined as follows:
INSERT INTO car
VALUES ('Ford','Focus','Black'),
('Jaguar','X-Type','Blue'),
('Citroen','AX','Silver');
Of course, inserting data in this way is more efficient, and make scripts that include INSERT statements easier to read.. and write!
Find Out When a SQL Sever Table Was Last Updated
The way I establish when a SQL server table was last updated is through the sys.dm_db_index_usage_stats database management view (DMV) as follows:
select last_user_update
from sys.dm_db_index_usage_stats
where DB_NAME(database_id) = 'your_database_name'
and OBJECT_SCHEMA_NAME(object_id, database_id) = 'your_table_name'
During a development phase, especially where there is more than one developer working on an application, you may end up with the occasional redundant table used for testing, moving data or for other reasons that you wont want to promote to your test environment or production. The above query helps to identify such tables where the last_user_update is NULL or not recent.
Another application is where you want to find out the sequence of table inserts/updates after running a number of DML queries – such as after the execution of an SSIS package. You could run the following query to see the the tables updated/inserted into and the order of these actions using a query similar to the following:
select last_user_update, OBJECT_SCHEMA_NAME(object_id, database_id) as table_name
from sys.dm_db_index_usage_stats
where DB_NAME(database_id) = 'your_database_name'
order by last_user_update
Of course, this DMV has alot of other useful info that you can query in a similar way using the DB_NAME() and OBJECT_SCHEMA_NAME() or OBJECT_NAME() functions.
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-discuss+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/2cd7db19-8ab6-4f26-bd80-6766f8973021%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
No comments:
Post a Comment