Is there any difference Performance wise between these filter methods? Method 1: WHERE (@Col1 IS NULL OR t.column = @Col1) Method 2: WHERE 1 = case when @col1 is null then 1 else case when col1 = @col1 then 1 else 0 end end ...
We have built an ASP.NET application (with C#.net language) and hosted on Windows Server 2003 Operating System. The database is SQL Server 2008. Now we need to do a search involving 4 tables and one of the column of table is varchar (2400) and the other columns are of normal lengths (E.g. Varchar(50) etc.). This search gets fired when...
I need to "use a database" and create a table with the following attributes in SQL Server 2005: Table name is "quiz_mailing_list" Fields are: id (typical auto-increment primary key) email (varchar size 256) optIn (boolean or tinyint size 1) referringEmail (varchar size 256) Can someone give me the command to "use the database" and the ...
INSERT INTO files (fileUID, filename) WITH fileUIDS(fileUID) AS ( VALUES(1) UNION ALL SELECT fileUID+1 FROM fileUIDS WHERE fileUID < 1000 ) SELECT fileUID, TRANSLATE ( CHAR(BIGINT(RAND() * 10000000000 )), 'abcdefgHij', '1234567890' ) FROM fileUIDS; ...
WITH y AS ( WITH x AS ( SELECT * FROM MyTable ) SELECT * FROM x ) SELECT * FROM y Does something like this work? I tried it earlier but I couldn't get it to work. ...
Can someone figure out how to get rid of the NOT EXISTS statements in the WHERE clause? SELECT TOP 100 PERCENT Ftm.AcctID AS [Acct Id], Act.AccountNumber AS [Account No.], Act.AccountTypeId, ISNULL(Cnt.FirstName, '')+ ' ' + ISNULL(Cnt.LastName, '') AS [Full Name], Ftm.FinTransTypeCode AS [Trans Type], Ftm.F...
I noticed asp_membership uses uniqueidentifier, and to me, it seemed like a waste of space, because I could not think of a particular reason to not replace it with identity. However, SQL Server is not letting me change it to int. It says "Conversion from uniqueidentifier to int is not supported on the connected database server". After...
I messed up. I wanted to store IP addresses compactly in SQL Server and chose 'int' for the column type. 'int' are 32 bit signed integers while IPs really are 32 bit binarys. My question is: How do I convert my existing signed int into Binary(4) in SQL Server and how should I properly parse the string-IP representation from .Net 'Reques...
I am having difficulty updating records within a database based on the most recent date and am looking for some guidance. By the way, I am new to SQL. As background, I have a windows forms application with SQL Express and am using ADO.NET to interact with the database. The application is designed to enable the user to track employee att...
Please see below SQL Server 2005 script Declare @xmlData XML SET @xmlData = '<?xml version="1.0"?> <bookstore xmlns="http://myBooks"> <book genre="autobiography" publicationdate="1981" ISBN="1-861003-11-0"> <title>The Autobiography of Benjamin Franklin</title> <author> <first-name>Benjamin</first-name> <l...
Have a new project with the following setup and requirments:- My client has a MSSQL 2005 server (A) in their office. Their vendor has a MSSQL 2005 server (B) in another part of the world, which contains real-time transactional data. My client wants to load the data from (B) to (A) on a daily basis during non office hours. They have data...
Hi, how big (binary(xy)) should I make my table column in SQL database if I want to store there pictures taken by camera - that means variable size up to.. I don't know.. 7MB? But if I should rather limit the size up to 2MB or something, I would. Whats your opinion? EDIT Or where else should I store them? I am building a web gallery us...
I am creating a database in SQL Server 2005 and cannot remember the "CREATE SCHEMA" statment, if that's it, to create a new schema similar to ".dbo". I did it last night, but I cannot remember how and cannot find the article. What I'm trying to accomplish is a database organization like that of the AdventureWorks database where the tab...
I'm working with a client who has just upgraded from SQL 2000 to SQL 2008 and their view query times have gone up a lot. I had a look at the views and couldn't see much wrong with them. When I ran the view directly on the server, the times were OK. When I ran via Management Studio remotely, the time goes from 2secs to about 30secs. So,...
I have a background process that is constantly inserting into a table in real-time. It could be thousands of inserts an hour (with a few updates). This same table is used by the web application to get some data needed to display to the user. Currently whenever the background process runs its inserts/updates, the web app seems blocked a...
Hello, I have a problem with some SQL queries that are wrapped inside a transaction. Here's how the code looks like: using (SqlTransaction dbTrans = conn.BeginTransaction()) { using (SqlCommand cmd = conn.CreateCommand()) { for(Parameters p in parameterList) try { //execute insert commmand } catch { //log...
I am looking at FILESTREAM attribute in SQL Server to store files in it. I understand it stores the files on hard drive and stores the file pointer/path information in DB. Also, maintains transactional consistency in the process. There also seems to be a limitation "FILESTREAM data can be stored only on local disk volumes" for the FILES...
Edit: OK I asked the wrong question here. I'm going to be coding a stored proc that affects a lot of data, so I need to know the quickest, easiest way to roll back the data to the original state after I run a test. Old question: I have a development database holding live data. This needs to be obfuscated for privacy, particularly com...
I'm using SQL Server 2005 with a table with a primary key field with the type varchar(40). I have to get the last inserted record's primary key value. I have tried scope_identity but it is not working. How can I get this? ...
Hi, Is there any API SQL Server 2008 offers for .net application to create and grant access authorities? Thanks! ...