I heard somewhere that you should have your int type fields before all other types in your tables. They said the query runs faster or something. Is this true? For example - id int(10) time int(11) user_id int(10) title varchar(128) text text ...instead of: id int(10) title varchar(128) text text time int(11) user_id int(10) ...
How to retrieve the names of all Nonsystem-databases from SQL Server 2000 using a TSQL query? I have anticipated: SELECT * FROM sysdatabases where dbid >4 order by dbid it does not seem to be reliable. Anything else? ...
I have a table with an Identity column which provides my ticketNumber. I want another table to provide a ticketStepNumber. A ticket may have 0 or more steps, so I won't always be creating a ticketStepNumber. The ticketStepNumber value is a combination of the ticketNumber column (int) and the stepNumber column (int). I'd like to def...
With the help of others on SO I've knocked up a couple of Tables and Stored Procedures, this morning, as I'm far from a DB programmer. Would someone mind casting an eye over this and telling me if it's thread-safe? I guess that's probably not the term DBAs/DB developers use but I hope you get the idea: basically, what happens if this s...
the mysql certification guide suggests that views can be used for: creating a summary that may involve calculations selecting a set of rows with a WHERE clause, hide irrelevant information result of a join or union allow for changes made to base table via a view that preserve the schema of original table to accommodate other applicati...
Definitions: In the results, * denotes an empty column The data in the tables is such that every field in the table has the value Fieldname + RowCount (so column 'a' in row 1 contains the value 'a1'). 2 MySQL Tables Table1 Fieldnames: a,b,c,d Table2 Fieldnames: e,f,g,h,i,j Task: I want to get the first 4 rows from each...
Using SQL Server 2000: SELECT PERSONID, CARDEVENTDATE, INTIME, CASE WHEN OUTTIME = INTIME THEN 'No PunchOut' ELSE OUTTIME END AS OUTTIME, CONVERT(char(8), CASE WHEN DateAdd(Day, - DateDiff(Day, 0, OutTime), OutTime) > '18:00:0...
I'm using a Microsoft SQL Server 2005 database with isolation level READ_COMMITTED and READ_COMMITTED_SNAPSHOT=ON. Now I want to use: SELECT * FROM <tablename> FOR UPDATE ...so that other database connections block when trying to access the same row "FOR UPDATE". I tried: SELECT * FROM <tablename> WITH (updlock) WHERE id=1 ...bu...
Inspired by this question where there are differing views on SET NOCOUNT... General accepted best practice (I thought until this question) is to use SET NOCOUNT ON in triggers and stored procedures in SQL Server. We use it everywhere and a quick google shows plenty of SQL Server MVPs agreeing too. MSDN says this can break a .net SQLDat...
I have the following 6 integer db columns: Year Day Month Hour Minute Second I need a query that takes these values for every row, combines them into a DATETIME and puts into another column. ...
Is it possible to make LinqToSql use XmlSerializer to deserialize a typed XML field to an C# object? Currently I have the following code: (note that I had to use XElement.ToString() to use XmlSerializer) protected static T Deserialize<T>(string xmlString) where T : class { var xmlSerializer = new XmlSerializer(typeof(T)...
how can i connect win nt sqlexpress? i am using this code to connect.it works with any version and kind of sql. internal string GetConnectionString() { return "Data Source=" + "MyPC\SQLExpress" + ";Initial Catalog=Master;User ID=" + username + ";Password=" + password; } when connectiong with expr...
Alright SQL Server Gurus, fire up your analyzers. I have a list of titles in application memory (250 or so). I have a database table "books" with greater than a million records, one of the columns of the table is "title" and is of type nvarchar. the "books" table has another column called "ISBN" books.title is not a primary key, is not...
Hi guys, Would any of you know how to get the list of computed columns in a SQL Server database table? I found sys.sp_help tablename does return this information, but only in the secord resultset. I am trying to find out if there is a better way of doing this. Something which only returns a single result set. Any help is very appre...
I have a select statement showing the following results: On_loan barcode Y 12345 Y 12345 N 12345 N 12344 Y 12344 Each barcode for a book can have more than one copy. Users can place a book on hold. E.g user '1' has reserved book 12345 and 12344. The above results show: that the two books with barcode 1234...
How can I write an sql statement that returns a list of how many students had lessons for each of the last 12 months? so I would expect output something like: +--------+----------------+ | Month | No of Students | +--------+----------------+ | Oct 08 | 12 | | ... | ... | | ... | ... | | Sep 09 |...
hey i am having a real headache with this problem. I have a a menu, i am trying to pupulate it from mysql, this is my result from my sql Axel - rubber - brake Axel - rubber - nobrake Axel - paste - brake Wheel - rubber - brake first row is first level of the menu and so on... so right now the menu is full of duplicates, is there an...
I'm implementing a DAL library that is database vendor neutral. Is there an abstraction in ADO.NET (System.Data) for describing pagination? And, do some vendors' ADO.NET provider implementations support such an interface so that I don't have to manually tool up the customized SQL syntax? ...
I am experiencing some strange behaviour in my Java server application whereby database operations that usually take a few milliseconds are sporadically taking much longer (30s - 170s) to complete. This isn't isolated to a specific query as I've seen the delays occurring for both SQL update and select statements. Also, all of my select...
I'm writing a user-defined function in SQL Server 2008. I know that functions cannot raise errors in the usual way - if you try to include the RAISERROR statement SQL returns: Msg 443, Level 16, State 14, Procedure ..., Line ... Invalid use of a side-effecting operator 'RAISERROR' within a function. But the fact is, the function takes...