Tag Info

Hot answers tagged

10

DECLARE @ranges TABLE ( Label VARCHAR(20), LBound INT, UBound INT ); INSERT @ranges(Label,LBound,UBound) VALUES(...), ('60-65', 60, 65), ('70-75', 70, 75), (...); SELECT ValueRange = r.Label, [Count] = COUNT(o.key) FROM @ranges AS r LEFT OUTER JOIN dbo.othertable AS o ON o.value1 > r.LBound AND o.value1 <= r.UBound -- AND other ...


7

Under the covers sp_indexoption just does an ALTER INDEX as well. My recommendation would be to forget about sp_indexoption and just utilize ALTER INDEX: alter index IX_YourIndex on dbo.YourTable set ( allow_row_locks = on ); go As noted in the BOL reference for ALTER INDEX, when you specify just the set options: Specifies index options without ...


7

This error indicates that Management Studio is running out of memory, not the SQL Server service. Even if you installed 64 bit SQL Server, the SQL Server Management Studio executable is a 32 bit application. This is likely caused by the size of the result set that you are returning to Management Studio. Are you executing something like SELECT * FROM ...


6

You could do this by running something like declare @newid int; select @newid = IDENT_CURRENT('YourTable') + 2000; dbcc checkident('YourTable', reseed, @newid); This would reseed your table identity to be 2000 higher than it currently is and seems like it should resolve what you are looking to do.


6

I've always been a fan of a dynamic sql approach for this type of problem. I find it provides the optimal balance between complexity versus quality query plan. In the following code, I define a base query which does whatever it would need to do and then only add in the filters if the provided parameter is not null. CREATE PROCEDURE [dbo].[GetData] ( ...


6

SELECT * FROM Table1 WHERE Table1.URL LIKE '%' + @Parameter1 + '%' AND Table1.ID = @Parameter2 AND ( @Parameter3 is null or Table1.ID2 = @Parameter3 ); Take a look at the above example. If you change your AND clause to a nested OR clause specifying your initial expression as well as @Parameter3 is null. That will then demand that the nested ...


6

I assumed it would follow my hint, and maybe error out at execution time if I wound up with some bad data and the index was missing some needed values. The query optimizer will only use a filtered index in a query plan if it can guarantee (within its reasoning framework) that all possible matches can be served from the index. This is by design, to avoid ...


6

Mike is right that the error message you're receiving is from the Management Studio application itself, and not from SQL Server. It is the memory on your local workstation that has been exhausted, likely due to trying to pull 16 billion rows into the client application (rendering that much data in a grid is quite expensive in terms of memory, so try to limit ...


5

The merge join works like a zipper - if you don't care about order, SQL Server knows that it can sort the input in any way it wants, and not have to worry about re-ordering anything. When you add the order by, in this case a merge join is no longer the best choice, because materializing and sorting the first CTE twice in the order defined by the ROW_NUMBER() ...


5

Use SSMS to generate scripts for the entire database DDL. Use the script to create a new, empty database. Use BCP to export the data from all tables. Use BCP to import that data into the new database. MAKE SURE YOU BACKUP THE ORIGINAL DATABASE before DROPing it (if you in fact intend to drop it at all). MAKE SURE YOU TEST RESTORE THE BACKUP.


4

The FlushCache message in the error log is caused by checkpoint logging, and in this case by a long checkpoint (which is defined as a checkpoint that is taking longer than the recovery interval). Whether it's logged or not, the behavior is different in pre-2012 and 2012+. Before SQL Server 2012, to get checkpoint logging you'd have to turn on a trace flag ...


4

There is no "BEST" way to add a column. There is only one way - using the T-SQL command. What do you think happens when you use SSMS table design? it's just a way to simplify tasks for those who don't know the syntax. When you click OK, SSMS generates the ALTER TABLE statement and executes it for you.


3

One thing to remember is that an ID does not have to be generated by the IDENTITY property. Using IDENTITY has challenges, since as soon as rows are moved from the standby server to the current server (using IDENTITY_INSERT of course) with an ID value higher than the current identity value it becomes the new current identity value. Which can complicate ...


3

I would not recommend SQL Server Management Studio's Table Designer, since in my experience, it will create a new table, move all the data, then drop the old table and rename the new table. The syntax for renaming a column is not to ALTER TABLE, but to use sp_rename: EXEC sp_rename 'table_name.old', 'new_name', 'COLUMN'; ...


3

