For SQL Server, from http://msdn.microsoft.com/en-us/library/ms179859.aspx : % Any string of zero or more characters. WHERE title LIKE ‘%computer%’ finds all book titles with the word ‘computer’ anywhere in the book title. _ Any single character. WHERE au_fname LIKE ‘_ean’ finds all four-letter first names that end with ean (Dean, Sean, and so on). [ ] … Read more
ALTER TABLE document MODIFY COLUMN document_id INT auto_increment
You can query the USER_TAB_COLUMNS table for table column metadata. SELECT table_name, column_name, data_type, data_length FROM USER_TAB_COLUMNS WHERE table_name=”MYTABLE”
In the sub-query vs simple (non-recursive) CTE versions, they are probably very similar. You would have to use the profiler and actual execution plan to spot any differences, and that would be specific to your setup (so we can’t tell you the answer in full). In general; A CTE can be used recursively; a sub-query … Read more
— Disable the constraints on a table called tableName: ALTER TABLE tableName NOCHECK CONSTRAINT ALL — Re-enable the constraints on a table called tableName: ALTER TABLE tableName WITH CHECK CHECK CONSTRAINT ALL ——————————————————— — Disable constraints for all tables in the database: EXEC sp_msforeachtable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’ — Re-enable constraints for all … Read more
Probably due to the way different sql dbms deal with schemas. Try the following For SQL Server: SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ‘BASE TABLE’ AND TABLE_CATALOG=’dbName’ For MySQL: SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ‘BASE TABLE’ AND TABLE_SCHEMA=’dbName’ For Oracle I think the equivalent would be to use DBA_TABLES.
VARCHAR is variable-length. CHAR is fixed length. If your content is a fixed size, you’ll get better performance with CHAR. See the MySQL page on CHAR and VARCHAR Types for a detailed explanation (be sure to also read the comments).
If you can’t just limit the query itself with a where clause, you can use the fact that the count aggregate only counts the non-null values: select count(case Position when ‘Manager’ then 1 else null end) from … You can also use the sum aggregate in a similar way: select sum(case Position when ‘Manager’ then … Read more
Here’s a good article on tagging Database schemas: http://howto.philippkeller.com/2005/04/24/Tags-Database-schemas/ along with performance tests: http://howto.philippkeller.com/2005/06/19/Tagsystems-performance-tests/ Note that the conclusions there are very specific to MySQL, which (at least in 2005 at the time that was written) had very poor full text indexing characteristics.
You need a function to do the concatenation. Microsoft Access condense multiple lines in a table Example using your data: Select T.ColumnA , GetList(“Select ColumnB From Table1 As T1 Where T1.ColumnA = ” & [T].[ColumnA],””,”, “) AS ColumnBItems From Table1 AS T Group By T.ColumnA;