Here’s a simple example on how to encrypt (and decrypt) values in SQL Server:
--How to Encrypt and Decrypt Text in SQL Server (2008+)
--Example by Clint Huijbers
@Key1 NVARCHAR(50) = N'8g87g8ag4r8hinsg^%$#F&^F&^F^F&ÛIBOUG(%*^R&$%&#%^C5'
,@Key2 NVARCHAR(50) = N'&*^%R%^**F$F*OUYBYUB*F%74d654d7f685f65f56f6v6vc88d'
,@ValueToEncrypt VARCHAR(150) = 'This is the value that will be encrypted by the built-in function of SQL Server with two keys and 128-bits encryption :) blablablablablablablablablabl'
,@EncryptedValue VARBINARY(350) --Increase the VARBINARY size when you increase the lengh of @ValueToEncrypt!
SELECT @EncryptedValue = ENCRYPTBYPASSPHRASE((@Key1+N'||'+@Key2),@ValueToEncrypt)
As of the 31st of March, SQL Server 2014 Developer Edition is now a free download:
Quote from MSDN:
SQL Server 2014 Developer Edition is now a free download for Visual Studio Dev Essentials members (you will be prompted to sign in to Visual Studio Dev Essentials before you can download SQL Server 2014 Developer Edition). We are making this change so that all developers can leverage the capabilities that SQL Server 2014 has to offer for their data solution, and this is another step in making SQL Server more accessible. SQL Server Developer Edition is for development and testing only, and not for production environments or for use with production data.
Visual Studio Dev Essentials is Microsoft’s most comprehensive free developer program ever, with everything you need to build and deploy your app on any platform, including state-of-the-art tools, the power of the cloud, training, and support.
Ohh….and this is also a nice one:
SQL Server 2016 Developer Edition, when released later this year, will also be free.
Read the full post on the SQL Server Blog: https://blogs.technet.microsoft.com/dataplatforminsider/2016/03/31/microsoft-sql-server-developer-edition-is-now-free/
Just want to download an evaluation edition?:
Microsoft® SQL Server® 2014 & 2016 Evaluation
Microsoft® SQL Server® 2012 Evaluation
--Search for a specific string in object-definitions:
o.name AS Object_Name
FROM sys.sql_modules m
INNER JOIN sys.objects o ON m.object_id = o.object_id
WHERE m.definition Like '%SELECT%'
--Find a column within the database:
t.name AS TableName,
SCHEMA_NAME(schema_id) AS SchemaName,
c.name AS ColumnName
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%Customer%'
ORDER BY SchemaName, ColumnName
Couple of weeks ago, I shared my SSRS dashboard with the community on LinkedIn:
Koos van Strien asked for the source (RDL) and restyled the dashboard with his ‘webdesign-look’. Pretty nice!
Koos discusses the changes he made to my dashboard on his blog:
Here are both dashboards in full size:
Today I played around with the TRY_PARSE() function and it does work.
Though it’s an expensive one and as we all know, SQL Server isn’t built for handling strings 🙂
DECLARE @dtSQL VARCHAR(10) = '09-2013-13';
SELECT TRY_CAST(@dtSQL AS datetime2) AS [TRY_CAST Result],
TRY_CONVERT(datetime2, @dtSQL) AS [TRY_CONVERT Result],
TRY_PARSE(@dtSQL AS datetime2) AS [TRY_PARSE Result];
More examples: TRY_CAST(), TRY_CONVERT() and TRY_PARSE() – Compare and Contrast
Normally you can find everything on Google, but there is literary no overall list of SQL Server features per version/release. So…I had to make the list myself starting from SQL Server 2012.
Did I forget a feature? Drop a comment.
SQL Server 2012:
– AlwaysOn Availability Groups
– AlwaysOn Failover Cluster Instances
– Non-Clustered Columnstore Indexes
– Data Quality Services (DQS)
– Tabular Model (SSAS)
– FileTable Storage
– Power View
– SQL Server Data Tools (SSDT)
– Contained Databases
– User-Defined Server Roles
– Enhanced Auditing Features
– Sequence Objects (sequence keys)
– Distributed Replay (capture a workload on a server and replay)
– T-SQL commands/statements (for example: THROW, PARSE, TRY_PARSE, TRY_CONVERT, TRY_CAST, CHOOSE, IIF, CONCAT, FORMAT, ROWS, RANGE, LAG, LEAD, PERCENT_RANK, ORDER BY…OFFSET/FETCH)
SQL Server 2014:
– In-Memory OLTP/Tables (256GB)
– Managed Backup to Azure
– Azure VMs for Availability replicas
– SQL Server Data Files in Azure
– Clustered / Updateable Columnstore Indexes
– Resource Governor for I/O
– Delayed durability
– Buffer Pool Extension (SSD)
– Incremental statistics
– Lock priority of online operations
– AlwaysOn Availability Groups (enhanced with support for additional secondary replicas and Windows Azure integration)
– Database Backup Encryption
– SQL Server Data Tools for Business Intelligence (SSDT-BI)
– Native Compiled Stored Procedures (“WITH NATIVE_COMPILATION”)
– T-SQL commands/statements (for example: SELECT INTO)
SQL Server 2016:
– Always Encrypted
– Row Level Security
– Dynamic Data Masking
– Data Compression / Decompression
– In-Memory Table Enhancements (2TB, FK, Constraints, ALTER, TDE, Triggers)
– Improvements on Columnstore Indexes (Parallel insert, Nonclustered B-tree indexes on top of the columnstore, Read/write nonclustered columnstore on top of a regular clustered index, REORGANIZE)
– Stretch Database
– Built-in R Support
– New SSRS Visualizations (‘Power BI’-like)
– Compare Execution Plans
– Multiple TempDB Database Files
– Live Query Statistics
– Native JSON Support
– Temporal Tables
– Query Store Built-in Reporting
– Natively compiled stored procedures can be nested
– T-SQL commands/statements (for example: DROP IF EXISTS, DATEDIFF_BIG, COMPRESS, DECOMPRESS, SESSION_CONTEXT)
The SQL Server Management Data Warehouse (MDW) is one of the most underappreciated features of SQL Server. But it’s a really handy feature which logs WaitStats and more, with almost no overhead (< 1% CPU). And…it does generate some nice reports which you could use to monitor your server (out of the box):
How to enable it
For more info (and how to enable it), please visit:
Here’s another annoying thing that might happen when a stored procedure is too ‘complex’ for SSIS:
"The metadata could not be determined because every code path results in an error..."
"Unable to retrieve column information from the data source. Make sure your target table in the database is available"
Simply said, it needs help regarding the metadata.
One way to do this is to provide SSIS the necessary metadata information. As shown below:
Microsoft SQL Server 2012 extends the EXECUTE statement to introduce WITH RESULT SETS option which can be used to change the Column Name and Data Types of the result set returned by the execution of stored procedure.
Example Using WITH RESULT SETS Feature of SQL Server 2012
Example - Using WITH RESULT SETS Feature of SQL Server 2012
WITH RESULT SETS
[Employe Name] NVARCHAR(100),
[Employee City] NVARCHAR(20),
[Employee Postal Code] NVARCHAR(30)
The WITH RESULT SET Feature of SQL Server 2012 is a great enhancement to the EXECUTE Statement. This feature will be widely used by Business Intelligence Developers to execute a stored procedure with in an SQL Server Integration Services (SSIS) Package to return the result set with required Columns and modified data types.
Read more: http://www.mytechmantra.com/LearnSQLServer/With-Result-Set-Feature-SQL-Server-2012.html#ixzz3nybyEcJU
A little while ago, I made a report to analyze the usage of SSRS report (users and rendering times).
Here’s how it works:
The vertical axis displays the total number of report executions, while the size (and (x) number) displays the total number of unique users.
And not to forget, color of the balloons is based on the average rendering time of that specific report.
It is pretty straight forward on how to implement it.
Download the SQL Stored Procedures and SSRS report RDL-file here:
Have a look at other reports I’ve published for my readers:
SQL Agent Job Schedule Timeline report
SQL Agent Job Status report
Stumbled upon this great post by Marco Schreuder about how to collect source system metadata with T-SQL queries:
The blog post is a few years old, but still the queries might come in handy.
TSQL Metadata Queries
1. Get column information from all tables
2. Get column information from all views
3. Get details of foreign key constraints
4. Get details of the indices created on user tables