Tag Info

New answers tagged

0

Assuming you need an exact count and not an approximation, and you want this to go fast without index scanning (yes, please!), a good place to start would be to create an indexed view that maintains the count of rows: CREATE VIEW dbo.MyView WITH SCHEMABINDING AS SELECT COUNT_BIG(*) AS NumberOfRows FROM [dbo].[Products]; GO CREATE ...


-3

It's got to sort the data for the clustered index some where and then rewrite your rows in that order. Don't forget that SQL Server will add an extra integer, the "uniquifier", so that it can disambiguate between the non-unique values of "myColumn". This will cause all your non-clustered index to include "myColumn" + "uniquifier" (4-byte).


2

Although you had SORT_IN_TEMPDB = OFF, that is not the only thing that uses tempdb. You also had set ONLINE = ON. Since the job ran 50 minutes before you had trouble, it may be that you had enough activity in that single transaction to fill tempdb with row versioning data. This is described here: http://technet.microsoft.com/en-us/library/ms179542.aspx ...


0

If I do a Paste Special, I get two options, Text and Unicode, and selecting Text seems to work properly, where as Unicode does not. as per comment by @JonSeigel


4

As of SQL Server 2008, IIS is no longer used by SQL Server Reporting Services. It utilizes the HTTP.sys API now. So you will not see SSRS show up as a site or application pool. The tip you linked to deals with SQL Server 2005, which required IIS in order to use SSRS. This tip speaks of the slowness you might be seeing and how to address it in SQL Server ...


0

Here are my answers: Define relationships based on your surrogate keys and not your business keys. This is because this is what the SQL engine will use to optimize queries and this is the reason you have surrogate keys. Since surrogate keys are often integers, this simplifies the joins from using different data types such as strings which are less ...


0

You can read more about the Policy Based Management and centralized policy management using a central management server in the SQL Server Policy Based Management – evaluating policies on multiple SQL Server instances online article. It describes how to create the central management server too


2

I don't think collation is your problem as that is related to sorting and ordering (although you might get a problem later using ORDER BY and so on), but you mention you have some VARCHAR fields. Those will not accept Unicode characters (which Cyrillic certainly are): They will need to be NVARCHAR throughout to do that. Next, how are you inserting your ...


3

You have to specify that you are inserting wide character data: CREATE TABLE #t (id INT,c1 VARCHAR(MAX),c2 NVARCHAR(MAX)); INSERT INTO #t VALUES(1,'žđšćč žđćčžđšćčŽĐŠĆČ','žđšćč žđćčžđšćčŽĐŠĆČ'); INSERT INTO #t VALUES(2,N'žđšćč žđćčžđšćčŽĐŠĆČ',N'žđšćč žđćčžđšćčŽĐŠĆČ'); SELECT * FROM #t; DROP TABLE #t; Result:


0

č is not part of Latin 1. You will need to use either ISO-8859-2 or Windows-1250 or start using Unicode at all (if applyable the Unicode-version is the future safe version). In oyur case you might have to reconfigure you database here. Maybe just a link https://en.wikipedia.org/wiki/Latin-1


1

There is a way to setup the number of times to retry and to set the interval between each try inside the SQL Agent Job. Simply open the job and edit the job step. Click on advanced link. Once there, you can set the retry attemps and retry interval see below. You can also setup the package to restart from the point of failure. I have not done that personally ...


1

Since Master Data Services is available on the 64-bit editions of Business Intelligence and Enterprise only, you can leverage partitioning for data management. You can partition the data by month or a quarter or year (depending on your requirements). This way you have a solid archival strategy and you can load new data into new partitions. You can have ...


1

Snapshots do have some performance overhead. With a dozen snapshots you could have up to 12 additional pages written to disk for each page written to the main DB, depending on your write patterns. Consider what you want to do with these snapshots. If they're simply for putative future auditing then do a backup and get them off your production server. You ...


0

I tried to install SQL Server 2012 and 2014 on the WIN2008R2 server with Sql Server 2000 Desktop Edition named instance installed. The installation process failed showing error: TITLE: SQL Server Setup failure. SQL Server Setup has encountered the following error: '.', hexadecimal value 0x00, is an invalid character. Line 1, position 444345. Error code ...


1

Your schema diagram is good. You should not put any total cost column either to Recipe or RecipeIngedient as it is not compliant to normalization. And so there is no need for triggers. The total cost sum should be calculated on the fly in a query that you use to list recipes.


0

They're all listed in Setting Database Options: bulkcopy: ALTER DATABASE .. SET RECOVERY BULK_LOGGED; trunc. log: ALTER DATABASE .. SET RECOVERY SIMPLE; dbo use: ALTER DATABASE ... SET RESTRICTED_USER; single: ALTER DATABASE ... SET SINGLE_USER; ansi null default: ALTER DATABASE ... SET ANSI_NULL_DEFAULT ON; db chaining: ALTER DATABASE ... SET DB_CHAINING ...


