Tag Info

Hot answers tagged

17

1. is a NUMERIC (or DECIMAL) constant, while 1 is an INTEGER constant. In some cases it is useful to specify the data type of a constant explicitly to avoid an unnecessary (or undesirable) implicit type conversion. Consider, for example create table t(f1 int); insert into t values (2); Then select 1/f1 from t returns 0 (INTEGER), while select 1./f1 from ...


9

Just a note: these new data types support the same sizes as the deprecated types they replace, e.g. 2GB of data (which means a different number of characters depending on Unicode and other factors). One thing for sure is you should parse all of your existing application code, stored procedures, functions etc. for instances of built-ins like UPDATETEXT, ...


7

Since you can reset the IDENTITY by issuing a simple TRUNCATE: DECLARE @sql NVARCHAR(MAX) = N''; SELECT @sql += N'TRUNCATE TABLE ' + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N';' + CHAR(13) + CHAR(10) FROM sys.tables AS t INNER JOIN sys.schemas AS s ON t.[schema_id] = s.[schema_id] INNER JOIN sys.identity_columns AS ic ON t.[object_id] = ...


7

First create an auxiliary numbers table larger than the maximum range you will ever be interested in. CREATE TABLE dbo.Numbers ( N INT primary key ); WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1), E02(N) AS (SELECT 1 FROM E00 a, E00 b), E04(N) AS (SELECT 1 FROM E02 a, E02 b), E08(N) AS (SELECT 1 FROM E04 a, E04 b), E16(N) AS (SELECT 1 ...


6

;WITH x AS ( SELECT ID, VAL, Prev_VAL = LAG(VAL, 1) OVER (ORDER BY ID) FROM dbo.your_table ) SELECT ID, VAL, Prev_VAL, Cumulative = VAL + Prev_VAL FROM x WHERE VAL < Prev_VAL ORDER BY ID; If you need to support versions older than SQL Server 2012 (when LAG() was introduced), you can do this, but by casual and hardly-scientific observation, it is ...


6

First thing is to change the max memory setting on your instance using either the GUI or sp_configure. EXEC sp_configure 'max server memory (MB)', 1024 Then go into SQL Server Configuration Manager and stop and re-start the instance. You don't actually have to restart your laptop. That will fix it so that your instance won't use much more than 1gb. I'm ...


5

Making the assumption that you are using SQL Server (because the RDBMS is going to matter here) you can do the following ALTER TABLE tablename REBUILD That being said you can read this article by Paul Randal as to why you shouldn't. Unless you are using your table as a staging table where you want a quick import but then clean the table out later anyway ...


5

Two subqueries? SELECT Q1.A, Q1.B, Q2.C, Q2.D FROM (SELECT SUM(A) A, SUM(B) B FROM @X) Q1(A, B) CROSS APPLY (SELECT SUM(C) C, SUM(D) D FROM @Y) Q2(C, D);


5

The cool way is to use an unattended installation script that simply is called from each server, where the media for the installation is on a central directory on your network, that is accessible from each server. You will have to run the script in an elevated mode, which this is fairly easy in PowerShell. You will need to use the CLI to extract the hotfix, ...


4

It is great that you are taking the time to understand, classify and model the data you are dealing with since, from my personal experiencie, all this makes the whole development process easier and very flexible for future changes. And I am quite sure that you are also aware of this already. Preliminary data model and assumed business rules I defined a ...


4

This should be good up to about 2,500 values (depending on version): ;WITH x(n) AS ( SELECT TOP (@variableNumberOfIdsNeeded) (ROW_NUMBER() OVER (ORDER BY number)-1) * CONVERT(BIGINT, @SeqIncr) + CONVERT(BIGINT, @FirstSeqNum) FROM master.dbo.spt_values ORDER BY number ) --INSERT @newIds([NewId]) SELECT n FROM x; If you need more, or ...


4

SQL Server is configured to work this way. It assumes that if you need the memory once, you'll need it again. It's expensive to constantly give this memory back to the operating system and ask for it back again, so why give any of it back to the operating system? The easiest solution for now is to just restart the SQL Server service - this will return you ...


4

The query will be executed by sp_send_dbmail asynchronously, on a separate session that has msdb as current database. So you must use three-part names in your query: @query = N'select substring(PostOfficeBox,1,4) as [Postcode] ... from TestCRM.dbo.AccountExtensionBase as a JOIN TestCRM.dbo.CustomerAddressBase as b ON a.AccountId = b.ParentId ...'


4

Other than the minor things you've already picked up on (additional storage requirements, and potential performance differences as a result of requiring more I/O for the same number of rows), no, I can't think of any real gotchas with adding a ROWVERSION column to these tables. One potential issue, though, with merge replication specifically, is if Access ...


4

You could use a logon trigger perhaps, going forward (see here and here). For historical information, by default, SQL Server doesn't even track successful logins (though this can be turned on - however it means you will need to parse the SQL Server log for some little bit of information). So while you might get some information (such as which workstations ...


4

Something seems really, really, really bad in this design. Do you really have a large number of tables that have a column called Internet? What does that column even mean? Do you really want to call this stored procedure such that it updates every row in the specified table? Why? You don't have a one-row table for every location / city / room, do you? ...


3

If I understand what you are asking then like this. WHERE DateModified BETWEEN @BeginDate AND @EndDate AND (@Troop IS NULL OR d.CreatorLEA = @Troop)


3

Oh Boy! System Databases on C Drive are recipe for disaster ! Change the default database location away from C:\ drive. You can do it using ALTER DATABASE .. MODIFY FILE Change the db file location ALTER DATABASE db_name MODIFY FILE (NAME = logical_data_file, FILENAME = 'D:\data\physical_file.mdf'); ALTER DATABASE db_name MODIFY FILE (NAME = ...


3

You can write a script to call DBCC CHECKIDENT ([TABLENAME], RESEED, 0) over the tables resulting from this query (which will inform of all the tables with an identity field. SELECT t.TABLE_NAME ,c.COLUMN_NAME ,c.TABLE_CATALOG ,c.TABLE_SCHEMA FROM ...


3

You can use the following approach in SQL Server to pivot the data into the format you are looking for. You may also be able to use the PIVOT operator, but I tend to prefer to write out the CASE statements since I find that syntax more clear (they have always resolved to the same query plan in all cases that I have checked). Updated answer based on Aaron ...


3

Here's one way. Assuming this table and sample data: USE tempdb; GO CREATE TABLE dbo.splunge(UserID CHAR(5), Name VARCHAR(32), Date DATE); INSERT dbo.splunge(UserID, Name, Date) VALUES('t0001','Tod','20150629'), ('t0001','Tod','20150629'),('t0001','Tod','20150629'),('t0001','Tod','20150629'), ...


3

It could happen that a small amount of data reaches a certain limit in the SQL Server to force another plan or something like that. This is not unlikely. But the fact that your disc seems to be heavily under duty takes me to another conclusion. There are 2 possible base reasons for your slow down. You upgraded your system and rebooted it You load a bunch ...


3

EXEC msdb.sys.sp_executesql N'ALTER ROLE ...';


2

Here's one way: SELECT MAX(A), MAX(B), MAX(C), MAX(D) FROM ( SELECT SUM(A) AS A, SUM(B) AS B, NULL AS C, NULL AS D FROM X UNION ALL SELECT NULL AS A, NULL AS B, SUM(C) AS C, SUM(D) AS D FROM Y ) AS T You may have to cast NULL to whatever type A, B, C and D have


2

I think you're running the restore with the NO RECOVERY option. This will restore your database, but will leave it in the RECOVERY state. You can easily check this by taking a look at it in the SSMS. It should say something like: yourDatabaseName (Recovering...) If you haven't the option to take a look in your SSMS, you can simply querying your master ...


2

You are getting deep into internals here Membership in the local Administrators group, or equivalent, is the minimum required to run DiskPart And this SE thread points to the same. Now usually you want to avoid giving to much permission to the service accounts and I'm not certain if giving the SQL Server agent permissions to mount/unmount volumes ...


2

Check this article. According to that article the transaction log backup is the only way how to reduce the size of the transaction log in the FULL recovery mode. Shrinking will help only in a situation when you for example have forgotten to take transaction log backups and your transaction log file grows and then you take the backup and you need to shrink ...


2

AWE is not a "process", it is an API used for memory allocation. Quick googling shows that SQL Server may be using that API to allocate its bufferpool pages. It must be a quirk of RAMmap to show this under the AWE category. I think it is pretty normal for a database server to show high memory utilization ratios, otherwise unused memory would be simply ...


2

From a historical perspective, if login auditing was enabled on the server then you could see the logins in the SQL Log and use the IP to trace back to user's workstation or server. This is useless, however, if users are remoting into the SQL host and making a local connection to it (though they really shouldn't be...) If that's not available to you, then ...


2

When you try to connect to a local instance, SQL Server will always attempt to use Shared Memory. This connection will work fine: localhost\SQL2012 This works in my scenario, and the connection is indeed using Shared Memory in this case. SELECT session_id,net_transport,local_net_address,local_tcp_port FROM sys.dm_exec_connections; Results: session_id ...



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