T-SQL – Concatenate many rows into a single text string

How to concatenate many rows into a single text string?
It’s easier than you think!

Let’s say that my table might look like this:

Names:
James
Daniel
Cindy
Edward

Now, I want to combine the values of ‘Name’ into a single string (separated by a comma: ‘, ‘)

“James, Daniel, Cindy, Edward”

You might want to use a query similar to this one:

DECLARE @Names VARCHAR(MAX)
SELECT @Names = COALESCE(@Names + ', ', '') + Name FROM People
SELECT @Names



MultiValue()
Want to split it up into separate values again? 🙂
Use this table-valued function (TVF) to do that:
Multi-value parameter function








Download the Windows Server 2012 R2 Preview

Windows-Server-2012-R2-Preview

Windows Server 2012 R2 brings Microsoft’s experience delivering global-scale cloud services into your infrastructure. Windows Server 2012 R2 offers exciting new features and enhancements across virtualization, storage, networking, virtual desktop infrastructure, access and information protection, and more.

New and enhanced features allow you to take advantage of even better performance and more efficient capacity utilization in your datacenter, helping you increase the agility of your business. Windows Server 2012 R2 offers a proven, enterprise-class virtualization and cloud platform that can scale to continuously run your largest workloads while enabling robust recovery options to protect against service outages.

Download the preview:
Click here to download the Windows Server 2012 R2 Preview.

More info about this release?
Want info about Windows Server 2012 R2 Preview? Click here.

The Start Menu is back! 🙂
Windows-Server-2012-R2-Preview-Start-Menu

Apps
Windows-Server-2012-R2-Preview-Apps

Download the preview
Windows-Server-2012-R2-Preview-Download


SQL Server 2014 CTP 1 Download

Download Microsoft SQL Server 2014 Community Technology Preview 1 (CTP1)

Microsoft SQL Server 2014 brings to market new in-memory capabilities built into the core database, including in-memory OLTP, which complements our existing in-memory data warehousing and BI capabilities for the most comprehensive in-memory database solution in the market. SQL Server 2014 also provides new cloud capabilities to simplify cloud adoption for your SQL databases and help you unlock new hybrid scenarios.

SQL-Server-2014-CTP-Download

Key Features

Project code-named “Hekaton”: “Hekaton” provides in-memory OLTP capabilities built into core SQL Server database to significantly improve the performance of your database application. “Hekaton” is installed with the SQL Server 2014 Engine without requiring any additional actions and allows in-memory performance benefits without rewriting your database application. You can also increase performance of existing SQL Server applications without having to refresh your hardware. “Hekaton” is easy to deploy and allows you to access the other rich features in SQL Server, while taking advantage of in-memory performance.

xVelocity ColumnStore: xVelocity ColumnStore provides in-memory capabilities for data warehousing workloads that result in dramatic improvement for query performance, load speed, and scan rate, while significantly reducing resource utilization (i.e., I/O, disk and memory footprint). The new ColumnStore complements the existing xVelocity ColumnStore Index, providing higher compression, richer query support and updateability of the ColumnStore giving you the even faster load speed, query performance, concurrency, and even lower price per terabyte.

Extending Memory to SSDs: Seamlessly and transparently integrates solid-state storage into SQL Server by using SSDs as an extension to the database buffer pool, allowing more in-memory processing and reducing disk IO.

Enhanced High Availability
New AlwaysOn features: availability Groups now support up to 8 secondary replicas that remain available for reads at all times, even in the presence of network failures. Failover Cluster Instances now support Windows Cluster Shared Volumes, improving the utilization of shared storage and increasing failover resiliency. Finally, various supportability enhancements make AlwaysOn easier to use.
Improved Online Database Operations: includes single partition online index rebuild and managing lock priority for table partition switch, greatly increasing enterprise application availability by reducing maintenance downtime impact.

TechNet Evaluation Center



Join us at LinkedIn!
Don’t forget to join the SQL Server 2014 group on LinkedIn and stay updated!:
linkedin-sql-server-2014-group








Microsoft® SQL Server® 2014 CTP1 Product Guide

SQL-Server-2014-CTP1-Product-Guide

The SQL Server 2014 CTP1 Product Guide is now officially available to customers and partners. The guide is intended to help you get the most value out of Microsoft SQL Server 2014 CTP1.

Download the datasheets, white papers and technical presentations that will help you evaluate Microsoft SQL Server 2014 CTP1.

Click here to download the product guide.



Join us at LinkedIn!
Don’t forget to join the SQL Server 2014 group on LinkedIn and stay updated!:
linkedin-sql-server-2014-group








SSIS Anchor Modeling example (tutorial)

About
This Anchor Modeling SSIS example is brought to you by:

Clint Huijbers (BumbleBI IT)

Bas van den Berg (C2H)

The first version of the demo was created by Bas and shared on AnchorModeling.com.

Tutorial Video by Bas
Advice you to watch it first 🙂

Tutorial-by-Bas-van-den-Berg


New version of the SSIS package
The fast performing package (final step in his tutorial) was rebuild by Clint and is described below:

Download
To download the SSIS package and T-SQL scripts, click here.


The Anchor Model (AM)
The AM is about telephone calls, that includes two Anchors (Call and Phone), two Ties and three Attributes:

Anchor Model with mnemonics
AM-Model-with-mnemonics

Anchor Model without mnemonics
AM-Model-without-mnemonics


How to install

1) Create a new database called ‘AM_SSIS_Example

2) Create all AM-objects by executing the script: ‘Generated SQL Script by AM Online Modeler.sql

