Given the constraint of only using T-Sql in Sql Server 2005, is there a better way to remove the decimal point from a money datatype than a conversion to a varchar (here implicitly) and then a replace of the decimal point? Here is what I have currently. SELECT REPLACE(1.23, '.', ''), REPLACE(19.99, '.', '') Which returns the desired 1...
I've just come across this in a WHERE clause: AND NOT (t.id = @id) How does this compare with: AND t.id != @id Or with: AND t.id <> @id I'd always write the latter myself, but clearly someone else thinks differently. Is one going to perform any better than the other? I know that using <> or != is going to bust any hopes for usin...
Is there some means of querying the system tables to establish which tables are using what locking schemes? I took a look at the columns in sysobjects but nothing jumped out. ...
I have a stored procedure which takes as its parameter a varchar which needs to be cast as a datetime for later use: SET @the_date = CAST(@date_string AS DATETIME) I'm expecting the date string to be supplied in the format "DD-MON-YYYY", but in an effort to code defensively, if for some reason it can't be cast successfully, I want to ...
I'm an ASP.NET developer who has used Microsoft SQL Server for all my database needs (both at work and for personal projects). I considering trying out the LAMP stack for some of my personal projects. What are some of the main differences between MySQL and MS SQL? Are using Stored Procedures a common practice in MySQL? Any advice or reso...
In SQL Server I have a DATETIME column which includes a time element. Example: '14 AUG 2008 14:23:019' What is the best method to only select the records for a particular day, ignoring the time part? Example: (Not safe, as it does not match the time part and returns no rows) DECLARE @p_date DATETIME SET @p_date = CONVERT(...
I'm guessing it needs to be something like: CONVERT(CHAR(24), lastModified, 101) However I'm not sure of the right value for the third parameter. Thanks! ...
I am currently architecting a small CRUD applicaton. Their database is a huge mess and will be changing frequently over the course of the next 6 months to a year. What would you recommend for my data layer: 1) ORM (if so, which one?) 2) Linq2Sql 3) Stored Procedures 4) Parametrized Queries I really need a solution that will be dynam...
Imagine the scene, you're updating some legacy Sybase code and come across a cursor. The stored procedure builds up a result set in a #temporary table which is all ready to be returned except that one of columns isn't terribly human readable, it's an alphanumeric code. What we need to do, is figure out the possible distinct values of th...
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...
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. ...
Here is the issue I am having: I have a large query that needs to compare datetimes in the where clause to see if two dates are on the same day. My current solution, which sucks, is to send the datetimes into a UDF to convert them to midnight of the same day, and then check those dates for equality. When it comes to the query plan, thi...
I know this falls into the black arts area of programming, but this is suddenly an area that I need to strengthen in my professional life. There are a couple of topics were this subject has been touched on but I'm not sure the item has been really addressed about how to become one with the system. For example: I became a phenomenally b...
Trying to find some simple SQL Server PIVOT examples. Most of the examples that I have found involve counting or summing up numbers. I just want to pivot some string data. For example, I have a query returning the following. Action1 VIEW Action1 EDIT Action2 VIEW Action3 VIEW Action3 EDIT I would like to use PIVOT (if even possibl...
Is there an automatic way in SQL Server 2005 to create a database from several tables in another database? I need to work on a project and I only need a few tables to run it locally, and I don't want to make a backup of a 50 gig DB. UPDATE I tried the Tasks -> Export Data in Management studio, and while it created a new sub database wi...
Is there a way to make a TSQL variable constant? ...
I have a History Table in SQL Server that basically tracks an item through a process. The item has some fixed fields that don't change throughout the process, but has a few other fields including status and Id which increment as the steps of the process increase. Basically I want to retrieve the last step for each item given a Batch Ref...
I am writing a query in which I have to get the data for only the last year What is the best way to do this SELECT ... From ... WHERE date > '8/27/2007 12:00:00 AM' ????? ...
Before someone gets in a tizzy because this was answered elsewhere (not sure if it was,but...), the 5 "related questions" shown right now do not match what I'm looking for, and I searched the string "sql insert" and did not find quite what I was looking for. So I'm basically trying to figure out the simplest way to perform your basic in...