You can change this behavior with the ALTER SERVER CONFIGURATION statement. The flexibility to modify all of the necessary parameters (path, max files, max file size) should give you enough control to get it to where you want. See the bottom of the referenced link above for samples/examples. Here is one copied from the source: ALTER SERVER CONFIGURATION ...


3

To make the scripting task alot easier, you can also use 3rd party tools. MSVisualStudio has both a Schema and Data comparison toolset. I personnally prefer RedGate's SQLCompare tool. I also use theses kind of tools all the time to ease DEV->QA->PROD deployments. Once the schema is in place, you do a simple "Data import Wizard" to copy the data over, as ...


3

A simple approach if all you care about is column names and merely identifying any tables that have the same naming scheme but have columns that don't exist in the "model" or "ideal" table (also, I kept this simple, assuming all tables are in dbo): USE tempdb; GO CREATE TABLE dbo.repo_2012_08_04(a INT, b INT); -- model CREATE TABLE dbo.repo_2012_08_05(a ...


3

Yes you can! Your first SQL Sever (2012) will most likely be the default instance, any subsequent installation of SQL Server (any version) must be a named instance. As long as your machine has the resources, you can install multiple instances of SQL Server.


3

Variable colors share settings with table, object and other names. So you can change the text color using Identifier: Just note that it will also affect table names, column names etc. For the highlighted color of identifiers and variables, use Selected Text. The highlights will use a slightly muted version of that color. And no, these are not ...


2

This is because of the changes affected when you modify the compatibility level of any database within SQL Server. This was an affect seen starting at SQL Server 2008 I believe, at least it shows up in documentation since then. As stated here on MSDN for the ALTER DATABASE SET COMPATIBILITY_LEVEL: Compatibility Levels and Stored Procedures When a ...


2

Short answer: Just leave the log file as big as it typically needs to be, and stop worrying about it. Longer answer: First, why do you want to keep shrinking the file? If it's just going to grow again (and keep in mind that shrink and grow operations are expensive, especially for the log), then what did you gain? What did you use all that freed up space ...


2

You could use the CHECKSUM() and CHECKSUM_AGG() functions. Though not perfect (checksums aren't, by definition), you'll get a pretty nice resultset to look at, where you can quickly spot the differences. SELECT tbl.[name], CHECKSUM_AGG(CHECKSUM(col.[name], typ.[name], col.max_length, col.[precision], col.[scale])) AS ...


2

.mdf and .ldf files do not show the last modification date in the way you think it does. The file-system date reflects the last time the instance started the database. Modifications to data continue while the instance has the database open, without the instance actually updating the file-system dates for the files in use by the database. Can you add the ...


2

The client connection role for secondary replicas determines whether your database is always available, or only available to read-only connections, or not available at all. If you set the database to read-only connections, then your connection string has to include the parameter ApplicationIntent=ReadOnly as defined in that Books Online page. When you try ...


2

To use a filtered index it wants to see the predicate there, or one that matches very closely. So explicitly saying AND ins.ParentID IS NULL is going to be useful. Now, you should generally include the columns you're filtering on in the index itself because of a QO quirk. If the predicate in the query matches exactly the one in the index, and you're not ...


2

On Checking for #tbl It can't hurt to check for the table's existence (and drop it if it exists) at the beginning of the procedure, but it depends on how you want to handle that scenario, and in most cases it's not possible for it to exist already anyway (at least if we're talking about the same #temp table as defined within that stored procedure). You ...


2

Start out with getting all the procedures from sys.procedures and then use EXEC sp_helptext on each to load the text of the procedures. Search for text to add, add if needed, load the updated text to a variable and execute dynamically.


2

I want to log all logons using a logon trigger from server A in server B. Ideally, if you are using Enterprise edition, then as Shawn mentioned, you can use SQL Server Audit. You can even set up a light weight server side trace and then define a WMI event notification - TRACE_FILE_CLOSE to automatically load the trace data into a table when it ...


2

There are a few good DMVs (system views) where you can find this info: sys.partition_functions and sys.partition_schemes can be joined to sys.partition_range_values to get all existing ranges. The number of ranges should be the number of partitions. The data_space_id of the partition scheme can be joined to that of the index or heap in sys.indexes on the ...


1

Since you are in 2012 version, here's another way to solve this gaps-and-islands problem that uses the new LAG() function: ; WITH seq AS ( SELECT SiteIDNumber, SequenceNumber, LagSequenceNumber = LAG(SequenceNumber) OVER (PARTITION BY SiteIDNumber ORDER BY SequenceNumber), ...



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