Tag Info

Hot answers tagged

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 *, ...


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)) + ' ...


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 ...


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 = ...


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 ...


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 ...


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:


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 ...


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 ...


3

You need to use a specific style when you expect to keep the same binary value when converting from a string. Otherwise SQL Server tries to encode the string the same way it would encode 'bob' or 'frank'. That said, your input string doesn't look correct - there is either a byte missing or one byte too many. This works fine if I drop the trailing E: SELECT ...


2

Does this failover cluster impose any restrictions on this backup restore or will it be a normal backup restore operation. It will be a normal backup and restore operation (provided there are no other issues with the actual backup and/or restore). The fact that you are going from a standalone instance to a failover cluster instance will not negatively ...


2

CDC relies on a SQL Server Agent job to capture information from the transaction log. You can customize the job parameters by updating msdb.dbo.cdc_jobs - there are multiple properties as described in this Books Online topic: maxtrans maxscans continuous pollinginterval That doc has some information about these parameters, as does this white paper. But ...


2

If you've dropped all the tables, you could just drop the database and re-create it. It will be created with the default sizes and growth rates as defined by the model database. These may not be ideal (I don't find the defaults particularly useful in most scenarios), but you can change them. Unless you have a lot of things set up on this database (like ...


2

There are many things that can cause gaps in an IDENTITY column (rollbacks, deletes), but in this case due to the jump I suspect it is this bug - caused by the changes to IDENTITY with the introduction of SEQUENCE: Connect # 739013 : Failover or Restart Results in Reseed of Identity So I bet that if you look in SQL Server's error logs, the rows ...


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 ...


2

During installation you were prompted to add Windows users to the SQL instance. Did you add any? Unless you added yourself as a SQL user you won't be able to connect. if you added Administrators as SQL users then you must run SSMS w/o the LUA token restriction, in other words you must right click the launch icon and select "run as Administrator" if you did ...


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 ...


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 ...


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 ...


1

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 ...


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

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 ...


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 ...


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 ...


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.


1

First off, there is no such thing as an "active/active" cluster. That is a huge misnomer, and can lead to a large amount of confusion. What you have is two failover cluster instances in a Windows failover cluster with at least two nodes. Secondly, this will not be possible if you are dealing two failover cluster instances. I wish to now have the ...


1

Neither of those should work. If it's a named instance, it should be: DATA SOURCE=ClusterName\SQLInstanceName; If it's a default instance, just: DATA SOURCE=ClusterName; You are always connecting to an instance of SQL Server, whether it is clustered or not. The cluster will know which node in the cluster is currently hosting the instance, and this ...



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