Tag Info

Hot answers tagged

10

You can cycle the database by doing: alter database YourDatabase set offline with rollback immediate; -- this line will rollback ongoing transactions go And then to bring the database back online you can do: alter database YourDatabase set online; go This is a way to "restart" the database itself directly within SQL Server, without bouncing either the ...


9

I don't believe that result was incorrect at all, your query is ambiguous. Semantically your subquery enumerates all rows and then you pick the top 25 rows without specifying an order by. That means that SQL server is at liberty to return any rows it feels like and those rows do not have to be the rows enumerated 1 to 25. It can be just any random rows and ...


7

The 64GB limit (and 128GB in 2014) applies only to buffer pool. The KB article "Memory configuration and sizing considerations in SQL Server 2012" says: Starting with SQL Server 2012, these memory limits are enforced only for the database cache (buffer pool). And as we all know, max server memory also does not control all of SQL Server's memory. The ...


7

This isn't as easy to achieve as you might think. One way is to create a new user-defined database role, give all the permissions needed to that role, then add users to the new role. This at least makes it easier to give users (or other user-defined roles) this set of permissions in future. The following steps are a good start: -- The user-defined role ...


6

I think the script in question is sp_BlitzIndex. The script lists tables that have more than 3 columns, and the number of non-nullable columns is 1 or 0. It doesn't mean that those tables are bad-- but if you see a lot of rows for this, it's just there to raise the question: Is it valid for those columns to all really contain nulls? When tables are ...


6

Whether or not you are using SQLCMD mode, you can always avoid the need for a GO (which enforces batch separation) by wrapping the calls in an EXEC(). For example: IF ($(EnableDBMail) = 1 ) BEGIN EXEC('sp_configure ''show advanced options'', 1;'); EXEC('RECONFIGURE;'); EXEC('sp_configure ''Database Mail XPs'', 1;'); EXEC('RECONFIGURE;'); END; ...


5

To check for a specific effective permission on a securable, use HAS_PERMS_BY_NAME. For example, to show if the current security context has SELECT permission on the Production.Product table in the AdventureWorks sample database: SELECT HAS_PERMS_BY_NAME ( N'Production.Product', N'OBJECT', N'SELECT' ); Related: To ...


5

You can use a Logon Trigger for that. CREATE TRIGGER TR_check_ip_address ON ALL SERVER FOR LOGON AS BEGIN DECLARE @ip_addr varchar(48) SELECT @ip_addr = client_net_address FROM sys.dm_exec_connections WHERE session_id = @@SPID IF ORIGINAL_LOGIN() = 'bob' AND @ip_addr <> '127.0.0.1' ROLLBACK; END If you try to ...


4

SYNCHRONIZED state only ensures that the writes are hardened by the secondary (log written to disk). It says nothing about them being applied (data changed). can I expect consistent results to be returned from both replicas, every time? Yes. The reads are consistent, always. But keep in mind that in relational parlance consistency (ACID) has a ...


4