0

As noted, mirroring is not dead. (At least not yet.) The general population doubts that Microsoft would totally abandon HA for Standard Edition. However, there are other methods. In the SQL Server 2012 documentation when referring to mirroring it says: "If your edition of SQL Server does not support AlwaysOn Availability Groups, use log shipping." ...


0

So one potential way of doing this would be to write your SQL Server query to output an XML string of the data you want to send by using FOR XML. You could then create an SSIS package and use a Data Flow Task to save the XML to an XML file, meaning you're logging all the exports made. A script task, following the success of the Data Flow Task, can then do ...


0

Mirroring isn't dead, and that concern isn't a very good reason to move away from it. As of SQL Server 2014 it's still there, so there's no cause for concern until you start to look at moving off of 2014 to a newer version. With 2014 only being 9 days old, that should be a while. If you still want to see your other high availability options, look at the ...


0

This is my final working solution. I am querying MSdistribution_history table to look for comment like 'Delivered snapshot%' I suppose there must be more elegant way to check it. DECLARE @TIMEOUT DATETIME SET @TIMEOUT = DATEADD(MINUTE, 3, GETDATE()) --3 minutes for timeout WHILE NOT EXISTS ( SELECT NULL FROM ...


0

This turned out not to be an error with the backup, but with the restore command that I was using. RESTORE verifyonly FROM DISK = @backupFile WITH FILE=1, STATS=5, MAXTRANSFERSIZE=4194304 returned the error "Damage to the backup set was detected" some whereas RESTORE verifyonly FROM DISK = @backupFile WITH FILE=1, STATS=5 returned "The backup ...


1

It depends on the commodity of the query. Being extra specific should never hurt in this instance, but for something that simple the query planner in SQL Server will be bright enough to imply either o.OrderID = @xorderid or od.OrderID = @xorderid from the other because of o.OrderID = od.OrderID so you should not need to specify both. Of course other ...


1

I am not sure if there is a better way but something simple like this should do it. If you run this query on the Publisher, it will compare the tables and will return you the difference in tables. The Publisher needs to be linked to the Subscriber. -- Publisher select t.name from sys.tables t where is_published = 1 except -- Subscriber select t.name from ...


2

The best way to determine this is to simply look at the execution plans. Typically the optimizer will "condense" a lot of the operations in a query down. For example, if you include "WHERE 1=0" in your query it will see that, know it to be false, and do as little work as possible to return an empty set. In fact, in this case, it does 0 I/O operations as far ...


0

There is also the Upgrade Advisor which will help you identify anything in your DB which might cause problems when changing the compatibility level (basically a semi-automated way of doing what @steoleary has suggested which reduces the chances of you missing anything).


1

If the source is insert-only give it an IDENTITY column. When you do your data transfer you log the highest value written across. During the next transfer you need only query for values greater than that logged during the previous transfer. We do this for transferring log records to a data warehouse. For updatable rows add a "dirty" flag. It will have ...


0

I pulled the oldest full backup I could find and was able to restore that over the Restoring db using: RESTORE DATABASE [ZOMBIEDB] FROM DISK = N'D:\ZOMBIEDB.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 5 I was then able to delete the restored database.


0

Figured it out shortly after posting. Although SSIS doesn't allow you to delete the error columns in the error output of the first insert, it does allow you to change the names. I was able to modify the names of these columns and then the Union does not throw any errors as the names are not in conflict anymore.


0

I had to do it the other way. I created an empty DB and used import wizard to import all the data and the schemas. This worked without any error. But I still don't know why the Copy Wizard didn't work.


2

No, there isn't any. Any sort of 'last updated at' tracking would run into a severe performance problem as all updates, from all transactions, would attempt to update the one record tracking the 'last updated at'. This would effectively mean only one transaction can update the table at any moment, and all other transactions have to wait for the first one to ...


2

Use following query if you want to count the number of occurrence of the string 'first' in the TextColumn each row. SELECT (LEN(TextColumn) - LEN(REPLACE(TextColumn, 'first', '')))/LEN('first') as NumOfOccurrence, TextColumn FROM dbo.SampleTable; Update: To cover the trailing space case (as caught by Twinkles in the following comment), I modified the ...


0

I just want to add some useful links related to SQL Failover Cluster Good introduction to Failover As you are working on MS Azure MS Azure Cloud Storage options Although @spaghettidba mentioned this is possible I'm not sure how to setup SAN storage on MS Azure so you have to check this by yourself.


0

"We made a full backup and restored it on the new server. The server specification are the same for both: X5650 @ 2.67GHz (4procs) 16 GB RAM" You bought a new server with the exact same specs as the old one? Or is this a VM? Including all the points Kin made above, you need to install SP1 up through the latest Cumulative Update. There are many ...


5

Look into BCP or BULK INSERT, both of which can share format files. Quick math based on pure guesses, since no hard details are given: 13 numeric columns, pretend they're 6 bytes each on average = 13*6=78 bytes. 5 VARCHAR columns, pretend they're an average of 300 bytes each based on the "largest" being VARCHAR(1000) per the original question = 5*300 = ...


0

Replication is a data distribution technology, it was not meant for HA. That said, I would use a cluster. Standard edition supports 2-nodes clusters.


1

This behaviour is a security feature to make hacking more difficult, specifically because there is no variation in the output depending on the input. (Aside from exposing the fact there is a logon trigger active.) If a password is mistyped, the system doesn't respond with an error saying "the password is wrong" or even whether the login name specified ...


6

Yes, you can do this with a COMPUTED column, It's a workaround really as the column has to be PERSISTED to be used for the foreign key constraint so it consumes storage space: CREATE TABLE dbo.OtherTable ( ID INT PRIMARY KEY IDENTITY(1,1), Blah VARCHAR(100), FieldName AS CAST('This' AS VARCHAR(100)) PERSISTED NOT NULL ) ; ALTER TABLE ...


3

The sub query in the second query it's what's killing you that requires that the sub query needs to be run once per row that's returned by the record set. If you look at the plan it'll tell you how many times each operator is executed. I'll bet that you'll see a high number of executions on some operators of the second query. (I'm on my phone at the moment ...


4

There can be many things to address. Below is an outline of things to check before jumping on any conclusions : First, your sequence of POST restore steps will mess up all the work you did. Never shrink your database and that too especially after doing Index maintenance. Read up - Why you should not shrink your data files by Paul Randal. Below are my ...


0

Came across this issue as well. We are keeping our 2008R2 edition for this very reason. Starting with SQL Server 2012, each edition of SQL Server has two compute capacity limits: A maximum number of Sockets (Same as Physical processor or Socket or Processor package). A maximum number of cores as reported by the operating system. ...


0

The answer, it turned out, is something no one in forum land would have been able to guess at, no matter how good with SQL Server they were. It was application-level security that no one here was aware of. The CRM database which was the source of the data for insert, contains a table of system users managed on the front end of the application. The vendor ...


1

Run the installer again. Installing/Configuring SSIS and SSRS to existing SQL Server 2008 database services Run SQL Server setup program and on the “Feature Selection” page click the checkboxes associated with the features you would like to have installed. When finish, reapply any service pack the instance have applied before.


2

You could assign the current context to a cookie then revert to that cookie when you desire to.


8

By using the ROW_NUMBER() ranking function, you could achieve that. Here's an example below. The top two queries are just for data verification. I'm a firm believer that you should see what you will be deleting (and what will remain) before actually deleting data. (Verification) Rows that will be deleted ;with cte as ( select *, ...


0

I have fixed this problem in SSMSBoost add-in that I develop: http://www.ssmsboost.com/Features/ssms-add-in-sql-editor-contents-history Autorecovery information is saved for all - saved and unsaved docs. You can even navigate through editor contents history and track executed queries.


1

No, there is not really any such thing. Of course if the VMs are actively connected on a reliable pipe, a view could always coalesce the original data in the base VM with the changed data in the new VM.


3

The closest thing is a database snapshot. You take a snapshot of your database, that will act as the 'base image'. Then customers go nuts on the database and hack it to pieces. When all said and done, you revert the database back to the snapshot and voila, everything is back to its original state. See Revert a Database to a Database Snapshot. You do have ...


0

You can try to use some third party tools for transaction log reading, ApexSQL, Idera, RedGate. You can also try to determinate by using fn_dblog function, with all that executions you should be able to easily see the problematic statement: SELECT [Current LSN], Operation, Context, [Transaction ID], [Begin time] FROM sys.fn_dblog (NULL, NULL)


7

DECLARE @sql NVARCHAR(MAX) = N''; SELECT @sql += [sql] FROM ( SELECT [sql] = N' ALTER TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ' ALTER COLUMN ' + c.name + ' VARCHAR(50);' FROM sys.columns AS c INNER JOIN sys.tables AS t ON c.[object_id] = t.[object_id] INNER JOIN sys.schemas AS s ON t.[schema_id] = s.[schema_id] WHERE ...


6

Here is a query that does a few things: use YourDatabase; go select object_name = object_name(c.object_id), column_name = c.name, type_name = t.name, c.max_length, alter_table_ddl = 'alter table ' + quotename(object_schema_name(c.object_id)) + '.' + quotename(object_name(c.object_id)) + ' ...



Top 50 recent answers are included