Tag Info

Hot answers tagged

12

When you do top 1 the query optimizer will build a plan that is built to fetch 1 row as fast as possible. When you use a local variable the value of the variable is unknown to the optimizer and instead builds a plan that is optimized to fetch 100 rows as fast as possible. In your case the query plan generated with a row goal of 100 is the better plan to ...


9

Don't. While is possible to use context_info, that is a really really really bad choice for a password. All users with VIEW SERVER STATE will see it in sys.dm_exec_sessions. SQL Profiler will not know what you're doing when you set it and will not obfuscate it in produced events. And more similar badness. Instead use proper encryption hierarchy. Do not ...


8

No. This hard-coded restriction exists for good reasons related to possible excessive query plan compilation time. You can workaround it by listing the VALUES clauses in a CTE, then INSERTing from the CTE, but I do not recommend it. Break the INSERT statement up into VALUES clauses of <= 1,000 lines each as a workaround, or use an alternative data ...


6

If you are using SQL Server 2008 R2 RTM or older then there is a CMD Command "SYSTEMINFO" which has information about System model. OR If cmdshell is enabled on instance then you can execute it from SQL Server Management Studio. DECLARE @result int EXEC @result = xp_cmdshell 'SYSTEMINFO' System Manufacturer: VMware, Inc. System Model: VMware ...


6

I guess that when Type has a certain value ('Test'), you want the Structure and Description columns to be considered for the grouping but otherwise to not use them at all. This is one way to do this: GROUP BY Type, Account, CASE WHEN Type = 'Test' THEN Structure ELSE '' END, CASE WHEN Type = 'Test' THEN Description ELSE '' END The '' should ...


4

You need to double-up apostrophes to embed them within a string, since they also serve as string delimiters: INSERT dbo.table(column) VALUES('''affinity mask'',0');


2

You are missing a new column generated by unpivoting attributes SELECT itemNumber, attributes AS [SourceName], Value As [SourceValue] FROM ( SELECT itemNumber, type, code, description FROM myTable ) AS tb UNPIVOT ( Value FOR attributes IN ( type, code, description) ) AS myValues


2

You should not be concerned on how SQL Server stores the data. For now, you should use the TIME data type and if you want the 12 hour format, then convert it in the front end application or during SELECT statement using CAST() or CONVERT()


2

Variable length character types only store actual content put into it and odn't keep the extra space allocated (thus the name "variable length"). As a result, when you INCREASE the size of a variable length column, the data storage doesn't actually change (in the table or the index). The SQL engine is smart enough to know that and doesn't do anything to ...


2

The error isn't coming from the trigger. It's coming from your insert statement. INSERT INTO PixTest(ID, TESTE) VALUES (1, 'TESTE 1') It's getting parsed and returning an error before you ever get to the trigger. Change it to this and it works. INSERT INTO PixTest(TESTE) VALUES ('TESTE 1')


2

No. Full recovery model is a prerequisite of AlwaysOn Availability groups as per the check list here, and minimally logged operations are only available under Simple or Bulk Logged recovery. Quote from the Data Loading Performance Guide: Minimally logged operations are available only if your database is in bulk-logged or simple recovery mode. Re ...


1

Something like the following should be good enough to get you started. It should be quite lightweight as well. DECLARE @Name NVARCHAR( MAX ), @SQL NVARCHAR( MAX ); DECLARE @t_BindingErrors TABLE ( ViewName NVARCHAR( MAX ), ErrorMessage NVARCHAR( MAX ) ); DECLARE c CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR ...


1

You can treat the union as a subquery. SELECT TOP 1 DBID, UPC, Price1, Price2 FROM (SELECT DBID, UPC, Price1, Price2 FROM Table1 UNION SELECT DBID, UPC, Price1, Price2 FROM Table2) sub ORDER BY Price1 DESC


1

Does this do what you're after? SELECT TOP (1) WITH TIES TX.COL1, TX.COL2, TX.COL3, TX.COL4 FROM dbo.TESTX AS TX ORDER BY ROW_NUMBER() OVER ( PARTITION BY TX.COL1 ORDER BY -- Priorities CASE COL2 WHEN 'dog' THEN 1 WHEN 'cat' THEN 2 WHEN 'bird' THEN 3 ...


1

Use RANK() function with CTE_X as ( select *, RANK() over (partition by Col1 order by Col2 DESC) rnk from t ) select * from CTE_X where rnk = 1 ; For reference (BOL) http://msdn.microsoft.com/en-us/library/ms176102.aspx


1

CREATE PROCEDURE sp_insert_allocated_time @p_project_id INT, @p_allocated_time INT AS BEGIN DECLARE @sum_alloc_time int, @alloc_hours int, @fld_id int select @sum_alloc_time = SUM(fld_allocated_time) from dbo.Timesheet where fld_project_id = @p_project_id; select @alloc_hours = p.fld_allocated_hours, @fld_id = p.fld_id FROM dbo.Project p INNER JOIN ...


1

I found a script: SELECT SERVERPROPERTY('computernamephysicalnetbios') AS ServerName ,dosi.virtual_machine_type_desc ,Server_type = CASE WHEN dosi.virtual_machine_type = 1 THEN 'Virtual' ELSE 'Physical' END FROM sys.dm_os_sys_info dosi If you have a CMS configured, run the below Script from your CMS against multiple servers: SELECT ...



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