New answers tagged

1

Since your day is divided into 5-second buckets, you'll need to do the same to your timestamp in the table. Since you're counting the 5-second buckets and not the actual number of updates, you'll need to use either a CTE or derived table. I'll use the CTE only because I think it's easier to read. WITH UpdateIntervals AS ( SELECT DATEADD(SECOND, ...


0

This sounds like a case where the key_guid in the encrypted data does not match the key_guid for the symmetric key used to encrypt it. When data encrypted using a symmetric key and is then saved in a varbinary column, the first 16 bytes are the Key_guid of the symmetric key used to encrypt the data, which explains how DECRYPTBYKEYAUTOCERT can automatically ...


11

Each server is a quad core virtual machine with 16GB of RAM provisioned on a server rack. We populate each server with the maximum of 50 instances allowed by SQL Server. Each instance will be accessed sporadically by a maximum of 10 clients simultaneously, and each database will in general only be around 100MB to 500MB in size. IMHO, your total RAM ...


2

Set a maximum of 300MB per instance and be done with it. Seriously, you can monitor it with something like this to determine which may be candidates for giving a little more or less to. SELECT DB_NAME(database_id) AS [Database Name], COUNT(*) * 8/1024.0 AS [Cached Size (MB)] FROM sys.dm_os_buffer_descriptors WHERE database_id > 4 -- system databases AND ...


18

This works too: SELECT salesid, SUM(num) FROM #BE GROUP BY salesid, CASE WHEN num >= 0 THEN 0 ELSE id END; Assumptions: Id starts at 1, hence it can use THEN 0. salesid ELSE salesid+id+1 would work as well 0 is considered positive number, hence the >= 0 (Is zero positive or negative?). Although x+0=x seems to make the = sign unnecessary, it helps ...


17

Try this: SELECT salesid, sum(num) as num FROM #BE WHERE num > 0 GROUP BY salesid UNION ALL SELECT salesid, num FROM #BE WHERE num < 0; If you want both the sum values in one row then you must create a maxValue (and minValue) function and use this as sum(maxValue(0, num)) and sum(minValue(0, num)). This is described in: Is there a ...


0

Paul Randal has some interesting comments on this error here. I encountered this error yesterday - I am currently investigation, will keep you posted.


2

After working on a similar question (Querying True Physical Database File Sizes), I think a query that display the different file sizes, separated, e.g. by log and database, and the size of the file on disk would be more helpful to most users. This includes the system file size and how much space it's taking on disk, as well as the SQL file size, SQL space ...


1

Even if not applicable here, please be wary of runaway queries initiated from an OPENQUERY command. These run on remote servers, and continue to do so no matter how many times the user tries to cancel from their SSMS console....these pass-thru queries will continue to run on the target server. And if, God forbid, there's a recursive loop in there, it'll ...


1

You have to be very careful. Assuming your query is not restarting and you have a rollback for that SPID. If you restart SQL Server it won't help, because transaction still would have to be rolled back. The problem with that is following: When you run a transaction in multi-CPU environment with not restricted degree of parallelism it most probably will ...


2

Another workaround for this problem is to use set based logic, replacing the CASE expression with a left join (or outer apply) to a reference table (ref in code below), which can be either permanent, temporary or a derived table / CTE. If this is needed in multiple queries and procedures, I'd prefer to have this as permanent table: SELECT ref.result_column ...


-4

one way to get around this is to include the test in the when clause i.e. case when SUBSTRING(p.Name, 1, 1) = 'a' THEN '1' ...


7

You could do that using the sql:column function and a Common Table Expression (CTE), something like this: CREATE TABLE #tmp ( col2 XML ) INSERT INTO #tmp ( col2 ) VALUES ( '<Root> <Row> <Rowid>trim this value </Rowid> </Row> </Root>' ) SELECT 'before' s, * FROM #tmp ;WITH cte AS ( SELECT col2, RTRIM(...


3

