I know that primary keys based on Guids do not have the best performance (due to the fragmentation), but they are globally unique and allow replication scenarios. Integral identifiers, on the other side, have greater performance at the cost of scalability. But in what scenarios would someone want to use sequential uniqueidentifier as t...
Converting a couple stored procedures from MySQL to Microsoft SQL server. Everything is going well, except one procedure used the MySQL SHA1() function. I cannot seem to find an equivalent to this in MS-SQL. Does anyone know a valid equivalent for SHA1() on MS-SQL? ...
I have topics(id*) and tags(id*,name) and a linking table topic_tags(topicFk*,tagFk*). Now I want to select every single topic, that has all of the good tags (a,b,c) but none of the bad tags (d,e,f). How do I do that? ...
Can you please let me know the SQL to split date ranges when they overlap? Data (sample data with a date range and possibly other columns): Col1 FromDate ToDate 1. 1 1/1/2008 31/12/2010 2. 1 1/1/2009 31/12/2012 3. 1 1/1/2009 31/12/2014 Output: Col1 From Date ToDate 1. 1 1/1/2008 31/12/2008 (from row 1 above) ...
I knew stackoverflow would help me for other than know what is the "favorite programming cartoon" :P This was the accepted answer by: Bill Karwin Thanks to all for the help ( I would like to double vote you all ) My query ended up like this ( this is the real one ) SELECT accepted.folio, COALESCE( inprog.activityin, accep...
Assuming I have three tables : TableA (key, value) TableB (key, value) TableC (key, value) and I want to return a value for all keys. If the key exists in TableC return that value else if the key exists in B return that value else return the value from table A The best I have come up with so far is SELECT key,Value FROM TableA WHERE k...
Hi, I have months stored in SQL Server as 1,2,3,4,...12. I would like to display them as January,February etc. Is there a function in SQL Server like MonthName(1) = January? I am trying to avoid a CASE statement, if possible. Thanks Edit ~ I am on SQL Server 2005, if this makes a difference. ...
I am writing a search application specifically for music playlists. The genre and file format differs from playlist to playlist, and sometimes within the playlist there are differences too. There is also a concept of "synonymous" tags (e.g. urban would cover both hiphop and r&b, but not the other way around). Below is a list of search...
I've been looking for quite some time for an application that fills the same role as Logparser, an awesome piece of technology, but for Unix. Does anyone know of something this? (I've looked at Splunk but its an overkill, a simple command line is all I really need) Note: Being able to make SQL queries on random logs, is great and much m...
I have a table in the database that I'm retrieving using LINQ to SQL, and as a part of my processing I want to add to this list, then update the database with the new items + any changes I've made. What I thought I could do was this: var list = (from item in db.Table select item).ToList(); [do processing where I modify ite...
Many database systems don't allow comments or descriptions of tables and fields, so how do you go about documenting the purpose of a table/field apart from the obvious of having good naming conventions? (Let's assume for now that "excellent" table and field names are not enough to document the full meaning of every table, field and rela...
I need a function which executes an INSERT statement on a database and returns the Auto_Increment primary key. I have the following C# code but, while the INSERT statement works fine (I can see the record in the database, the PK is generated correctly and rows == 1), the id value is always 0. Any ideas on what might be going wrong? ...
Hi, I have two queries saved on my SQL Server 2005 and I would like to have them running at certain intervals, e.g. every 24 hours. Is there a way to automate these queries and schedule them via SQL Server Management Studio? Thanks for all the help in advance. Ym PS: New here and have intermediate level knowledge of SQL server ...
How do I generate a range of consecutive numbers (one per line) from a mysql query so that I can insert them into a table? for example : nr 1 2 3 4 5 I would like to use only mysql for this (not php or other languages) ...
I have this script: select name,create_date,modify_date from sys.procedures order by modify_date desc I can see what procedures were modified lately. I will add a "where modify_date >= " And I'd like to use some system stored procedure, that will generate me : drop + create scripts for the (let's say 5 matching) stored procedures Ca...
I have an UPDATE sql command that modifies a Date/Time field in a particular table. This table has two keys, ProductionCode and TestTime, which is what is being modified. The Production Code never changes for a particular record, but often a user will find that they entered the wrong test time and need to change it. I have the updated t...
This is my query: $query = $this->db->query(' SELECT archives.id, archives.signature, type_of_source.description, media_type.description, origin.description FROM archives, type_of_source, media_type, origin WHERE archives.type_of_source_id = type_of_source.id ...
I have a problem with an SQL query on Postgresql. This select clause is an example from a lecture on databases: 1 select t.CourseNr, t.StudentsPerCourse, g.StudentCount, 2 t.StudentsPerCourse/g.StudentCount as Marketshare 3 from (select CourseNr, count(*) as StudentsPerCourse 4 from taking 5 group by CourseNr) t, 6 ...
I have a database called foo and a database called bar. I have a table in foo called tblFoobar that I want to move (data and all) to database bar from database foo. What is the SQL statement to do this? ...
I am sending data across a link with very little bandwidth and I will probably be sending large data files. I have Merge Replication and Snapshot replication configured at present. Is it possible to enable compression in SQL Server 2005 replication and if so, how? Thanks. ...