A community of more than 1,600,000 database professionals and growing

Featured Script

Database schema wise size detail

The Voice of the DBA

Dynamic SQL Crossword

See if you can solve this crossword puzzle based on Dynamic SQL.

Dynamic SQL Crossword

Across

2. CHAR(10)

3. Without human intervention.

6. Create.

10. Making it easier to read.

12. Single or double.

13. Trying to do too much at once.

17. a.b.______.d

18. An invitation to Bobby.

19. Not quite a colon.

20. Instructions.

Down

1. Auto escape.

4. Get the data back out.

5. Looks like a variable.

7. Better for running Dynamic SQL than EXEC.

8. SQL inception.

9. Twice as many.

11. Little Bobble Tables.

14. Small rope.

15. Number to character.

16. Write it all out.

19. Not dynamic.

Kenneth Fisher from SQLServerCentral.com

Join the debate, and respond to today's editorial on the forums

SQL in the City Summits - New York, London & Chicago

This October Redgate are inviting those interested in learning how their business can benefit from implementing Compliant Database DevOps to attend one of our SQL in the City Summits. If you manage SQL Server databases, or manage a team of people who do so, Redgate’s SQL in the City Summit is the conference for you. Find out who’s presenting and register for a Summit near you today.
Register now

The industry standard for comparing and deploying SQL Server database schemas

Trusted by 71% of Fortune 100 companies, SQL Compare is the fastest way to compare changes, and create and deploy error-free scripts in minutes. Plus you can easily find and fix errors caused by database differences. Download your free trial

Featured Contents

Stairway to Columnstore Indexes Level 10: Optimizing Queries For Batch Mode (Part 1)

Hugo Kornelis from SQLServerCentral.com

In this level, Hugo Kornelis looks at how to rewrite your queries to best take advantage of batch mode. More »

Free eBook: Defensive Database Programming

Press Release from Redgate

Resilient T-SQL code is code that is designed to last, and to be safely reused by others. The goal of defensive database programming, the goal of this book, is to help you to produce resilient T-SQL code that robustly and gracefully handles cases of unintended use, and is resilient to common changes to the database environment. More »

SQL Server Index Tutorial Overview

Additional Articles from MSSQLTips.com

Although there are many different things that can be done to improve the performance of a database system, creating an index is the main tool in every DBA's toolbox that is used when we are trying to improve the performance of a query. More »

From the SQLServerCentral Blogs - Querying Data in Temporal Tables

SQL_Girl from SQLServerCentral Blogs

Following on from my previous post about modifying data in a temporal table, I will be looking into querying the... More »

From the SQLServerCentral Blogs - Are your backup files where they say they are?

david.fowler 42596 from SQLServerCentral Blogs

I was having a cleanup of some old scripts the other day when I stumbled upon a script I wrote... More »

Question of the Day

Today's Question (by Steve Jones):

I want to detect in my python source file whether this file is being run as a script or being imported into a REPL or other module. What variable allows me to detect if I've run the file as a script?

Think you know the answer? Click here, and find out if you are right.

We keep track of your score to give you bragging rights against your peers.
This question is worth 1 point in this category: Python.

We'd love to give you credit for your own question and answer.
To submit a QOTD, simply log in to the Contribution Center.

Design and configure SQL Server instances and databases in support of high-throughput applications that are mission-critical and provide consistent response times in the face of variations in user numbers and query volumes. Learn to configure SQL Server and design your databases to support a given instance and workload.

Pick up your copy of this great book today at Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Steve Jones):

In SQL Server 2017, I have this table:

 CREATE TABLE OrderHeader ( OrderKey INT IDENTITY(1, 1) NOT NULL CONSTRAINT OrderHeaderPK PRIMARY KEY , OrderDate DATE , OrderTotal NUMERIC(10, 2) , CustomerKey INT );

I decide to insert some values with this code:

 INSERT dbo.OrderHeader ( OrderDate , OrderTotal , CustomerKey ) <***> VALUES ('2018-08-01', 500, 1), ('2018-08-02', 300, 2)

