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

Featured Script

Find the position of Nth repeated char/string

The Voice of the DBA

Byte Me: Cloud Storage

Bob Lang from SQLServerCentral.com

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

How to track every change to your SQL Server database

See who’s changing your database, alongside affected objects, date, time, and reason for the change with SQL Source Control. Get a full change history in your source control system. Learn more.

Not enough hours in your day?

The SQL Toolbelt lets you reduce the time spent on SQL Server development and administration. Cambridge University developer David Spaxman, for example, says: “I’m saving 10-12 hours a week using the SQL Toolbelt.” Learn how you can double your productivity, speed up deployments and protect your data. Download a free trial.

SQL Clone – create database copies fast!

Create copies of production databases and SQL backups in seconds and save up to 99% of disk space using SQL Clone. Redgate’s new tool removes much of the time and resource needed to create and manage database copies, allowing teams to work on local environments to develop, test and diagnose issues faster. Join the beta.

Featured Contents

8 Ways to Export SQL Results To a Text File

Daniel Calbimonte from SQLServerCentral.com

This tip will show eight ways to export the results of a query to a text file. More »

Free eBook: SQL Server Backup and Restore

Press Release from Redgate

In this free eBook Shawn McGehee offers advice on query tuning, cutting stored procedures, and system process design and implementation for high availability. Discover how to perform backup and restore operations using SQL Server Management Studio (SSMS), basic T-SQL scripts and Redgate's SQL Backup tool. More »

Real-time Operational Analytics in SQL Server 2016 - Part 2

Additional Articles from Database Journal

Arshad Ali discusses the different design considerations for leveraging the Real-time Operational Analytics feature of SQL Server 2016, covering the different scenarios where it fits or does not fit, and how to get started with it for disk based tables. More »

From the SQLServerCentral Blogs - Read this before using SQL 2016 Temporal Tables

Daniel Janik from SQLServerCentral Blogs

I’ve been testing the new Temporal Tables feature over the past day to see about using it in one of... More »

Question of the Day

Today's Question (by Steve Jones):

I decide to create a new table for tracking Orders and want to have this be a Temporal table. I use this syntax:

 CREATE TABLE [dbo].[Orders]( [OrdersID] int PRIMARY KEY CLUSTERED, [Quantity] int NOT NULL, [UnitPrice] money not null, [OrderDate] datetime2 NOT NULL, [SysStartTime] datetime2(0) GENERATED ALWAYS AS ROW START NOT NULL, [SysEndTime] datetime2(0) GENERATED ALWAYS AS ROW END NOT NULL, PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime]) ) WITH (SYSTEM_VERSIONING = ON );

What will be the name of my history table?

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: Temporal Tables.

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

Professional Microsoft SQL Server 2014 Integration Services

Master the fundamentals of Transact-SQL—and develop your own code for querying and modifying data in Microsoft SQL Server 2016. Led by a SQL Server expert, you’ll learn the concepts behind T-SQL querying and programming, and then apply your knowledge with exercises in each chapter. Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Steve Jones):

I have a simple table, created from this code:

 CREATE TABLE [dbo].[Customers] ( [CustomerID] [int] NULL, [CustomerName] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [CustomerNotes] [varchar] (max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Active] [tinyint] NULL ) GO

I then add this index:

 CREATE NONCLUSTERED INDEX [CustomersNDX] ON [dbo].[Customers] ([CustomerID]) ON [PRIMARY] GO

What happens in sys.indexes? (choose 2)

Answer:

The first statement creates a row with index_id = 0.
The second statement adds a row to sys.indexes with index_id > 1 (2 or more)

Explanation:

When you create a table, a row is added in sys.indexes. If the table is a heap, the row has index_id = 0. If a clustered index is included in the CREATE TABLE definition, then the row as index_id = 1 for that index.

When you add a nonclustered index to the table, it receives index_id > 1. This does not affect the rows with index_id = {0,1}.

Ref: sys.indexes - https://msdn.microsoft.com/en-us/library/ms173760.aspx

» Discuss this question and answer on the forums

Featured Script

Find the position of Nth repeated char/string

Hemant Ray from SQLServerCentral.com

CHARINDEX just returns the 1st position of give char.

This function can be used in Data Analysis or Data Mining to find the Nth posistion of any repeated character or string.

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 2016 : SQL Server 2016 - Development and T-SQL

Concurrency - Please Provide Comments - Hi all I have a question about concurrency. Let me provide some set-up code first. [code="sql"]DROP TABLE IF EXISTS dbo.Test1 CREATE TABLE dbo.Test1 ( PK...

SQL Server 2014 : Administration - SQL Server 2014

Bit of a rant - queries on space - Sorry this might be me ranting abit, but am prepared for someone to tell me I am being unreasonable hehe. So...

database features - is it always better to add database features like analysis services and integration tools and reporting services when installing sql server...

backward compatibility - do I have to install backward compatibility after installing SQL server 2014. And what is backward compatibility used for. And Can I...

Displaying Multiple rows clock event Under a different Column view - I would like to query the data were table output is by each row but i need need display the...

Very bad performance on a good HP DL380 Gen9 Server - This issue is something that I haven't had much luck trying to solve via on-line research, and was hoping to...

SQL Server 2014 : Development - SQL Server 2014

insert a new row(s) into sql table A from table B - Hello, I am using SQL Server 2014. I have two table the first one is Table_A and Table_B. I want to copy...

SQL Server 2012 : SQL 2012 - General

How do I install the Adventureworks database ? - TSQL - Folks I have the AdventureWorks2012_Data.mdf file with me. Will this be sufficient for me to install the DB on my local...

function to clean a phonenumber - Hello, I want a sql function that will allow to make one update but with several replace on the same column. For...

Can I do this? - Here is my brilliant idea (that I am sure someone else already thought of...) We have a situation where we regularly...

sql backup - Do you have any idea how long it might take to sql backup for 600 gb. Thank you

SQL Server 2012 : SQL Server 2012 - T-SQL

Selecting from two groups - Hi, I am having issues with the following script. I have 3 tables that link together. What I need to do...

Aggregate Different Values - Hi Gurus, Just wanted to seek out for your help. This is a bit opposite of what aggregation should be. :hehe: I...

Get information about people who are free on a particular day - Hi, I have the following data with me: CREATE TABLE Table1 (ID varchar(10),StudentName varchar(30), Course varchar(15) ,SECTION varchar(2),DAY varchar(10), START_TIME time , END_TIME...

Big data: transfer x-number of rows per batch? - Hi all, I must transfer a big table (231k rows not that big, but 735GB big because of blob) to another...

SQL Server 2008 : SQL Server 2008 - General

SQL Backup running long - Hi Guys, Can you give me a guideline as to what to check what caused my backup to run for a...

Looking for way to dynamically replace street suffix with abbreviation based on tables - I'm working on creating a unique address list, but many of the duplicates come in with Street one time and...

SQL Server 2008 : T-SQL (SS2K8)

Next Identity needed - How do you get SQL Server to assign the next identity to a query? I am getting a NULL not...

Counting field values per day between specific date ranges - Hi All, thanks in advance for any help. I have a table where I store holiday bookings, one row per booking....

Programming : Powershell

Parameter on Powershell Script - I'm having some problems sending parameters to a Powershell script when it includes a dash (-) in it. Here's an example...

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.