When designing tables, I've developed a habit of having one column that is unique and that I make the primary key. This is achieved in four ways depending on requirements: Identity integer column that auto increments. Unique identifier (GUID) A short character(x) or integer (or other relatively small numeric type) column that can serv...
In the case when I want to check, if a certain entry in the database exists I have two options. I can create an sql query using COUNT() and then check, if the result is >0... ...or I can just retrieve the record(s) and then count the number of rows in the returned rowset. For example with $result->num_rows; What's better/faster? in my...
Is there a simple process in SQL 2005 for spitting all of my stored procedures out to individual .sql files. I'd like to move them into VSS, but am not too excited by the prospect of clicking on each one to get the source, dumping it into a text file and so on.. ...
How do I parameterize a query containing an IN clause with a variable number of arguments, like this one? select * from Tags where Name in ('ruby','rails','scruffy','rubyonrails') order by Count desc In this query, the number of arguments could be anywhere from 1 to 5. I would prefer not to use a dedicated stored procedure for this ...
Convention has it that table names should be the singular of the entity that they store attributes of. I dislike any T-SQL that requires square brackets around names, but I have renamed a Users table to the singular, forever sentencing those using the table to sometimes have to use brackets. My gut feel is that it is more correct t...
I have a MySQL table containing domain names: +----+---------------+ | id | domain | +----+---------------+ | 1 | amazon.com | | 2 | google.com | | 3 | microsoft.com | | | ... | +----+---------------+ I'd like to be able to search through this table for a full hostname (i.e. 'www.google.com'). If it were t...
I'm trying to convert an Excel document into a table in SQL 2005. I found the link below and am wondering if it looks like a solution. If so, what would the @excel_full_file_name syntax would be and where would the path be relative to.. http://www.siccolo.com/Articles/SQLScripts/how-to-create-sql-to-convert-Excel_to_table.html ...
Hello everybody! Tip me plase, how to insert a null value into table using Trolltech Qt 4.x SQL classes? QSqlQuery, I guess, or something else from QtNetwork. As analog of it, in .NET there is the System.DbNull class, which represents sql NULL. And what type should I use for some object's property, that can hold both null-value and QSt...
Why does nvarchar(256) seem to the be the standard for user names in SQL Server? Any system functions that return a user name return nvarchar(256), the ASP Membership provider uses nvarchar(256) 256 seems like an odd number (yes, I know its even...) - 255 I could understand (1 byte address) but 256 doesn't make sense to me. Can anyo...
I've been working with LINQ To SQL for a little while now and when it comes to removing an entity from the DB, I've always called the table's .DeleteOnSubmit and passed in the entity. Sometimes I've found myself writing something like: db.Users.DeleteOnSubmit(db.Users.Where(c => c.ID == xyz).Select(c => c).Single()); This of course ca...
A puzzler from a coworker that I cannot figure out... update btd.dbo.tblpayroll set empname = ( select b.Legal_Name from ( SELECT Legal_Name, Employee_ID FROM Com.dbo.Workers WHERE isnumeric(Employ...
I have a problem where I want to read an object from the database using Hibernate, change a value, and save the object. If changing the value takes some time, what's the best way to ensure the underlying object in the database has not changed? I am doing this in one transaction (and one session). The code looks something like: // Load...
I'm working on some upgrades to an internal web analytics system we provide for our clients (in the absence of a preferred vendor or Google Analytics), and I'm working on the following query: select path as EntryPage, count(Path) as [Count] from ( /* Sub-query 1 */ select pv2.path from pagevi...
How do you organize your stored procedures so you can easily find them and keep track of their dependencies? ...
Can we write an regular expression such that it splits the stored procedure in multiple SQL statements. It should split update, delete select etc statements. ...
I have a set of conditions in my where clause like WHERE d.attribute3 = 'abcd*' AND x.STATUS != 'P' AND x.STATUS != 'J' AND x.STATUS != 'X' AND x.STATUS != 'S' AND x.STATUS != 'D' AND CURRENT_TIMESTAMP - 1 < x.CREATION_TIMESTAMP Which of these conditions will be executed first? I am using oracle. Will I get these details in m...
I need to update a record in a database with the following fields [ID] int (AutoIncr. PK) [ScorerID] int [Score] int [DateCreated] smalldatetime If a record exists for todays date (only the date portion should be checked, not the time) and a given scorer, I'd like to update the score value for this guy and this day. If the scorer do...
So I was thinking about creating a dynamic sql question, meaning that i want the amount of parameters to be dynamic. Having looked at this: http://stackoverflow.com/questions/337704/parameterizing-a-sql-in-clause#337725 i was thinking that using like '%x%' is SLOW and not good. What i actually would like to do is using the IN keyword a...
I need to update a group of cells by inserting the same two characters into all of them, but I'm just drawing a blank on how to do this. Could someone point me in the right direction? Old Cells HI.1 HI.2 HII.1 New Cells H08I.1 H08I.2 H08II.1 ...
Without using plpgsql, I'm trying to urlencode a given text within a pgsql SELECT statement. The problem with this approach: select regexp_replace('héllo there','([^A-Za-z0-9])','%' || encode(E'\\1','hex'),'g') ...is that the encode function is not passed the regexp parameter, unless there's another way to call functions from within ...