Queue table processing in SQL Server(www.adathedev.co.uk)

submitted by AdaTheDev(123) 2 years, 2 months ago

The scenario is you queue up records in a database table, each representing a piece of work needing to be done. You then want to have processes that periodically poll this table to pick up the next item of work from the queue and process them. read more...

add a comment |category: Database |Views: 139

tags: SQLServer queue Database SQL another

Changing primary key index structure(www.adathedev.co.uk)

submitted by AdaTheDev(123) 2 years, 2 months ago

Changing the structure of a primary key constraint index from nonclustered to clustered (or from clustered to nonclustered) is not necessarily as straight forward as it first seems. The process of changing it over, involves the constraint being dropped and then recreated. This could potentially cause a problem if you're making the change on a table whilst there could be activity against it. read more...

add a comment |category: Database |Views: 3

tags: SQLServer SQL Database another

Autogenerated SQL constraint names - why to avoid them(www.adathedev.co.uk)

submitted by AdaTheDev(123) 2 years, 3 months ago

Autogenerated constraint names in SQL Server - are they convenient, or are they a hindrance? For me, they are a hindrance. Why should I care that SQL Server autogenerates a constraint name when I don't explicitly specify one? Why does it matter if it names my PRIMARY KEY constraint "PK__Constrai__3214EC271FCDBCEB", or my DEFAULT constraint "DF__Constraint__ColumnA__21B6055D"? This article explains reasons why you should specify a name yourself for each constraint. read more...

add a comment |category: Database |Views: 7

tags: SQLServer Database another

IF EXISTS(Select null from table) vs IF EXISTS(Select 1 from table)(blog.sqlauthority.com)

submitted by pinaldave(9662) 2 years, 3 months ago

The most interesting comment conversation is among Divya, Brian and Marko. Please read the comments of Marko for sure. It is the comment, which has triggered this post. What is your opinion and experience about this post? read more...

add a comment |category: Database |Views: 550

tags: SQLAuthority pinaldave hi Database SQL another

Optimising date filtered SQL queries(www.adathedev.co.uk)

submitted by AdaTheDev(123) 2 years, 3 months ago

How you structure your SQL queries is very important and choosing the wrong approach can have big effects on the performance of the query. This article shows the difference between an optimal query and a sub-optimal query resulting from subtle changes to the approach taken. read more...

add a comment |category: Database |Views: 10

tags: SQL SQLServer Database Performance another

Stored Procedure Optimization Tips – Best Practices(blog.sqlauthority.com)

submitted by pinaldave(9662) 2 years, 3 months ago

Key Points discussed in the articles are : 1. Include SET NOCOUNT ON statement 2. Use schema name with object name 3. Do not use the prefix “sp_” in the stored procedure name 4. Use IF EXISTS (SELECT 1) instead of (SELECT *) 5. Try to avoid using SQL Server cursors whenever possible 6. Keep the Transaction as short as possible 7. Use TRY-Catch for error handling read more...

2 comments |category: Database |Views: 779

tags: SQLAuthority pinaldave SQLServer Database SQL another

Reason of Compute Scalar before Stream Aggregate(blog.sqlauthority.com)

submitted by pinaldave(9662) 2 years, 3 months ago

Running the following query will give you Stream Aggregate Operator in Execution Plan. To turn on Execution Plan, press CTRL + M. I would like to know your feedback about Stream Aggregate Operation. read more...

add a comment |category: Database |Views: 162

tags: SQLAuthority pinaldave SQLServer Database SQL another

Query Optimization - Interesting Observation of Compute Scalar before (dotnetkicks.com)

submitted by pinaldave(9662) 2 years, 3 months ago

SQL SERVER – Stream Aggregate Showplan Operator – Reason of Compute Scalar before Stream Aggregate February 5, 2010 by pinaldave | Edit I keep a check on the questions received from my readers; when any question crosses my threshold, I surely try to blog about it online. Stream Aggregate is a quite commonly encountered showplan operator. I have often found it in very simple COUNT(*) operation’s execution plan. If you like to read an official note on the subject, you can read the same on Book Online over here. The Stream Aggregate operator groups rows by one or more columns and then calculates one or more aggregate expressions returned by the query. Click more for further story. read more...