A multiple-column foreign key constraint must be declared at the table level, not on each column: CREATE TABLE dbo.t1 ( id int NOT NULL, name nchar(10) NOT NULL, CONSTRAINT PK_dbo_t1_id_name PRIMARY KEY CLUSTERED (id,name) ); CREATE TABLE dbo.t2 ( id int NOT NULL, name nchar(10) NOT NULL, CONSTRAINT PK_dbo_t2_id_name ...


4

You should consider refactoring to use the new paging clause in SQL Server 2012, ( OFFSET...FETCH ), rather than that old-fashioned 'roll your own paging' eg SELECT ... FROM TableC WHERE AccountID = @P3 AND Processed = @P4 AND Deleted = 0 AND Source = @P5 ORDER BY ProcessedDate DESC OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY It's potentially ...


4

I am unsure if its necessary to add the TABLOCK table hint to an empty temporary table, defined with a clustered index in order to get minimal logging. No. Local temporary tables (#temp) are private to the creating session, so a table lock hint is not required. A table lock hint would be required for a global temporary table (##temp) or a regular table ...


4

I imagine you'll probably need a secondary ordering column, as Col1 is not a unique value, however this should get you started. WITH sampledata AS ( SELECT ROW_NUMBER() OVER(ORDER BY Col1) as row_num, ID, Col0, Col1, Col2 FROM Table1 ) SELECT row_num FROM sampledata WHERE ID = 263;


4

Thomas Cleberg is correct. The first row you insert into bit2 shows up in DBCC page as 10000500 ff080000 63 This breaks down as follows +-------+-----------------+--------+-------------------------------------------------------+ | Bytes | Hex (LSB order) | Hex | Comments | ...


3

As you already know there is No general formula to calculate max server memory you can do some quick maths and reach to a value but still you would need help of Perfmon counters at last to monitor memory usage and change accordingly. I know below general formula and I use it as well. I learned this formula from This Link For SQL Server 2005 to 2008 R2 ...


3

Per Aaron Bertrand's answer (Quick look at how much RAM is allocated to SQL Server?). Task Manager shouldn't be used to ascertain how much memory SQL is actually using. I prefer to use the following query (Again, thanks Aaron): SELECT object_name, cntr_value FROM sys.dm_os_performance_counters WHERE counter_name IN ('Total Server Memory (KB)', 'Target ...


3

Try this select a.Date, a.Foo, b.Bar from TableA a LEFT JOIN TableB b ON a.Date = b.Date UNION select b.Date, a.Foo, b.Bar from TableB b LEFT JOIN TableA a ON a.Date = b.Date Strictly speaking, the query above does not quite have the same semantics as the original full join, with respect to duplicates. A correct transformation of full join is to a left ...


3

You can achieve this using logon trigger as following USE master GO -- Create table to hold valid IP values CREATE TABLE ValidIPAddress (IP NVARCHAR(15) CONSTRAINT PK_ValidAddress PRIMARY KEY) -- Declare local machine as valid one INSERT INTO ValidIPAddress SELECT '<local machine>' -- Create Logon Trigger to stop logins from invalid IPs CREATE ...


3

Use script instead to check status of currently running job. SELECT job.name, job.job_id, job.originating_server, activity.run_requested_date, DATEDIFF(MINUTE, activity.run_requested_date, GETDATE()) as Elapsed, case when activity.last_executed_step_id is null then 'Step 1 executing' else ...


3

Either of these models look reasonable. The NoticeMasterFile intersection table is not strictly needed since you can get the information through either model. The question is: How do you intend to use the NoticeMasterFile information? Perhaps this change would support some future plans that you have. Of course, the problem is that looking at a data model ...


3

The optimizer makes choices based on costing estimates. The cost model is generic, and may not always choose optimal plans for your particular hardware, and its assumptions may not always be valid for your circumstances. In this case, the optimizer assesses a hash join as the cheaper option over nested loops when the estimated number of rows to be joined is ...


3

I did some searching myself, since I noticed that you're running this query in SQLCMD mode, and I didn't know if it would be any different. Did a quick Google search, and found: http://stackoverflow.com/questions/3988199/how-to-use-sp-configure-in-another-stored-procedure Basically, "GO" is just the batch separator within SSMS, and you should be able to ...


3

Identical tables, identical columns, identical records Not entirely true. At first they were different, and row 1 was added to [mybit2] when they were different. Your second image, the DBCC PAGE output for [mybit2], clearly indicates this fact. Why different record sizes? Simply put: you changed the table definition and didn't rebuild it. Some ...


3

No this isn't possible. A permanent TVF or view isn't an option due to the reference to #MyTempTable I've seen a connect item request for temporary views and agree sometimes they would be useful. This was closed as duplicate of one requesting Module-level table expressions. You might be able to rewrite as SELECT A, B, C, CASE ...


3

Strictly speaking (T-SQL subroutine): No. Technically speaking (a means to abstract formulas to be defined once): Yes. Pragmatically speaking: It depends :). Here are the issues that are currently impeding you regarding restrictions on T-SQL functions: They cannot be declared dynamically They cannot access temporary tables They cannot be aggregate ...


3

You need CROSS APPLY not join. The definition of table expressions involved in joins must be stable. I.e. They can't be correlated such that the table expression means something different dependant on the value of a row in another table. select f.ID, f.Desc, u.Field1, u.Field2 from Foo f Cross apply ut_FooFunc(f.ID, 1) u where f.SomeCriterion = ...


2

The spatial index, as with other objects, have their own script <object> as menus and need to be done separately. This can be done to the clipboard and pasted to the query window generated by the script table as command. I can't confirm at the moment, but I suspect that the primary key and other constraints come out in the script table as options ...


2

trace flag 2505 can be used. Wrote a blog post with experiment http://sqlserver-help.com/2014/12/09/help-how-to-suppress-dbcc-traceonoff-messages-in-the-error-log


2

No, you do not have to buy a VS licence to use SSDT or SSDT-BI, you have the licence for the services (IS, AS, RS) if you have a SQL Server licence. SSDT and SSDT-BI are just tools to use those services. You can freely download SSDT and SSDT-BI from Microsoft: SSDT: http://msdn.microsoft.com/en-us/data/hh297027 (for SQL Server Database projects) SSDT-BI: ...


2

Views do not have datatypes; they reference columns in tables that do have datatypes. You can do an explicit CONVERT() in a View, but just like any query, it will return the underlying type. Now, doing what amounts to a "default" (especially inline with your request to use DATETIME2 even if someone tries to use another type) is a bit complicated and shown ...



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