Based off of your examples queries, this should work out for you (Comments Included): -- works SELECT o1.oname AS [Order Name], (SELECT (STUFF( -- Here is the XML concatenation. (SELECT ', ' + p.pname FROM dbo.products AS p INNER JOIN dbo....


1

You can use the following UDF (inline function rather than scalar) CREATE FUNCTION dbo.INSTR ( @str VARCHAR(8000), @Substr VARCHAR(1000), @start INT , @Occurance INT ) RETURNS TABLE AS RETURN WITH Tally (n) AS ( SELECT TOP (LEN(@str)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0)) a(n) ...


4

Simplified version drop table TabA; create table TabA ( a int , b int ); insert into TabA values ( 1, 11 ), ( 2, 22 ), ( 3, 333 ); select * from TabA; --T1. Run T1 first. begin tran t1 insert into TabA values (5, 55 ); waitfor delay '00:00:10'; --Run T2 in the other session within 10 sec. -- After delay expires deadlock is ...


-3

You need to convert date into nvarchar first through your query: Convert(varchar,[date_attribute],106)


7

To monitor disk space usage and database file size I use this script that you can find on my blog: -- create a temporary table to hold data from sys.master_files IF OBJECT_ID('tempdb..#masterfiles') IS NOT NULL DROP TABLE #masterfiles; CREATE TABLE #masterfiles ( database_id int, type_desc varchar(10), name sysname, physical_name ...


9

"Better" in what sense? The LIKE version is sargable as you have no leading wildcards and can use index seeks so that is better in that respect. In this case you could also consider SELECT * FROM TableA WHERE acccode LIKE '1[3-6]1%' OR acccode LIKE '121-111%' As a simplification though the fully expanded version might be more efficient. If you ...


0

One general way which requires no EXCEL no OLE DB providers installed on sever machine is to create XML according to MS Office Open XML specification https://support.office.com/en-us/article/Open-XML-Formats-and-file-name-extensions-5200d93c-3449-4380-8e11-31ef14555b18 . To create a template for your data just create .xls doc you need using EXCEL 2007+ and ...


4

The date type can't store partial dates, like only year, or only day and month. In one project I had a need to store incomplete dates and I used three separate nullable columns for day (tinyint), month (tinyint) and year (smallint). I don't see in what cases you'd need to use varchar instead of integer types. In my case it was information about customer ...


0

I think you can't add this value as date type. Because date type is special type, you want to use it so you must accept its rules which are defined by Microsoft. You may use another type like varchar to save your value easier. It surely can not use wrong syntax of date type. If you want to save year only (just for querying, no need to compare or calculate, ...


0

First try DROP SCHEMA schema_name If the schema contains objects, the DROP statement fails. Why? I was trying to transfer a scheme that existed on a user account that came over when a database moved. I needed to delete the user but was getting hung-up on the schema that came with it. The schema matched the user name. I tried the solution at this ...


4

Is there anyway to tell up front whether the blanket grant has been applied? For the blanket grant I don't see any users set up under the Permissions page in SSMS for any of the SPs in question and yet it just works. Below is a TSQL method to find all explicit DB object permissions and members of fixed or custom DB roles per each DB you run it ...


0

Not sure if this helps but for me my temp table column was set to decimal, I was passing convert(decimal(15,2), 0.65) from the insert into temp_table. This was auto rounding, I changed the column type to decimal(16,2) to match what was being passed. Table is now storing 0.65.


1

To anyone who cares, Our SQL 2008 instance was not on SP3. After installing SP3 everything looks to have taken care of itself.


22

There is a (closed) connect item requesting support for NULLS LAST in SQL Server. A couple of other ways would be ORDER BY CASE WHEN FullName IS NULL THEN 1 ELSE 0 END, FullName, Login I prefer this as it doesn't rely on hardcoding a string that it is assumed no legitimate data will sort after. I'd rather not have to consider ...


7

Select FullName, Login FROM [User] ORDER BY CASE WHEN FullName IS NULL THEN 'ZZZZZZZ' ELSE FullName END, login asc


1

The MSDN page says this about reorganizing and rebuilding indexes: The SQL Server Database Engine automatically maintains indexes whenever insert, update, or delete operations are made to the underlying data. Over time these modifications can cause the information in the index to become scattered in the database (fragmented). Fragmentation exists when ...


7

The view is called whatever is listed in the CREATE statement, which would look like this in the script. CREATE VIEW viewA AS The vw_tableB is an table alias https://technet.microsoft.com/en-us/library/ms187455.aspx The readability of a SELECT statement can be improved by giving a table an alias, also known as a correlation name or range variable. A ...



Top 50 recent answers are included