What's better to use when comparing columns that could contains values with different cases? Is Collate faster? Just trying to figure this out because in our project with tons of data comparisons. we use: select * from table t1, table t2 where t1.col1 collate SQL_Latin1_General_CP1_CI_AS = t2.colb collate SQL_Latin1_General_CP1_CI_AS ...
Hi I've got a problem I've not come across before. I've got an Excel datasource I've loaded into SQL table and I am trying to transform into something a bit more sensible. What I've got is column A in the SQL table from Excel that contains all 3 fields of data but I need to add columns to replace the groups of Owner and Stage Note a u...
Hello everyone, I am using SQL Server 2008. One int column I used as primary key but not identity column (whose value will be increased by 1 automatically). I want to convert this column to identity column. Any solutions? thanks in advance, George ...
Hi, I have a table and I need retrieve the ID of the First and Second row. How to achieve that ? By Top 2 I select two first rows, but I need only second row ...
How can I read an accounts emails subject and content, and store these in my SQL Server database? ...
This is the case. There are 3 tables - state, region and city. If I delete a state with the name "France", I want that all regions and cities belonging to this state be deleted as well. How to do this in T-SQL? ...
I have the following string in database: SET @Value = "[4888378977CA4A5] Test String" Also, the @Value may also have 'RE: ' at the start of the string such as: SET @Value = "RE: [4888378977CA4A5] Test String" How can I access the 4888378977CA4A5 part of the string using regular expressions? Thanks! ...
I'm creating a robust date table want to know the best way to link to it. The Primary Key Clustered Index will be on the smart date integer key (per Kimball spec) with a name of DateID. Until now I have been running queries against it like so: select Foo.orderdate -- a bunch of fields from Foo ,DTE.FiscalYearName ,DTE.Fiscal...
Hi All, I am building and executing some dynamic sql and wanted to use SQL Server profiler to view that execution statement. Do you know what filters /settings I have to use in order to see that? I have looked through them and could be just looking right over the obvious setting. EDIT: The dynamic sql is called within a procedure. T...
Hi All Bit rusty in sql I have a situation where I need to insert a field "@Amount" into a temp table.If @amount from tableA is null or 0 get it from tableB This is a simplified example of what I am doing.This must be done within a select statement when inserting into #CustomerTable Do I need a case when statement? DECLARE @Amount DE...
I have SQL Server 2005 Standard Service Pack 2 9.00.4053.00 (Intel X86) Table has close to 30 million rows.. If I do SELECT GETDATE(), * FROM <table> Identical Date and time value is returned including milliseconds part.. though query took more then 3 minutes to complete... I have already read http://sqlblog.com/blogs/andrew_k...
I need to convert Datetime fields to a specifically formatted INT type. For example, I want 2000-01-01 00:00:00.000 to convert to 20010101. What is the most performant way to make that conversion for comparison in a query? Something like: DATEPART(year, orderdate) * 10000 + DATEPART(month, orderdate) * 100 + DATEPART(day, ord...
Hi All, I have Created a Procedure for one of our .Net Devs where they can pass in values that will then build and output a result set. Part of the params they pass in will determine which table gets called. My question is how can I ensure that the dynamic sql statement I am building will get cached for faster execution? None of the c...
Given a the following table: Index | Element --------------- 1 | A 2 | B 3 | C 4 | D We want to generate all the possible permutations (without repetitions) using the elements. the final result (skipping some rows) will look like this: Results ---------- ABCD ABDC ACBD ACDB ADAC ADCA ...
Hi, I want to get the entire row of a table in SQL Server, but only the last inserted row (which is the row with the highest "ID"). I tried top, max etc, but can't seem to get this to work. So if the highest ID is 15, I want to get all the fields of that row (15). Any ideas? Thanks ...
Is this possible? I would like to have a limit for a user, lets say 5 databases. So when he tries to issue a CREATE query to create a 6th an exception is thrown. ...
Hello, I'm trying to transfer an entire column's worth of data from the backup database to the current production database (earlier in the day I had ruined this column in production with a bad update). I am using MS SQL Server 2005. In this example, I am trying to restore 'Column1' from DB2.Table1 into DB1.Table1: begin transaction ...
This seems so simple, but I just can't figure it out. I want to simply join 2 tables together. I don't care which values are paired with which. Using TSQL, here is an example: declare @tbl1 table(id int) declare @tbl2 table(id int) insert @tbl1 values(1) insert @tbl1 values(2) insert @tbl2 values(3) insert @tbl2 values(4...
Lets say I have a table that has a bit column named Active. By default, the column will contain a value of false for every row except one. When I choose to use a gridview to update a new row and have its 'Active' column change from false to true... How can I modify the following update command to update all previous rows to false when ...
The DB resides in SQL Server 2008 ... I have a db that resides in a different country, i want the tables DateCreated and DateUpdated to have Date and Time of e.g. Canada/ON/Toronto time zone instead of the time zone of US where db is located. DateCreated has a default value GetDate() and DateUpdated has the following trigger: CREATE T...