sql

Should a SQL VIEW always be in 1NF?

A SQL VIEW is a global, logical table that may or may not be persisted. But it's still a table. Therefore, should a VIEW always adhere to first normal form (1NF)? i.e. no duplicate rows, scalar types only, no top-to-bottom or left-to-right ordering, etc. What about the higher normal forms? For me, my applications 'consume' the results o...

Adding an identity to an existing column [SQL Server]

I need to change the primary key of a table to an identity column, and there's already a number of rows in table. I've got a script to clean up the IDs to ensure they're sequential starting at 1, works fine on my test database. What's the SQL command to alter the column to have an identity property? ...

Getting status message of successful executed SQL command?

I wrote a little C++/MFC application that allows me to connect to an ODBC database and execute SQL statements. I'm using SQLGetDiagField / SQLGetDiagRec to display meaningful error messages to the user in case of any errors. Is there a way to receive and disply the database confirmation messages ("table created", "role set", "user dropp...

SQL objects that use an index

Is there a way to see what SQL objects from one data base use a certain index. But I do not want to have to see the execution plan for every object because I have a lot of stored procedures and views PS. It's for SQL 2005 ...

SQL-How to Insert Row Without Auto incrementing a ID Column?

I have a table that has a forced auto increment column and this column is a very valuable ID that is retained through out the entire app. Sorry to say it was poor development on my part to have this be the auto incrementing column. So, here is the problem. I have to insert into this table an ID for the column that has already been cre...

One-way Database Synchronization

There is frequently the need to synchronize data from master tables in one database to clone tables in other databases, often on other servers. For example, consider the case where a backend system manages inventory data and that inventory data ultimately must be pushed to one or more databases that are part of a web site application. T...

Conditionals in transact-sql select column lists

I've got a query that looks a bit like this: select records.id, contacts.name + ' (' + contacts.organization + ')' as contact, from records left join contacts on records.contact = contacts.contactid Problem is - contacts.organization is frequently empty, and I get contacts like "John Smith ()". Is there a way to only concatenate the o...

Create a SQL query to retrieve most recent records

I am creating a status board module for my project team. The status board allows the user to to set their status as in or out and they can also provide a note. I was planning on storing all the information in a single table ... and example of the data follows: Date User Status Notes -----------------------------------...

How do I see what character set a database / table / column is in MySQL?

How do I see what the character set that a MySQL database, table and column are in? Is there something like SHOW CHARACTER SET FOR mydatabase; and SHOW CHARACTER SET FOR mydatabase.mytable; and SHOW CHARACTER SET FOR mydatabase.mytable.mycolumn; ...

Improving scalability of the modified preorder tree traversal algorithm

I've been thinking about the modified preorder tree traversal algorithm for storing trees within a flat table (such as SQL). One property I dislike about the standard approach is that to insert a node you have to touch (on average) N/2 of the nodes (everything with left or right higher than the insert point). The implementations I've...

How can I optimize this query...?

I have two tables, one for routes and one for airports. Routes contains just over 9000 rows and I have indexed every column. Airports only 2000 rows and I have also indexed every column. When I run this query it can take up to 35 seconds to return 300 rows: SELECT routes.* , a1.name as origin_name, a2.name as destination_name FROM ro...

MySQL join based on regexp

I have a table products where one of the columns are relatedproducts. relatedproducts contains strings of concatenated product IDs (column productid) separated by colon, e.g. abc-123:foo-prod:ada69, etc. Due to some bad design, there is the case that a product may be removed from products table and still be referenced in the relatedprodu...

Oracle 11g SQL select max integer value of varchar column

myTable UNID 9 10 UNID is a VARCHAR2 select max(UNID) from myTable returns the UNID with the highest lexicographic value (9). how do i select the UNID with the highest integer value (10)? ...

Add a deployment administrator to CRM 4.0 using SQL

Is there a way to add a deployment administrator to Microsoft CRM 4.0 using a sql query against the CRM database? The person that setup the CRM was the only one who was the Deployment Administrator and he has left and his account was deactivated before another Deployment Administrator could be added using the deployment manager tool. O...

SQL Server 2000: Limit number of rows

Hello sql gurus, Is there a way to limit the number of rows in a SQL Server 2000 database, such that older rows are deleted when new ones come in? I have some data that I'd like to keep around for a about thirty days - after that, I don't care whether the data lays around or is deleted - as long as the table doesn't become huge. Any o...

sql query - true => true, false => true or false

Hi, Simple query, possibly impossible but I know there are some clever people out there :) Given a boolean parameter, I wish to define my where clause to either limit a certain column's output - or do nothing. So, given parameter @bit = 1 this would be the result: where column = 1 given parameter @bit = 0 this would be the result: ...

3 joins and where clause together

I have 3 tables bl_main (bl_id UNIQUE, bl_area) bl_details (bl_id UNIQUE, name) bl_data(bl_id, month, paper_tons, bottles_tons) bl_id is not unique in the last table. There will be multiple rows of same bl_id. I am trying to retrieve data in the following way bl_id | name | bl_area | sum(paper_tons) | sum (bottles_tons) | paper_to...

Converting from globalized date/time format (maybe ISO8601) to SQL Server datetime type

Is there a string format to represent a datetime that SQL will be able to parse and convert into another offset (EST -> UTC for example). I have a string from the user such as: declare @p1 varchar(50); declare @utcDateTime datetime; set @p1 = "2009-06-26 14:30:00.000Z-4:00"; -- could be ISO8601 -- what do I do here to convert @p1? ...

How to export the results of a stored procedure directly to a MS Excel file?

I found a way to extract the contents of a MS Sql table directly (faster) to excel. But I do not know how to do this with a stored procedure that requires parameters. Is it possible to extract, directly, to an Excel File the results of a stored procedure? I know how to do it indirectly (using a data table) but it is too slow. Thank you v...

Column not found when trying a cross database update in mysql

I'm trying to copy the contents of a column in one mysql database to an identical table in another mysql database. I'm using: UPDATE db1.table SET db1.table.name = db2.table.name, db1.table.address = db2.table.address WHERE db1.table.id = db2.table.id; I'm getting the error 1054: Unknown column 'db2.table.id' in 'where cl...