3) Generate source data by executing the script: ‘Create dbo_Calls table (source).sql
Give it a few minutes, it will generate 691.200 rows

The table dbo.Calls is already filled with demo data:
dbo_Calls

4) Create a Batch-table for metadata by executing the script: ‘Create BA_Batch table.sql

The database should now have a couple of tables (also Views, SPs and UDFs):
SQL-Server-Tables

5) Open the SSIS package in BIDS/SSDT and execute the package

The SSIS package demos two scenarios:
#1 – Insert records by using the INSERT-trigger on view dbo.lPhone
#2 – Insert records directly into the Anchor and Attributes
(by using the fast load / bulk insert and parallel processing of the Attributes)

SSIS-Package-Design

Raw File
A Raw File Source is used to temporary store and quickly re-load the full dataset, located in ‘C:\Temp\AM_SSIS_Example_RAW_FILE‘. Important: make sure to manually create/verify this Raw File. You’ll be able to find it here:

SSIS-Raw-File-Connection

Open the ‘Raw File Destination Editor‘-window (right-click and ‘Edit’) and verify the Raw File’s location:

SSIS-Raw-File

6) Watch the show

SSIS-Package-Executed

7) Verify the results by executing the script: ‘Verify the results.sql

AM-data-loaded

8) Increase the volume!
Would you like to re-process all data and perhaps with a lot more records? 😀
Truncate all tables by executing the script: ‘Truncate and delete all data.sql’
Now, drop the table dbo.Calls.
Execute the script ‘Create dbo_Calls table (source).sql‘ again, but this time increase the variable @EndDateTime with just a couple of days.
Have fun!

Coyote

SQLTimer: a SQL Server Timer

the-science-of-timing

thinking_animated_smiley_emoticon

So…you’ve got a fancy Business Intelligence (BI) solution? Predictive Analytics or perhaps Actionable Analytics? Ever thought about the right time to contact your (potential) customers?



SQLTimer might be a handy addition to your BI Suite!
Like a timer for your lighting, it checks every hour whether it’s the right moment to execute jobs for categories like:

BusinessHour: Indicates whether or not it’s the right time to contact your customer during business hours (included in the SQLTimer script)

Other additional time schedules which are downloadable:
Twitter: Indicates whether or not it’s the right time to contact your Twitter users
FaceBook: Indicates whether or not it’s the right time to contact your FaceBook users
LinkedIn: Indicates whether or not it’s the right time to contact your LinkedIn users
GooglePlus: Indicates whether or not it’s the right time to contact your Google+ users
Pinterest: Indicates whether or not it’s the right time to contact your Pinterest users

In this blogpost, I’ll be diving into the SQLTimer itself and the free time schedule called ‘BusinessHour’ (included in the script).

Download the complete script free on:
SQLTimer – How to install


How it works:
Just like the timer for your lightbulb, it is configured on an hourly basis:

SQL-Timer-Execute-TimeSchedule

The values indicate whether it is a bad, better or even the best time to contact your business on a weekly basis:

BusinessHour values:
0) = Outside of business hours
1) = Inside of business hours
2) = Preferred hours

Important note: SQLTimer is setup on a day-to-day basis and thus you might tweak the time schedule a bit for local holidays or other important days in the year:

SQL-Timer-TimeSchedule

SQLTimer objects:
(An existing database is required)
Let’s have a look at all the SQLTimer objects:

SQL-Timer-Objects

*** Tables ***

timer.TimerLogging
This table contains the logging (when a time schedule was executed (StartTime/EndTime) and what value the time schedule had)

SQLTimer’s logging in timer.TimerLogging:

SQL-Timer-Logging

timer.TimeSchedule
This table actually contains the time schedule itself

timer.TimeScheduleConfiguration
This table is all about the configuration

SQLTimer’s configuration in timer.TimeScheduleConfiguration
In case you would like to change the name of the schema, don’t forget to change the configuration settings:

SQL-Timer-TimeScheduleConfiguration


*** Stored Procedures ***

timer.usp_Hourly_Timer
This SP is the timer itself and needs to be scheduled in SQL Agent to be run on an hourly basis

timer.usp_Select_TimeSchedule
You might want to use the timer.usp_Select_TimeSchedule() stored procedure to analyze the time schedule for the coming week:

SQL-Timer-Execute-Select-TimeSchedule

timer.usp_Execute_BusinessHour_Jobs
This SP will execute the preferred jobs (for example: T-SQL code, SP’s or SQL Agent Jobs) for the time schedule ‘BusinessHour’

Here’s how the timer.usp_Execute_BusinessHour_Jobs looks like.
You need to add your T-SQL code, SP’s or SQL Agent Jobs here that need to be executed if the @TimeScheduleValue reaches a certain value.

SQL-Timer-Execute-BusinessHour-Jobs


*** Install the SQLTimer ***

So far about the SQLTimer itself, it’s time to install it!
1) Download the T-SQL script here: SQLTimer – How to install

2) Unzip the ZIP-file and open the *.sql file in SSMS or SSDT

3) Change the database name and uncomment the T-SQL code:

SQL-Timer-T-SQL-Script

4) Execute the T-SQL script
Output of the script:

SQL-Timer-T-SQL-Script-executed

5) Create a SQL Agent Job (Execute T-SQL statements) which executes the timer.usp_Hourly_Timer stored procedure every hour


6) Don’t forget to update the time schedule with your local (company) holidays 🙂
Table: timer.TimeSchedule


Websites:
SQLTimer
BumbleBI IT