sql

Any good SQL Anywhere database schema comparison tools?

Are there any good database schema comparison tools out there that support Sybase SQL Anywhere version 10? I've seen a litany of them for SQL Server, a few for MySQL and Oracle, but nothing that supports SQL Anywhere correctly. I tried using DB Solo, but it turned all my non-unique indexes into unique ones, and I didn't see any optio...

SQL Query Help: Selecting Rows That Appear A Certain Number Of Times

I have a table with a "Date" column. Each Date may appear multiple times. How do I select only the dates that appear < k number of times? ...

How do you deal with configuration management of Database Tables?

How do you deal with source control management and automated deployment (configuration management) of database tables. I work in a SQL Server environment and it's pretty easy to script out drop and create files for stored procedures/triggers/functions even jobs. It's also easy to handle scripting out the creation of a new db table. Ho...

What are indexes and how can I use them to optimize queries in my database?

I am maintaining a pretty sizable application and database and am noticing some poor database performance in a few of our stored procedures. I always hear that "adding an index" can be done to help performance. I am certainly no DBA, and I do not understand what indexes are, why the help, and how to create them. I basically need an ind...

Automatically Generating SQL Schema from XML

We are attempting to use a SQL Server 2003 database for our test records and want a quick way to take NUnit and NAnt output and produce SQL schema and data. Is there a simple way to generate SQL Schema using the XSD file describing these XML documents? ...

Is it possible to build following SQL query

The original query looks like this (MySQL): select * from books where title like "%text%" or description like "%text%" order by date Would it be possible to rewrite it (without unions or procedures), so that result will look like this: - list of books where title matches query ordered by date, followed by: - list of books where descript...

A script to change all tables and fields to the utf-8-bin collation in MYSQL

Is there a SQL or PHP script that I can run that will change the default collation in all tables and fields in a database? I can write one myself, but I think that this should be something that readily available at a site like this. If I can come up with one myself before somebody posts one, I will post it myself. ...

Fastest way to remove non-numeric characters from a VARCHAR in SQL Server

I'm writing an import utility that is using phone numbers as a unique key within the import. I need to check that the phone number does not already exist in my DB. The problem is that phone numbers in the DB could have things like dashes and parenthesis and possibly other things. I wrote a function to remove these things, the problem ...

How to get a distinct list of words used in all Field Records using MS SQL?

If I have a table field named 'description', what would be the SQL (using MS SQL) to get a list of records of all distinct words used in this field. For example: If the table contains the following for the 'description' field: Record1 "The dog jumped over the fence." Record2 "The giant tripped on the fence." ... The SQL record outpu...

Fetch top X users, plus a specific user (if they're not in the top X)

I have a list of ranked users, and would like to select the top 50. I also want to make sure one particular user is in this result set, even if they aren't in the top 50. Is there a sensible way to do this in a single mysql query? Or should I just check the results for the particular user and fetch him separately, if necessary? Thanks! ...

What is the best way to debug stored procedures (and write sprocs that are easier to debug)?

What are good methodologies for creating sprocs that reduce the pain of debugging? And what tools are out there for debugging stored procedures? Perhaps most importantly, what are indications to look out for that errors are happening in a sproc and not in the code? I hope I'm not all over the board too terribly bad here. Votes for answe...

Whats a good value for 'identity increment' for an 'Orders' table

Whats a good value for an identity increment for an 'Orders' table? (orders as in shopping cart orders) I want the order numbers to appear so that we have more orders than we really do, plus make it harder for users to guess order numbers of other users in cases where that might be a problem. I dont want too big a value such that I mig...

What are the down sides of using a composite/compound primary key?

What are the down sides of using a composite/compound primary key? ...

Sql: add a column to existing table and uniquely number them

I want to add a column to an existing legacy database and write a procedure by which I can assign each record a different value. Something like add a column and autogenerate the data for it. Like, if I add a new column called "ID" (number) I want to then initialize a unique value to each of the records. So, my ID column will have record...

Can I refactor my MySql queries into one query based on number of results?

I have a table y Which has two columns a and b Entries are a b 1 2 1 3 1 4 0 5 0 2 0 4 I want to get 2,3,4 if I search column a for 1, and 5,2,4 if I search column a. So, if I search A for something that is in A, (1) I get those rows, and if there are no entries A for given value, give me the 'Defaults' (a = '0') ...

Solutions for INSERT OR UPDATE on SQL Server

Assume a table structure of MyTable(KEY, datafield1, datafield2...) Often I want to either update an existing record, or insert a new record if it doesn't exist. essentially if (key exists) Run Update command ELSE run insert command What's the best performing way to write this? ...

Is there set division in SQL?

I'm fully aware that set division can be accomplished through a series of other operations, so my question is: Is there a command for set division in SQL? ...

Tips for getting started with SQL?

I've never had much need for programming with databases. Since their use is so widespread it seems like a good thing for me to learn. SQL seems like the place to start, possibly SQLite and maybe the Python bindings. What would you recommend for someone new to this? Libraries, tools and project ideas are all welcome. ...

SQL - How do you compare a CLOB

in a DB2 trigger, I need to compare the value of a CLOB field. Something like: IF OLD_ROW.CLOB_FIELD != UPDATED_ROW.CLOB_FIELD but "!=" does not work for comparing CLOBs. What is the way to compare it? Edited to add: My trigger needs to do some action if the Clob field was changed during an update. This is the reason I need to comp...

MySQL transaction with thousands of Inserts - how many "round trips" does it take?

I've got C# code that accesses MySQL through ODBC. It creates a transaction, does a few thousand insert commands, and then commits. Now my question is how many "round trips", so to speak, happen against the DB server? I mean, does it simply transmit every insert command to the DB server, or does it cache/buffer them and send them in bat...