What is the difference between a primary key and a unique constraint?
Someone asked me this question on an interview... ...
sql
(SQL Server 2008) - SQL question regarding CTE and joins :-(
How can I retrieve the following results? user | date | login time | logoff time I wish to print out every day in a given time period with matching login and logoff time which is written is table history. If there's a multiple login for same user and for same day then SQL should select minimal date for login and maximum date for logof...
Should I use Query Hint Fast number_rows / FASTFIRSTROW?
I was reading over the documentation for query hints: http://msdn.microsoft.com/en-us/library/ms181714%28SQL.90%29.aspx And noticed this: FAST number_rows Specifies that the query is optimized for fast retrieval of the first number_rows. This is a nonnegative integer. After the first number_rows are returned, the query continues executi...
IFNULL in NHibernate
Right now I have something like this in NHibernate: Expression.Like(property, value, MatchMode.Anywhere) and that generates SQL like: property LIKE '%value%' which is fine for that case. In another case, I want the SQL: IFNULL(property LIKE '%value%', 0) but I don't see any example in the manual that refers to IFNULL, nor can I...
Order By Sum of Two Fields
Let's say I have a table with karma_up and karma_down. Everytime someone votes up karma_up gets incremented and everytime someone votes down karma_down gets incremented one as well. How can I pull these a selection of rows and have them ordered by the sum of these new values? ORDER BY (karma_up - karma_down) does not seem to work how ...
Seaching for two columns in three-column composite index - Oracle
Hello, I have a composite index on three column in one of my table. It works find if I have the three columns in the where close of my query. When my search query has only two out of three, things don't seem to be as fast any more! Do you know how can go around this? Thanks, Tam P.S. The table APPL_PERF_STATS has composite index on ...
Any tips on planning a large database
I usually fly by the seat of my pants when building my databases. However, my new project is going to need quite a bit of planning. I never went to school for database development so I have no formal training on the planning process. Is there any good software, methodologies for planning these things out? ...
How to ORDER BY a SUM() in MySQL?
I have a table: "ID name c_counts f_counts " and I want to order all the record by sum(c_counts+f_counts) but this doesn't work: SELECT * FROM table ORDER BY sum(c_counts+f_counts) LIMIET 20; ...
sproc execution far slower than SQL ad-hoc query equivalent
I have a sproc that was taking far more time than I expected. I pulled out the SQL and ran it with just DECLARED variables for the parameters. It ran nearly instantaneously (versus a reliable 8 seconds with the sproc). This is the same SQL on same machine, returning the same data. How can I figure out and fix what is causing the spro...
SQL Query: data with comma
Hi All, Im using sql server 2005. i want data with comma. for example ['5000'],['5001'],.. but the last record should not include comma. Pls help me. Query: select '['''+convert(varchar,parcelid)+'''],' from sampletable ...
Will I save any time on a INDEX that SELECTs only once ?
On DBD::SQLite of SQLite3 If I am going to query a SELECT only once. Should I CREATE a INDEX first and then query the SELECT or just query the SELECT without an INDEX, which is faster ? If need to be specified, the col. to be index on is a INTEGER of undef or 1, just these 2 possibilities. ...
How to get all combinations satisfying a condition
Given a table, Table, with columns N1, N2, N3, how can I get all combinations satisfying the condition N1 + N2 + N3 > 10? For example, querying the table: N1 N2 N3 Row1 1 5 4 Row2 4 4 3 Should give the result: N1 N2 N3 Row1 4 5 4 Row2 4 4 4 Row...
SQL query for finding the first, second and third highest numbers
What is an example query to retrieve the first, second and third largest number from a database table using SQL Server? ...
Converting from SQL to HQL fail
Trying to go from here this SQL: SELECT DISTINCT iss.ID FROM Issue AS iss INNER JOIN Message ON Message.IssueID = iss.ID INNER JOIN Clinician ON Clinician.UserID = Message.FromUserID INNER JOIN OrgUnit ON OrgUnit.ID = Clinician.OrgUnitID WHERE OrgUnit.ID = [id-number] To here in HQL: select distinc...
Best way to improve performance (and include somehow failover)
Hi, We have an application running where IIS and SQL are on the same machine. It's a windows2003standard server, with 4gigs of RAM running on a VM. Now the numbers of users are rising constantly. There are some huge statistics also, which can be run by the users but has very much impact of the performance for other users. So we need t...
OleDbConnection.Open() throws a "No error message available" error before even contacting the SQL Server
Hi all Using a connection string of "Provider=SQLOLEDB;Data Source=localhost;User ID=foo;password=bar;Database=CodeLists;Pooling=true;Min Pool Size=20;Max Pool Size=30;" I get the following stack trace System.Data.OleDb.OleDbException: No error message available, result code: -2147024770(0x8007007E). at System.Data.OleDb.O...
ORA-0 [BEA][Oracle JDBC Driver] Unhandled sql type
I have a very simple sql query and when i try to execute, i get following error java.sql.SQLException: [BEA][Oracle JDBCDriver]Unhandled sql type at weblogic.jdbc.base.BaseExceptions.createException(Unknown Source) at weblogic.jdbc.base.BaseExceptions.getException(Unknown Source) at ... I have configured weblogic datats...
some good tutorials about SQL Server deadlock?
Hello everyone, I am learning SQL Server deadlock, about why there is deadlock, how to solve dead lock issue, best practices, how to analyze why deadlock happens. I am working on SQL Server 2008 Enterprise. Any recommended readings for me? thank in advance, George ...
SP return null as ZERO ?
Using a stored procedure i have a fairly complex SQL statement which returns a COUNT value as a pseudo column. In many cases the result will be 'null'. This causes problems in my application, so i am wondering if it is possible to return a 'null' as '0' by default from the stored procedure? Thanks. UPDATE I need to apply the ISNULL to...
Getting field names and types in CLSQL from a result set
So I'm looking to modify the CLSQL abstractions to suit my own needs. I've been using the clsql-sys package and that's suited most of my needs. However, I can't seem to find how to get a list of field names and field types from the result set. In fact, I just seem can't to find anything ANYWHERE to get types (names I can just hack into t...