Tag Info

Hot answers tagged

10

In theory, and particularly in modern versions of SQL Server, the order of the WHERE clauses makes absolutely no difference. SQL Server will generally process the filters in the most efficient order, and will pick the index that it determines will allow for the most efficient query. A difference that may occur is when the WHERE clause includes JOIN criteria ...


7

If the list of payment types and currencies are known, you can do this with CTEs. with paymenttypes as ( select 0 as ptype, Union select 1 as ptype, Union select 2 as ptype ), currencies as ( select 'Aaa' as currency union select 'Bbb' as currency ), sourcedata as ( select currency, ptype as paymenttype, 0 as amount from currencies, ...


7

Passwords are case-sensitive. Passwords should always be at least 8 characters long, and cannot exceed 128 characters. Passwords can include a-z, A-Z, 0-9, and most non-alphanumeric characters. Passwords cannot contain single quotes, or the login_name. https://msdn.microsoft.com/en-us/library/ms189751.aspx


6

In order to understanding why error 3456 would be thrown, we need to take a little step back and understand how SQL Server handles this corner of recovery. When SQL Server is redoing an operation, and that redo is a page modification, it makes a quick check. In the page header there is ultimately going to be a PageLSN, which is an indication of the last ...


6

No sane DBA will ever allow such a procedure. This is a SQL injection privilege escalation vector. I can pass in the tablename 'x; exec sp_myfoo;' and voila. There are basic issue: table names are NVARCHAR, not VARCHAR table names are length 128, not 60 sysname is a handy type to represent object names, is an alias for NVARCHAR(128) tables are qualified ...


5

You want to drop the values part of the query and do an INSERT\SELECT. This is really basic SQL and you can read more about it in BOL. INSERT INTO wtdocversion (col1, col2, col3) SELECT id, 'col2', 'col3' FROM wtdocmaster;


5

Not sure what you meant by A <> ''; but here is an example that should give you the right idea at least. USE [tempdb] ; GO CREATE TABLE [dbo].[tbl] ( [name] VARCHAR(15) NULL , [A] VARCHAR(15) NULL , [B] VARCHAR(15) NULL , [C] VARCHAR(15) NULL ); GO INSERT INTO [dbo].[tbl] VALUES ('name1','A1','',''); INSERT INTO [dbo].[tbl] ...


4

No, it does't, and never did. The post you link links to the blog http://blogs.msdn.com/b/sqlexpress/archive/2008/02/22/sql-express-behaviors-idle-time-resources-usage-auto-close-and-user-instances.aspx that explains that the 'instance going to sleep' is a RANU behavior (user instance), not an Express behavior. As you plan to move from Standard to Express, ...


4

Because each sqlcmd execution is a separate session, it means you can't have transactions spanning those executions. You're going to want to combine your scripts into a single script for you to do what you want. If you can properly order your scripts, the following approach in Powershell will work for you: $scripts = Get-ChildItem "C:\temp\*.sql" ...


4

A SQL Server 2008 R2 mirror is not itself readable. If you are using Enterprise Edition, you can create a named snapshot database from the mirror. This allows you to create a point-in-time readable database that can be used for pretty much any query that does not do any updates. (Though you could use another database (tempdb or a workdb) to do auxiliary ...


4

CREATE DATABASE [your_database_name_here] ON ( FILENAME = N'C:\sql server\nologfile.mdf' ) FOR ATTACH_REBUILD_LOG; GO -- Use this 'ATTACH_REBUILD_LOG' when you have one or more log files. 'FOR ATTACH' works with one and only one log file A POSSIBLE TEST DEMO... USE [master]; GO IF DATABASEPROPERTYEX(N'TlogDemo' , N'Version') > 0 BEGIN ...


4

SELECT Func(PK),count(*) FROM tab GROUP BY Func(PK) HAVING Count(*)>1 ; Where Func() is whatever you're using to clean up the PK column spaces Example: SELECT LTRIM(RTRIM(PK)),count(*) FROM tab GROUP BY LTRIM(RTRIM(PK)) HAVING Count(*)>1 ; Example2: (as suggested by Martin in the comments) WITH cte AS (SELECT ROW_NUM() OVER (PARTITION BY ...


3

Assuming a not very old version of SQL-Server, you can use ROW_NUMBER() to find one row per submitter: ; with r as ( select id, submitter, answer, rnd = row_number() over (partition by submitter order by newid()) from #tmpData ) select id, submitter, answer from r where rnd = 1 ; Test at: SQLfiddle


3

Since I can't see your table structure and I don't know the size of your data, here's something that will return the right answer but may or may not need to be tweaked for performance. You can write a query that groups both created and closed tickets together by date. Although you could just write two queries and do a full outer join, I would guess you want ...


3

My Network Admin. wants an "Active/Active" with shared storage There is no such thing as an "active/active" instance. Only one node of the cluster can be hosting the SQL instance for a failover cluster instance. If by "active/active" you mean having two different SQL Server failover cluster instances, that is possible. Why shared storage? That's an ...


3

3GB is not really a lot of data. My laptop has 16GB memory, our datawarehouse is 3TB and that would be a fairly small warehouse. The maximum database size according to here is 524,272 TB!? So I think performance here is more about the kit you are on, plus index and query design. You could refactor as Russell has suggested. However you could also ...


3

Yes, this still holds true. It is covered in Upgrade Replicated Databases in the SQL Server 2014 documentation.


3

My motto is to always use the most recent version of the tools and always have tools >= highest engine you manage. There are a couple of exceptions, in addition to those others have mentioned: For SQL Server 2000, I always install the 2000 client tools. If you use DTS/SSIS you will want to have the tools relevant to the version of your packages. If you ...


2

You mean how to pick a random row from the data? This is one way to do it: declare @data table ( value int ) insert into @data values (16), (17), (18), (22), (24) select top 1 value from @data order by newid()


2

Having a wildcard in front of your search term will prevent SQL Server from being able to use an index and thus become painful very fast. 2 things you could do, besides going for full text search: The obvisous one, do not put a wildcard at the start of the keyword requiring the user to enter a search query matching the start of col1. Splitting col1 from ...


2

There are a few approaches to handling this problem. Here are three options: Create a FingerprintBLOB table (perhaps in a different database) for the BLOBs of fingerprints then drop that column from theOriginalTable. That way when you SELECT TOP(1000) * FROM OriginalTable you will not need to read the BLOBs from the FingerprintBLOB table. This should ...


2

If you run sp_helpfile in the master database on your SQL Server 2014, this will tell you where your main database files are, eg Copy your extracted AdventureWorks .mdf file to that location, then attempt to attach. That should work. The reason you probably can't 'see' the directory you want is that SQL Server does not have access to it. Alternately, ...


2

Do not use period in database names, publication names, user names. And I strongly recommend never to use a period in name (column, table, view, database, etc). Update: I can confirm, that the minus, dash "-" causes simmilar issues. Here is what happens: SQL Server uses scripts for internal usage like system stored procedures. As already mentioned here, ...


2

No, it is executing TRUNCATE TABLE in the context of the current owner of the module. So, as long as the owner of stored procedure has the ALTER TABLE permission, it will work. Anyone who has CREATE PROCEDURE permission in the database and ALTER permission on the schema in which the procedure is being created, can create the procedure. Any member who has ...


2

Option 1 will always force a O(N) operation (a table/index scan) which disqualifies this option for significant amounts of data. You must use option 2 if you want a solution that scales with the amount of data. If there are very few rows (such as 3 or so) option 1 might actually be faster. I doubt that we are talking about that case here.


2

Definitely you want to use option 2. Not only will your queries be faster (= is always faster than like) but you can also index on that keyword field for even faster queries AND your storage space will be significantly reduced since you are not storing the long keyword strings for each website.


2

I have been using it about a year now connecting also to SQL Server 2005. There are some features that don't work, at least following two I can remember now: Connecting to profiler doesn't work. I noticed there is a patch for this, but it's not yet in the service pack. The error says "SQL Server Profiler cannot perform this action on database servers ...


2

That depends on your definition of major complication. Will it somehow cause your data to be affected in a negative way? No. Would I recommend using SSMS 2014 to administrate a production 2005 instance? No, because you're going to run into a lot of issues when using the GUI to perform many types of operations. Even if they are supported in 2005, they may ...


2

Just pull the DataTable object(s) and enumerate the DataColumnCollection. Here is some sample code to show how you can enumerate the collection and dump the column names: DataTable queryResults = // get your data foreach (DataColumn column in queryResults.Columns) Console.WriteLine("Column Name :: {0}", column.ColumnName); Of course this is an ...


1

Your data sample is not very clear but it looks like another PIVOT question :) Also, your openrowset tag is probably wrong.



Only top voted, non community-wiki answers of a minimum length are eligible