This functionality is already built in to Sql Server Management Studio 2008. Just download the trial and only install the client tools (which shouldn’t expire). Use Management Studio 2008 to connect to your 2005 database (its backwards compatible). Right click your database Choose Tasks > Generate Scripts Press Next, select your database again On the … Read more
This is a problem that is pretty common: generating a relation on the fly without creating a table. SQL solutions for this problem are pretty awkward. One example using a derived table: SELECT n.id FROM (SELECT 2 AS id UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7) AS … Read more
Another solution is to precreate 100 rows and instead of INSERT use UPDATE to update the oldest row. Assuming that the table has a datetime field, the query UPDATE … WHERE datetime = (SELECT min(datetime) FROM logtable) can do the job. Edit: display the last 100 entries SELECT * FROM logtable ORDER BY datetime DESC … Read more
This feature has been implemented in Postgres 9.1: CREATE TABLE IF NOT EXISTS myschema.mytable (i integer); For older versions, here is a function to work around it: CREATE OR REPLACE FUNCTION create_mytable() RETURNS void LANGUAGE plpgsql AS $func$ BEGIN IF EXISTS (SELECT FROM pg_catalog.pg_tables WHERE schemaname=”myschema” AND tablename=”mytable”) THEN RAISE NOTICE ‘Table myschema.mytable already exists.’; … Read more
Yes, SELECT COUNT(*) FROM TableName
From http://www.sqlite.org/lang_createtable.html: CREATE TABLE IF NOT EXISTS some_table (id INTEGER PRIMARY KEY AUTOINCREMENT, …);
Go into Tools -> Options -> Designers-> Uncheck “Prevent saving changes that require table re-creation”. Voila. That happens because sometimes it is necessary to drop and recreate a table in order to change something. This can take a while, since all data must be copied to a temp table and then re-inserted in the new … Read more
Column order had a big performance impact on some of the databases I’ve tuned, spanning Sql Server, Oracle, and MySQL. This post has good rules of thumb: Primary key columns first Foreign key columns next. Frequently searched columns next Frequently updated columns later Nullable columns last. Least used nullable columns after more frequently used nullable … Read more
In SQL Server Management Studio, go to Options / SQL Server Object Explorer / Scripting, and enable ‘Generate script for dependent objects’. Then right click the table, script > drop to > new query window and it will generate it for you.
Your understanding is correct. You would do this in many cases. One example is in a relationship like OrderHeader and OrderDetail. The PK in OrderHeader might be OrderNumber. The PK in OrderDetail might be OrderNumber AND LineNumber. If it was either of those two, it would not be unique, but the combination of the two … Read more