Tag Info

Hot answers tagged

8

As alluded to by @Souplex in the comments one possible explanation might be if this column is the first NULL-able column in the non clustered index it participates in. For the following setup CREATE TABLE Foo ( A UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID() PRIMARY KEY, B CHAR(1) NOT NULL DEFAULT 'B' ) CREATE NONCLUSTERED INDEX ix ...


6

When a view is created it is bound to the metadata it needs in order to execute. Note: https://msdn.microsoft.com/en-us/library/ms187821.aspx That says that sp_refreshview: "Updates the metadata for the specified non-schema-bound view. Persistent metadata for a view can become outdated because of changes to the underlying objects upon which the view ...


5

This should do what you need SELECT ISNULL(ta.[tag-value], 'N/A') AS [tag-value] FROM @AgentTableFilt ta Or you could use the following SELECT COALESCE(ta.[tag-value], 'N/A') AS [tag-value] FROM @AgentTableFilt ta


5

SQL Server 2008 R2 service packs are cumulative and can be used to upgrade all releases of SQL Server 2008 R2 to Service Pack 3. From http://www.microsoft.com/en-US/download/details.aspx?id=44271 Given the documentation, you should be able to go directly from SP0 to SP3.


5

A couple of feasible options, in order of my preference: Option 1 Create a new, empty database locally - maybe make the log file large enough, at least temporarily, to accommodate the entire set of data you are moving without growth Use SELECT INTO, the Import/Export "Wizard", or Red Gate SQL Data Compare to copy this table to the new database (note that ...


5

To answer your first question : If you are not sure, then just manually launch the setup.exe and verify. When you launch SQL Server 2008 installation using setup.exe, you will see the Below is for SQL Server 2008R2 : To answer your second question : From SQL Server 2008 and up, Microsoft has changed as the edition depends on the PID that can be ...


4

You CANNOT do this - you cannot attach/detach or backup/restore a database from a newer version of SQL Server (2014) down to an older version (2012) - the internal file structures are just too different to support backwards compatibility. You can either get around this problem by using the same version of SQL Server on all your machines - then you can ...


4

No, the option is there for you so that you can decide to either: Kick all users out right now and restore immediately; or, let the restore operation wait until it can get exclusive access to the database before it begins. You can't restore and keep connections active, sorry. This is like changing the keg without interrupting someone actively pouring a ...


4

It will definitely re-create the non clustered index and not just update metadata. This is tested on SQL 2014 and should really not be tested on a production system CREATE TABLE [z]( [a] [int] IDENTITY(1,1) NOT NULL, [b] [int] NOT NULL, CONSTRAINT [c_a] PRIMARY KEY CLUSTERED ([a] ASC)) go CREATE NONCLUSTERED INDEX [nc_b] on z (b asc) GO insert ...


4

You can use sp_attach_single_file_db or the newer version CREATE DATABASE database_name FOR ATTACH_REBUILD_LOG. In both cases it will create a new log file for you. Unfortunately because you have no log you will not be able to recover anything that has not been committed. It is also a good idea to run a DBCC CHECKDB afterwards to make sure there is no ...


3

The EnrichedVendor is not the same in the queries. I suspect that when you get a table scan you are working with products where the EnrichedVendor has a good majority of all the records in the table and therefore it makes sense to read all the table and the other EnrichedVendor has a much smaller percentile of the total records in the table. But you also ...


3

A clustered index stores all columns from the table, and is effectively the table's data source. Therefore, updating the table in any way will update the clustered index.


3

You need to use the OUTPUT parameter, as it is described in the MSDN article: Given this dummy stored procedure: CREATE PROCEDURE sp_test AS RETURN 2 This code will give you the result: declare @ret int exec sp_executesql N'exec @ret = sp_test', N'@ret int OUTPUT', @ret = @ret OUTPUT select @ret as result result ----------- 2 (1 row(s) affected)


3

The problem is that you are missing a way to tie the outer table to the data inside of the STUFF. You can do this by adding a WHERE clause to the subquery: SELECT x.SurveyQuestionID, PossibleAnswers = STUFF(( SELECT ', ' + Answer FROM SurveyQuestionPossibleAnswer sqpa INNER JOIN SurveyQuestion sq ...


2

Differential backups are backups of any changes to the DB since the last full backup. So in theory, yes, you could have it do a diff backup since some date (if you did a full backup on that date). Otherwise, no. It'll always be since the last full backup.


2

SQL Server uses statistics to determine an execution plan. If an index is available, so are statistics, and SQL server will determine the path of least work. This could be using the index or doing a table scan. In your example SQL server has determined that a table scan is less work than doing an index seek and a bookmark lookup. What you can see is that ...


2

I would always err on the side of just installing the service pack again (it will know which components, if any, to update). Especially when adding client tools to a workstation - where this is not a major disruption. (And if you're adding BIDS to a production server, you should reconsider IMHO.)


2

The !! <command> syntax lets you shell out. The external command is handled synchronously (i.e. the next line of the SQL script will not execute until this command finishes). This is why the documentation has a note in a few places stating: When you use the Query Editor in SQLCMD Mode, you must be careful not to execute interactive statements. The ...


2

Start by making a column like CASE WHEN MinutesSinceLastRequest < 20 THEN 0 ELSE 1 END AS IsOver20 Then wrap that in a rolling sum - this will change every time there's a 1 for IsOver20: SUM(IsOver20) OVER (ORDER BY [TimeStamp] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS GroupNum So now you can group by this easily. Include PARTITION BY UserId ...


1

I got this same error a few weeks ago, despite the fact the connection was fine; it was actually due to an authentication issue. If you don't have domain connectivity between the two machines, you can use certificates to create credentials that can be used for mirroring endpoints. To do this, you create a certificate on the database on each end, create an ...


1

No, there is no means of partitioning either an IDENTITY or SEQUENCE. In order to do this you would have to create a table to manage TenantID and RecordID for this table. Then you would need a function that accepts TenantID and will return RecordID. This means that you need to insert via a stored procedure that can grab that value and then do the INSERT, or ...


1

Are you just looking for a complete copy of the data to completely replace what already lives on the destination instance? In that case, a full backup of the source database and a restore into the destination instance would be the cleanest and easiest route. If you are looking at select data copying (i.e. you don't want all data, or you don't want to ...


1

I don't think that any of these suggestions are particularly improvements in performance compared to the CASE as that's already very basic. Another possible option (although again, probably not worth the effort as it's just a short-hand way of writing your CASE) for SQL Server 2012 and above. I'd be interested in seeing what the reaction was of the person ...


1

You could use the RML utilities to capture a workload trace before and after the index rebuild and compare execution times. RML utilities come with a reporter application that can compare the workload analysis of two trace files. You can download it from here: X64: ...


1

I found this answer on mssqltips and this is the link to the article I found there. I am also posting the TSQL for possible link-rot in future. T-SQL Script to Drop All SQL Server Indexes DECLARE @SchemaName VARCHAR(256)DECLARE @TableName VARCHAR(256) DECLARE @IndexName VARCHAR(256) DECLARE @TSQLDropIndex VARCHAR(MAX) DECLARE CursorIndexes CURSOR FOR ...


1

Your file is probably large but empty. In SSMS, right-click on your database and go into Tasks, Shrink Files. DO NOT HIT OK IN THIS DIALOG - but it's the easiest way of seeing how empty your files are. Choose Log in the dropdown, and I imagine you'll see that your log file is actually 99% empty. You probably had a large process that grew the file to ...


1

Many NoSQL products have sharding built-in. The DBMS itself looks after storing a particular key range on a certain server and keeping redundant copies for high availability. Client connections are routed within the DBMS rather than in the application. Multi-server scale out becomes easy, at the cost of CAP compromises.


1

Once you switch to Simple the Trans Log will no longer maintain a log of operations since your last (database files) backup - at the point of the switch all transactions will have been committed to the main database files so will not be discarded - they are already effective in the database files. Your point 1 is not (in my opinion) worth doing - once ...


1

Since the secondary in a log-shipping configuration is either READ ONLY or RESTORING, you cannot make changes to the database (i.e. create a USER) until a ROLE CHANGE happens (i.e. when you failover from primary to secondary). You can create a LOGIN on the secondary server. You can use this script - How to transfer logins and passwords between instances of ...


1

Without an internet connection, you would be looking at merge or peer-to-peer replication. Neither of which can be implemented easily (google I seem to remember seeing a workaround somewhere) how you want with Express edition. If you had standard or better edition I would personally go with Peer-To-Peer as it negates the need for an additional column per ...



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