What should I replace the <***> with to get the identity values returned to the calling application?

Answer: OUTPUT Inserted.OrderKey

Explanation:

The OUTPUT clause will return the values from the inserted table, which will contain the identity values.

Ref: The OUTPUT Clause for INSERT and DELETE Statements - http://www.sqlservercentral.com/articles/T-SQL/156204/

OUTPUT - click here

» Discuss this question and answer on the forums

Featured Script

Database schema wise size detail

Subhash Chandra from SQLServerCentral.com

With help of this script, Databases schema wise size can be explored.

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2017 : SQL Server 2017 - Administration

Jobs running indefinitely - Hi, I have an sql server 2017 enterprise and sometimes (not always), when it runs a job (the jobs he...

SQL Server 2017 : SQL Server 2017 - Development

SQL Database Documentation - We have a situation where the Lead SQL Developer is leaving the company and he says there's no documentation on...

parsing log data - hi everyone, i have case to parsing the log data like this on sql server { "Name": "David", "Gender": "Male", "Address":...

formatting T-SQL - Hi, I found one formatter online, but wanted to know if you use any tools to format the T-SQL code , before...

SQL Server 2016 : SQL Server 2016 - Development and T-SQL

Partioning Huge Table (650GB about) - Hi all, in my data warehouse (SQL Server 2016 Std) I need to maintain 36 (!!) versions of a table in order...

Stored Procedure Analysis and Design - I’m pretty new to Sql and I’ve been given a set of reports I need to develop. Specifically, I’ve got...

Translation to valid TSQL - I've been trying for the past hour to nest a case in another case. How can I say this exactly?

Best way to SELECT with one Case Expression - I have three seperate tables that can be joined to eachother. For examples sake We'll call them three tables MainTableOne,...

SQL Server 2014 : Development - SQL Server 2014

Entity Framework and SQL - Hi Experts, Our dev team is using Entity framework and now all the DB calls are written in code. Since there...

SQL Server 2012 : SQL 2012 - General

Move some tables onto a different database. - Hi Y'all I'd like some advice. My problem is there are a pair of audit log tables (AuditLog and AuditLogDetail) that...

Rows into dynamic columns - I have a table with name and Amount Name Amount rita 100.00 rick 100.00 sam 150.00 harry 200.00 heather 290.00 James 200.00 I was wondering if I can do a dynamic pivot on Am

BCP Error - Hi All: I'm trying to import data using bcp however, my I keep getting the below message. I have checked the...

SQL Server 2012 : SQL Server 2012 - T-SQL

Adding Indexes to Tables - Hello, How can I determine if an Index should be added to a table? Should this be done by running each...

SQL Server 2008 : SQL Server 2008 - General

Archiving when 2 tables are sharing the same partition scheme and same partition function - Hi everyone, Need some help in confirming/understanding the behavior of my archiving process. Table1 AND Table2 are using partition scheme PSD01. Both...

Delete and shrink data file - Hi guys I just deleted a lot of rows in a table to gain more disk space . and did shrink...

SQL Server 2008 : T-SQL (SS2K8)

please help !! Dynamic SQL - Hi experts Please I REALLY NEED YOUR HELP !! I want to create a dynamic sql in a cursor. I want to replace...

Standard Deviation (variance) using columns in same row. - Is there a way to get a variance from two columns in the same row? I have a row that...

Reporting Services : Reporting Services

Report Columns not summing correctly. - Hello, I am using report builder 3 to build a report. The report is based on three tables and two datasets. The...

Reporting Services : SSRS 2016

Problem with interactive sort when dynamically changing group - Hi folks, I have row groups as such: ParentUnit -> Unit -> Details I created a parameter to change the ParentUnit Row Group grouping from...

Data Warehousing : Integration Services

Need help to extract unstructured excel data to sql server - I have 12 different Excel files which are having multiple tabs in every file and data in really unstructured. What's...

This email has been sent to newsletter@newslettercollector.com. To be removed from this list, please click here.
If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com.

This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.