I have a Monthly Status database view I need to build a report based on. The data in the view looks something like this: Category | Revenue | Yearh | Month Bikes 10 000 2008 1 Bikes 12 000 2008 2 Bikes 12 000 2008 3 Bikes 15 000 2008 1 Bikes 11 000 2007 ...
I have a table with a 'filename' column. I recently performed an insert into this column but in my haste forgot to append the file extension to all the filenames entered. Fortunately they are all '.jpg' images. How can I easily update the 'filename' column of these inserted fields (assuming I can select the recent rows based on known i...
We're storing some Guids in a MS SQL database. There's some legacy code that does Guid.ToString() and then passes them in to a varchar(64) and there's some newer code that passes them in using a uniqueidentifier parameter. When you look at the results using MS SQL Management studio they look different. The byte order of the first three b...
I haven't really done a ton of database work in a few years and have become unaccustomed with the tools available. Back a few years ago I was working with oracle databases and was using primarily TOAD with a little bit of MS Access as my tools of choice to prototype the queries I was using in my applications. I really loved TOAD in tha...
I am about to migrate a project from SQL Server to MySQL. I am looking for a good book that will quickly get me up to speed with MySQL (that assumes I already have a good knowledge of relational databases). Any recommendations? ...
I've got a database server that I am unable to connect to using the credentials I've been provided. However, on the staging version of the same server, there's a linked server that points to the production database. Both the staging server and the linked server have the same schema. I've been reassured that I should expect to be able to...
What are some good references for SQL Server 2005/2008? As someone with background working in PHP and interacting with MySQL/PostGreSQL databases, is there anything specific that I should be on the look out for? ...
So I'm getting a new job working with databases (Microsoft SQL Server to be precise). I know nothing about SQL much less SQL Server. They said they'd train me, but I want to take some initiative to learn about it on my own to be ahead. Where's the best place to start (tutorials, books, etc)? I want to learn more about the SQL languag...
I have a SQL Server 2005 database that I would like to be able to recreate at a moment's notice. I want to be able to point to my database and generate a complete set of scripts that will not only create all the tables/views/sprocs/functions that are in the database, but will also populate all the tables with data. Are there any tools t...
I have a table in a SQL Server 2005 database with a trigger that is supposed to add a record to a different table whenever a new record is inserted. It seems to work fine, but if I execute an Insert Into on the master table that uses a subquery as the source of the values, the trigger only inserts one record in the other table, even thou...
Server Management Studio tends to be a bit un-intuitive when it comes to managing Extended Properties, so can anyone recommend a decent tool that improves the situation. One thing I would like to do is to have templates that I can apply objects, thus standardising the nomenclature and content of the properties applied to objects. Many ...
I have a column of data that contains a percentage range as a string that I'd like to convert to a number so I can do easy comparisons. Possible values in the string: '<5%' '5-10%' '10-15%' ... '95-100%' I'd like to convert this in my select where clause to just the first number, 5, 10, 15, etc. so that I can compare that value to a pa...
Since both a Table Scan and a Clustered Index Scan essentially scan all records in the table, why is a Clustered Index Scan supposedly better? As an example - what's the performance difference between the following when there are many records?: declare @temp table( SomeColumn varchar(50) ) insert into @temp select 'SomeVal' selec...
I forgot my password for Sql Server 2005. Windows Authentication is not enabled so I cannot login. How can I remove the current instance and create a new db instance? Or is there a better solution exists? ...
When you have a query or stored procedure that needs performance tuning, what are some of the first things you try? ...
What is the best way to remove duplicate rows from a fairly large table (i.e. 300,000+ rows)? The rows of course will not be perfect duplicates because of the existence of the RowID identity field. MyTable ----------- RowID int not null identity(1,1) primary key, Col1 varchar(20) not null, Col2 varchar(2048) not null, Col3 tinyint not ...
I have a view that has a list of jobs in it, with data like who they're assigned to and the stage they are in. I need to write a stored procedure that returns how many jobs each person has at each stage. So far I have this (simplified): DECLARE @ResultTable table ( StaffName nvarchar(100), Stage1Count int, Stage2Count int ) INS...
Is it possible to create a Trigger that will not be in a transaction? I want to update data on a linked server with a trigger but due to firewall issues we can't create a distributed transaction between the two servers. ...
Currently we have a hybrid ASP/PHP setup connecting to a SQL Server 2005 database. But all the query work is done on the client side, I'd like to move some of this to PHP. What driver and/or connection string is needed to connect to Sql Svr and what is the syntax to use in PHP? Update: OK so I was definitely trying to avoid using an...
We're seeing some pernicious, but rare, deadlock conditions in the Stack Overflow SQL Server 2005 database. I attached the profiler, set up a trace profile using this excellent article on troubleshooting deadlocks, and captured a bunch of examples. The weird thing is that the deadlocking write is always the same: UPDATE [dbo].[Posts] S...