At every company I have worked at, I have found that people are still writing their SQL queries in the ANSI-89 standard: select a.id, b.id, b.address_1 from person a, address b where a.id = b.id rather than the ANSI-92 standard: select a.id, b.id, b.address_1 from person a inner join address b on a.id = b.id For an extremely simple...
I have a table with a view_name field (varchar(256)) and I would like to use that field in an sql query. Example : TABLE university_members id | type | view_name | count 1 | professors | view_professors | 0 2 | students | view_students2 | 0 3 | staff | view_staff4 | 0 And I would like to update all rows with some aggregate calcula...
I know it's probably not the right way to structure a database but does the database perform faster if the data is put in one huge table instead of breaking it up logically in other tables? I want to design and create the database properly using keys to create relational integrity across tables but when quering, is JOIN'ing slower than ...
I have a legacy MS Access 2007 table that contains 52 fields (1 field for each week of the year) representing historical sales data (plus one field for the year actually). I would like to convert this database into a more conventional Time/Value listing. Does anyone knows how to do that without writing queries with 52+ explicit paramete...
I need to create a trigger in every database on my sql 2005 instance. I'm setting up some auditing ddl triggers. I create a cursor with all database names and try to execute a USE statement. This doesn't seem to change the database - the CREATE TRIGGER statement just fires in adventureworks repeatedly. The other option would be to pr...
Is it possible to create a MySQL select statement that uses an expression as x then checks if the value for x is under a certain amount? SELECT (mytable.field1 + 10) AS x FROM `mytable` WHERE x < 50; ...
Hi, I'm trying to write a simple query involving two tables. The "person" table has a unique person_id and a name, and the "friends" table has a person_id and a friend_id which is a FK to a person_id in the person table. person: int person_id varchar[45] name friends: int person_id int friend_id I want to select the name of all...
At my current job, we're looking to implement our own odbc driver to allow many different applications to be able to connect to our own app as a datasource. Right now we are trying to weigh the options of developing our own driver to the implementation spec, which is massive, or using an SDK that allows for programmers to 'fill in' the d...
I need a way to determine from Wise Install Script if SQL Server Management Studio Express 2005 is installed on computer. Does someone know a Registry entry or something that will be present when SSMSE is installed? ...
In visual studio 2008, when I drag a database table into my dbml screen, any tables that end with the letter s automatcially get the s removed from the dbml object. Is there any way to disable this? Also, the collection of rows also gets an s appended to the collection property name. Is there a way to change that as well? Thanks ...
I have a 'reference' SQL Server 2005 database that is used as our global standard. We're all set up for keeping general table schema and data properly synchronized, but don't yet have a good solution for other objects like views, stored procedures, and user-defined functions. I'm aware of products like Redgate's SQL Compare, but we don'...
I thought these were synonomous, but I wrote the following in Microsoft SQL: Select Unique col from (select col from table1 union select col from table2) alias And it failed. Changing it to Select Distinct col from (select col from table1 union select col from table2) alias fixed it. Can someone explain? ...
Did the recent purchase of MySQL by Sun and the subsequent buggy releases kill the MySQL brand? I whole heartedly embraced MySQL when it first came out as I used to be a poor developer and all the RDBMs were too expensive. I have fond feelings for MySQL and their being able to compete with Oracle and SQL Server. I credit the original ...
In the setup method to a JUnit test case I'm working on I need it to run a sql script on my database before each test case, and then a rollback afterwards. I have tried using a tokenizer, which added each SQL command to a batch and then executing them. But I can't get working. So my question is if there is some standard method in JUnit ...
How do I create a view dynamically in SQL Server using C#? ...
I am working with an order system that has two tables Order and OrderLine pretty standard stuff. I want to work out an order line number for the order lines with respect to the order e.g. Orderid Orderlineid linenumber 1 1 1 2 2 1 2 3 2 3 4 1 4 ...
I have researched and haven't found a way to run INTERSECT and MINUS operations in MS Access. Does any way exist ...
I was once given this task to do in an RDBMS: Given tables customer, order, orderlines and product. Everything done with the usual fields and relationships, with a comment memo field on the orderline table. For one customer retrieve a list of all products that customer has ever ordered with product name, year of first purchase, dates o...
I'm using a table adapter in Visual Studio to make a query to a stored procedure in my SQL Server 2005 database. When I make the call via my website application it returns nothing. When I make the same call via SQL Server Manager it returns the expected data. I put a breakpoint on the call to the adapter's getData method and looked at...
If I have an mssql varchar[1024] that is always empty in a table, how much actual bytes will be wasted in the db file on disk per row? Please let me know the size for both: NULLs allowed storing '' NULLs not allowed storing '' NULLs allowed storing NULL Since the max varchar size is > 2^1 and < 2^3 I would assume 2 bytes for the len...