Archive

Posts Tagged ‘‎SSGAS2015’

SQL Server Geeks Annual Conference sessions – SSGAS 2015

September 4, 2015 Leave a comment

This year I got an opportunity to attend the SSGAS 2015 i.e. SQL Server Geeks – Annual Summit, the first time ever, large scale professional SQL Conference in India, Bangalore (KA), India, on Aug 27-29, 2015.
 

11952925_10153595838942152_239524521059569587_o
 

This was a 3 day conference organized by the SQLServerGeeks team, headed by Amit Bansal, covering sessions on Database Development, Administration, BI & Analytics, Big Data and Cloud & Azure Platform, delivered by expert speakers from Microsoft, MVP & MCM Community and Industry leaders.
 

11882831_10153595836172152_4025091312935596970_o
 

I would like to share the sessions/topics I attended, their details and links to external references with my readers.

Following are the sessions I attended in the 3 day conference:
 

SSGAS 2015 | Day – 1

Topic Speaker Session Details & external links
SQL Server 2016, new innovations Sanjay Mishra (MSFT) Introduced various new features in SQL 2016, link.
Azure SQL Database, now & future Lara Rubbelke (MSFT) Detailed deep dive on Azure SQL database, link.
Tabular vs Multidimensional in SSAS Alberto Ferrari Details and differences between the two, link.
Business Prediction using Microsoft Azure ML Deepthi Anantharam (MSFT) Demoed a scenario on predicting income by reading different metrics, link.
Column Store Index: SQL Server 2014 & beyond Sunil Agarwal (MSFT) Detailed deep dive on ColumnStore tech, link.
Advanced SQL 2014 Reporting Tech Dr Subramani Paramasivam Detailed deep dive on Reporting Services (SSRS), and demoed how to configure Reports, connections and properties.

 

SSGAS 2015 | Day – 2

Topic Speaker Session Details & external links
In Memory OLTP: SQL Server 2014 and beyond Sunil Agarwal (MSFT) Detailed deep dive on In-Memory tech, link.
SQL Server Performance Tuning Amit Bansal/ Manohar Punna (SSGAS) Demoed performance tuning by using Query Store tool, a new feature of SQL 2016, link.
Analytics with Column Store Index Praveen Srivastava Discussed and demoed ColumnStore indexes, link.
Troubleshooting SQL Server Tips & Tech Amit Khandelwal/ Sumit Sarabhai (MSFT) Customer centric perf issues, creating dump by using SSDIAG & PSDIAG tools, and their dump analysis by SQL Nexus.
Query Processing in In-Memory OLTP Benjamen Nevarez (MVP) Discussed about In-Memory features in SQL 2014/2016, link.
Big Data for the SQL Ninja Scott Klein (MSFT) Demo on spinning up a new Hadoop cluster and Blob Storage on Azure.

 

SSGAS 2015 | Day – 3

Topic Speaker Session Details & external links
Dive into the Query Optimizer Benjamen Nevarez (MVP) Discussed QO internal and Trace flags, link.
SQL Server under attack via SQL injection Anderas Wolter (MCM) Preventing SQL Injection, link.
Operational Analytics in SQL Server Sunil Agarwal (MSFT) Real time analytics with ColumnStore, link.
Understanding Parameter Sniffing Benjamen Nevarez (MVP) Checking and resolving incorrect plans created by SQL DB engine, link.
SQL Server 2016 Stretch Database Stuart Padley (MSFT) Stretch Database demo to stretch on-prem SQL table to Azure DB, link.
Dealing with Bad Roommates – Resource Governor Joey D’Antoni (MVP) Discussed about how to manage SQL workload and system resource consumption, link.
Evangelizing SQL Server Dev paradise Mahendraprasad Dubey/ Narendra Angane (MSFT) Customer centric perf issues due to Blocks, Waits and Latches, resolving them by refactoring their SQL Code.
Social Media Sentiment Analysis using Hadoop on Windows Debcharan Skarkar (MSFT) Intro, demo.
Bursting Through the power of Datazen Slevakumar Rajakumar (MSFT) Datazen Intro, Server & Client details, vs Power BI & SSRS

 

Some social-networking, met with some tech-gurus:

11950421_10153595838007152_7307917696804527762_o

With Scott Klien, [twitter], the Microsoft Channel9 host.
 

11895306_10153595837672152_7826040929249792430_o

With Benjamin Nevarez, [twitter], [blog], the SQL Server Performance guru and author of Microsoft SQL Server 2014 Query Tuning & Optimization book.

And Alberto Ferrari, [twitter], [blog], founder at SQLBI and author of Microsoft SQL Server 2012 Analysis Services: The BISM Tabular Model book.
 

11113257_10153595837972152_3300664183761273059_o

The wall!
 

11887758_10153595841867152_4004313675303231303_o

at NIMHANS Convention Center, Bangalore (KA), India


T-SQL Query solution to ‎SSGAS2015‬ 2nd Question/Challenge – SQL Server Geeks

June 12, 2015 1 comment

The 2nd Challenge in SQL Server Geeks Facebook Page goes like this:

For the #SSGAS2015 attendees are leaving comments with their email ids. These comments gets saved into a table COMMENTS. You as an SQL Developer, need to extract all the email ids from the COMMENTS table.

–> Sample records in the table:

ID	Comment
1	Can I pay using PayUMoney. Please revert on ahmad.osama1984@gmail.com. 
        Also send details to manusqlgeek@gmail.com
2	I would like to get updates on SQLServerGeeks summit. Please send details 
        to myemailid@yahoo.com

–> Expected Answer:

ID	Emails
1	ahmad.osama1984@gmail.com, manusqlgeek@gmail.com
2	myemailid@yahoo.com

 

–> Initially this looked very tricky and tough question, but when you think by dividing the problem it looked very simple and here it goes:

1. First split the sentence into columns containing individual words by the whitespace separator.

2. Then Transpose all the columns as rows.

3. Then filter out the rows that contains email ID values.

4. Now for every ID column Transpost back the filtered rows into a single comma separated column value.
 

–> Here is the full solution:

-- Create the Table (DDL):
CREATE TABLE COMMENTS (
	ID INT, 
	Comment VARCHAR(1000)
)

-- Insert the 2 test rows:
INSERT INTO COMMENTS
SELECT 1, 'Can I pay using PayUMoney. Please revert on ahmad.osama1984@gmail.com. Also send details to manusqlgeek@gmail.com'
UNION ALL
SELECT 2, 'I would like to get updates on SQLServerGeeks summit. Please send details to myemailid@yahoo.com'

-- Check the rows:
select * from COMMENTS

-- Final solution as a single T-SQL Query:
;with CTE AS (
	SELECT A.ID, Split.a.value('.', 'VARCHAR(100)') AS Email
	FROM (SELECT ID, CAST ('<M>' + REPLACE(REPLACE(Comment,'. ', ' '), ' ', '</M><M>') + '</M>' AS XML) AS String
		FROM  COMMENTS) AS A
	CROSS APPLY String.nodes ('/M') AS Split(a)
)
, CTE2 AS (
	SELECT ID, Email
	FROM CTE
	WHERE Email like '%@%'
)
SELECT DISTINCT ID, (SELECT STUFF((SELECT ', ' + Email
 FROM CTE2
 WHERE ID  = t.ID
 FOR XML PATH('')),1,1,'')) AS Emails
FROM CTE2 t

-- Drop the table finally:
DROP TABLE COMMENTS
GO

Waiting for the coming challenges. Thanks!