Are there any built in functions for SQL where I can pass in a number and it returns a Letter that matches the number. Example, I supply a 6 and it returns a "F" I realize its an incredibly long shot, just don't wanna re-invent the wheel ...
I have a database in which there is a parent "Account" row that then has a 1-Many relationship with another table, and that table has a 1-Many relationship with another table. This goes on about 6 levels deep (with Account at the top). At the very bottom there could possibly be thousands (can even go beyond 100k) of rows. On each tabl...
I have been asked to keep track of how many times each item comes up within the results of a particular query. My thought is to just store the result of a query into a tracking table and then spit the results back to the caller. I am wondering what the most efficient method of storing these results would be since the result set could i...
i have one simple table with following columns: id, name and parentID i created relationship diagram between id and parentID (on a same table), like simple tree, on the same table, but when i tried to user cascade delete it was disabled for me i know that it will be recursive delete if i will delete parent it will delete his children ...
I turned snapshot isolation on in my database using the following code ALTER DATABASE MyDatabase SET ALLOW_SNAPSHOT_ISOLATION ON ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ON and got rid off lots of deadlocks. But my database still produces deadlocks, when I need to run a script every hour to clean up 100,000+ rows. Is...
A colleague of mine discovered a behaviour in SQL Server which I was unaware of. CREATE VIEW dbo.vRandNumber AS SELECT RAND() as RandNumber GO CREATE FUNCTION dbo.RandNumber() RETURNS float AS RETURN (SELECT RandNumber FROM vRandNumber) GO DECLARE @mytable TABLE (id INT) INSERT INTO @mytable SELECT 1 INSERT INTO @mytable SELECT 2 INSE...
Source Table Id, Name, Address 1 A #202 1 A #203 1 A #204 2 A #202 Target Table Id, Name, Address 1 A NULL After Merge Id, Name, Address 1 A #202 2 A #202 I am using this SQL create table #S (ID int, Name varchar(25) NULL, Address varchar(25) NULL) create table #T (ID int, Name varc...
OK, I have a table with no natural key, only an integer identity column as it's primary key. I'd like to insert and retrieve the identity value, but also use a trigger to ensure that certain fields are always set. Originally, the design was to use instead of insert triggers, but that breaks scope_identity. The output clause on the insert...
Assume I have a table called "table" and I have 3 columns, a, b, and c. What does it mean to have a non-clustered index on columns a,b? Is a nonclustered index on columns a,b the same as a nonclustered index on columns b,a? (Note the order). Also, Is a nonclustered index on column a the same as a nonclustered index on a,c? I was look...
The year is 2009 and SQL Server does not have CREATE OR ALTER/REPLACE. This is what I do instead. IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'SynchronizeRemoteCatalog' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE = 'PROCEDURE') EXEC ('DROP PROCEDURE dbo.SynchronizeRemoteCatalog') CREATE PROCEDURE dbo.Syn...
I have been trying to find out if the data-type enum exists in SQL Server 2008 like the one you have in MySQL. ...
Are these statements valid? UPDATE Table1 FROM (SELECT * FROM Table2) INSERT INTO Table1 (SELECT * FROM Table2) ...
I need to keep track of different dates (dynamic). So for a specific Task you could have X number of dates to track (for example DDR1 meeting date, DDR2 meeting date, Due Date, etc). My strategy was to create one table (DateTypeID, DateDescription) which would store the description of each date. Then I could create the main table (ID, ...
Bit of a complicated SQL question here. I currently have a SELECT statement which matches several fields, like this. SELECT field1, field2, field3, field4, field5 FROM table WHERE field1 = 'variable 1' AND field2 = 'variable 2' AND field3 = 'variable 3' AND field4 = 'variable 4' AND field5 = 'variable 5' I would like to modify t...
Hey all, I'm trying to write an SQL query that would populate a dropdown box of locations. In addition, I need the query to select the location associated with a user automatically (ie: be the first in the list results.) I had the following, but recently realized that the list isn't completely alphabetized. To add another level to this...
I want to filter out several key phrases out of a dataset. Unfortunately, the only algorithm I've been able to come up with thus far is nested replace statements, such as: SELECT REPLACE( REPLACE(FIELDNAME,'</u>','') ,'<u>','') where FIELDNAME is raw HTML code stored in a table. As you can see, this is hideous. Any be...
Hi All, I have a User table where there are a Username and Application columns. Username may repeat but combination of Username + Application is unique, but I don't have the unique constraint set on the table (for performance) Question: will there be any difference (performance-wise) between : SELECT * FROM User where UserName='myuse...
I am transfering data from an old database to a new one, and I need to keep the ID from the old database when inserting them into the new database. However, it's not working. This is my Query: SET IDENTITY_INSERT RentalEase.dbo.tblTenant ON INSERT INTO RentalEase.dbo.tblTenant SELECT [ID] ,1 ,[PropertyID] ,0 ,[UnitI...
Here is my query: SELECT publicationId AS PublicationID FROM dbo.PublicationOwner WHERE ownerId = 31331 UNION SELECT AreaBuy.AreaBuyID AS PublicationID FROM AreaBuy JOIN PublicationAreaBuy ON AreaBuy.AreaBuyID = PublicationAreaBuy.AreaBuyID WHERE PublicationAreaBuy.PublicationID IN (SELECT publicationId ...
Hi I am looking for a parser that can operate on a query filter. However, I'm not quite sure of the terminology so it's proving hard work. I hope that someone can help me. I've read about 'Recursive descent parsers' but I wonder if these are for full-blown language parsers rather than the logical expression evaluation that I'm looking f...