Tag Info

Hot answers tagged

11

Personally, whenever I build a new server for a new project I always enable TF4199 globally. The same applies when I upgrade existing instances to newer versions. The TF enables new fixes that would affect the behaviour of the application, but for new projects the risk of regression is not an issue. For instances upgraded from previous versions, the ...


7

Developer Edition is free No its not free, but its very cheap. For all editions, you can check here. If you want to explore sql server (2014,2012,etc) then you can download SQL Server Evaluation Edition for 180 days and then purchase it.


7

The syntax choice is likely for consistency, albeit redundant. TRUNCATE TABLE follows the same pattern as other DDL statements (CREATE, ALTER, DROP), where the object type immediately follows the action keyword. I should add TRUNCATE TABLE is part of the ANSI SQL Standard as of the 2008 version, although it was implemented in SQL Server long before then.


6

In SQL Server, you can create file groups that consist of 1 or more files. You can then migrate data into those file groups individually by rebuilding the clustered index for each table on that file group. You can also do the same for indexes by rebuilding the index by specifying the desired file group. You can use file size caps to ensure the files do ...


5

When a SQL Server named instance configured with dynamic port is started, the dynamic port is provided by windows. The dynamic port range is outside the range of well-known static ports, where port 1433 resides. The dynamic port range is between 49152 and 65535, as documented here. At subsequent startups, the instances reuse the same dynamic port used ...


5

The CPU time is tiny compared to the amount of actual time. The second time the query runs, it's fast - presumably once the data is in RAM (being one of the few things which benefits from the second run). Sounds to me like the problem isn't SQL, but the disk. Notice the PAGEIOLATCH waits increasing while your query runs. Have a look at what's happening on ...


5

Sure : select * from database_n.dbo.Table_1 UNION ALL select * from database_n+1.dbo.Table_1 UNION ALL select * from database_n+2.dbo.Table_1 -- etc.


5

Yesterday: SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE() - 1), 0) Today of Last Year: SELECT DATEADD(YEAR, -1, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)) Today of Last Month: SELECT DATEADD(MONTH, -1, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)) Yesterday of Last Year: SELECT DATEADD(YEAR, -1, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE() - 1), 0)) ...


5

Shouldn't really need any hacks to handle leap years, but it depends on what results you expect. Typically you just subtract the larger component first, so subtract a month before you subtract a day, instead of the other way around. Do any of these produce results you don't expect? If so, which ones are "wrong" to you, and why? DECLARE @d TABLE(d DATE); ...


4

I would set it offline. I say this because SINGLE_USER mode is generally used to allow an administrator to perform some kind on maintenance on a database without users attempting to access and possibly interfering with the maintenance. It doesn't really matter either way for a restore as your users will experience the same thing which is they wont be able ...


4

You need to restore last FULL backup, the Differential, the 10:15 transaction backup and the 10:30 one with the STOPAT = '2015-05-22 22:24:00' clause.


4