add a comment |category: Database |Views: 277

tags: SQLAuthority pinaldave SQLServer Database SQL another

How to Convert Hex to Decimal - A simple puzzle (blog.sqlauthority.com)

submitted by pinaldave(9662) 2 years, 3 months ago

How to Convert Hex to Decimal - A simple puzzle? Let us see if you have answer to this simple puzzle. read more...

1 comment |category: Database |Views: 483

tags: SQLAuthority pinaldave SQLServer Database SQL another

SQL SERVER – Find Statistics Update Date – Update Statistics(blog.sqlauthority.com)

submitted by pinaldave(9662) 2 years, 4 months ago

Statistics are one of the most important factors of a database as it contains information about how data is distributed in the database objects (tables, indexes etc). It is quite common to listen people talking about not optimal plan and expired statistics. Quite often I have heard the suggestion to update the statistics if query is not optimal. Please note that there are many other factors for query to not perform well; expired statistics are one of them for sure. read more...

3 comments |category: Database |Views: 193

tags: SQLAuthority pinaldave SQLServer Database SQL another

Unique Nonclustered Index Creation with - A Transactional Behavior(blog.sqlauthority.com)

submitted by pinaldave(9662) 2 years, 4 months ago

When we have situation where we are dealing with INSERT and TRANSACTION, we can see this feature in action. Let us consider an example where we have two tables. One table has all the data and the second table has partial data. If you want to insert all the data from the first table to the second table and insert all nonduplicate values, you can use this feature. read more...

add a comment |category: Database |Views: 62

tags: SQLAuthority pinaldave SQLServer Database SQL another

Fastest Way - Comma Separated Values (CSV) from Table Column(blog.sqlauthority.com)

submitted by pinaldave(9662) 2 years, 4 months ago

Learn the Fastest Way to get Comma Separated Values (CSV) from Table Column read more...

3 comments |category: Database |Views: 461

tags: XML SQLAuthority pinaldave Database SQL another

Service Broker Basics(sqlblog.com)

submitted by Jemm(9604) 2 years, 4 months ago

"I'm currently implementing a Service Broker solution at a client site, and it's been an interesting challenge, because there's not a lot of information out there to help guide you through the process. Here I'd like to walk you through the basics." read more...

add a comment |category: Database |Views: 24

tags: ServiceBroker SQLServer Database another

Differences in Vulnerability between Oracle and SQL Server(blog.sqlauthority.com)

submitted by pinaldave(9662) 2 years, 4 months ago

In the IT world, but not among experienced DBAs, there has been a long-standing myth that the Oracle database platform is more stable and more secure than SQL Server from Microsoft. This is due to a variety of reasons; but in my opinion, the main ones are listed below: Read the whole article read more...

3 comments |category: Database |Views: 478

tags: Oracle SQLAuthority pinaldave Database SQL another

SSMS tab grouping with Tabs Studio(tabsstudio.wordpress.com)

submitted by SergeyVlasov(173) 2 years, 5 months ago

SQL Server Management Studio uses quite different scheme for tab names from Visual Studio. I’ve significantly improved SSMS support in the next Tabs Studio version... read more...

add a comment |category: Database |Views: 12

tags: SQLServer SSMS Database SQL another

Difference TempTable and Table Variable (blog.sqlauthority.com)

submitted by pinaldave(9662) 2 years, 5 months ago

A Myth Busted. Recently, I have been conducting many training sessions at a leading technology company in India. During the discussion of temp table and table variable, I quite commonly hear that Table Variables are stored in memory and Temp Tables are stored in TempDB. I would like to bust this misconception by suggesting following: Temp Table and Table Variable — both are created in TempDB and not in memory. read more...

6 comments |category: Database |Views: 392

tags: SQLAuthority pinaldave SQLServer Database SQL another