You can create a trigger to update the UserID columns in related tables, such as: USE Tempdb; CREATE TABLE dbo.Users ( UserID INT NOT NULL CONSTRAINT PK_Users PRIMARY KEY CLUSTERED , ApplicationID INT NOT NULL , Country VARCHAR(255) NOT NULL , DefaultTenantID INT NOT NULL ); GO CREATE TABLE dbo.Tasks ( TaskID INT NOT NULL , ...


4

The difference between global and local temp tables isn't how long they exist, it's who can see them. Local temp tables can only be seen by the session (spid) that created them. Global temp tables can be seen by everyone, but only while the session that created them is active. If you are running it in a SQL Agent job, as soon as it completes it ...


4

You can save the index creation DDL statement and delete the indexes, to save more space before any archival path you choose. There's more advice on shrinking and fill factor here: http://www.brentozar.com/archive/2010/02/how-to-really-compress-your-sql-server-backups/ I've used option 2 to get better results than with the SQL built-in backup compression. ...


4

It does not sound like your database and application would be very big in current terms. You could look into the freely available Microsoft tools for Visual Studio Express and SQL Server Express: https://www.visualstudio.com/en-us/products/visual-studio-express-vs.aspx ...


4

That may work for your clustered indexes, but you'll also have nonclustered indexes to contend with. They can suffer a lot too, and that's often where it can matter the most. But you should consider the impact. There are times when fragmentation can be a big thing (such as when you delete data from heaps a lot), and times when it's really not such a huge ...


3

RANK() OVER (PARTITION BY xxx, yyy ORDER BY zzz) provides a number ranking for each row where xxx and yyy are identical, and orders that list by zzz The sub-query contained in your question: SELECT Sr_No FROM ( SELECT *, [Rank] = RANK() OVER (PARTITION By Email, Campaign_Name ORDER By Sr_No DESC) FROM dbo.LeadsContacts ) T WHERE T.Rank>1 ...


3

So I am wondering how this might have been broken (SQLServer update?). SQL Server update wont change any login / user or role permissions as part of update (installing CUs or SPs). The permissions might have changed due to a possible database upgrade (your application version upgrade). Check your upgrade scripts. To find out who changed the ...


3

If you are running SQL Server 2008 R2 SP1 and up then you can query sys.dm_server_registry to find out TRACE FLAGS that are enabled at startup. select * from sys.dm_server_registry where cast(value_data as varchar(max)) like '%-T%' Just incase if you or future visitors need the output in one row then, you can use below T-SQL : select distinct ...


3

The user account that is running the SQL Server agent might not have permissions to use the database mail profile you can check that with EXECUTE msdb.dbo.sysmail_help_profileaccount_sp; But you are having some issues with the agent account security, please try the following steps. Remove all extra privileges from the Agent user account (or create a new ...


3

The validity depends on how users want to look at the data. You are looking at it as just a transaction fact. Other types of fact tables include periodic snapshots and accumulating snapshots. If you want to see all the times that someone corrected a row to help decrease erroneous entries, the effective dates may be appropriate so it's clear that the ...


3

There are basically two things to consider: You cause fragmentation when shrinking the database If the database grew yesterday, there's a good chance it will grow again tomorrow. To address 1, you need to defrag indexes once you've shrunk the database. The database will grow when you do this, although probably not back to the original size. To address ...


3

While I am not sure of the exact reason for those specific characters, the issue has to do with the older collations. And it is not just empty string that they equate to, but also to just one of those characters: SELECT * FROM (SELECT N'ግዜ') tab(col) WHERE tab.col = N'ግ' And if you try a case-sensitive collation, or even a different code page, they still ...


2

The scripts go over all of your tables, rebuild all the indexes and update their statistics. The reasons they CAN improve performance is that they reduce the index fragmentation and force SQL Server to generate new execution plans to queries, that may be more suitable. I'm not sure, though, why you need to run the first command which disables the indexes.. ...


2

First of all, I have to ask: Why are you doing this? If you have a performance issue, pursue it. However, you only mention that you are seeing scans instead of seeks. Scans are not always bad - they can be the most efficient method of pulling large amounts of data since sequential file access is less costly in I/O terms than random access. The commenters ...


2

The data should be "the same" as on my machine, that I can dismiss my server and point to new machine in the end. Based on your comments, your best option would be Database Mirroring or Log shipping. You have to evaluate your RPO and RTO. Since you are using VPN to your actual machine - meaning it is point-to-point connection : If you go with ...


2

It sounds like you're trying to setup a disaster recovery environment, so that if your local machine blows-up you can switch over to the Azure machine. There are a number of options, each with their own pros and cons. These are well documented elsewhere (see this link for an overview from Brent Ozar: ...


2

It's not supported, but you can get around it.. select * from openquery( LinkedServerName, 'exec [database].dbo.sproc @Id=@Id, @PItems=@PItems output'','' @PId bigint, @PItems xml output'', valueOf@PId, valueOf@PItems output' ) There ...


2

No buffer pool and VAS are not the same thing. In SQL Server direct physical memory access is not allowed any memory request which comes is first mapped to process VAS and then if SQL Server find memory free it would map this VAS address to physical memory and then memory becomes committed. Bufferpool is physical memory VAS is virtual memory. VAS VAS is ...


2

No loops are needed in this (please!). You should be doing an INSERT...SELECT. Always run the SELECT part of the statement on it's own first, to make sure it is only selecting what you expect to be inserted. INSERT INTO subscription(username,name,startdate,enddate,saledate,pkgid) SELECT DISTINCT username, name, startdate, enddate, saledate, 41 ...



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