In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Data Generator How do you generate test data for your database?
SQL Data Generator quickly populates databases with intelligent and meaningful test data. "SQL Data Generator is simple and effective." Michael Gaertner, Quintech. Download a free trial now.
 
SQL Backup Pro Can you can restore your backups under pressure?
Use SQL Backup Pro's fully integrated DBCC CHECKDB to verify your backups, so you can restore them when it matters most. Download a free trial now.
 
SQL Prompt Make working with SQL a breeze
SQL Prompt 5 is the effortless way to write, edit, and explore SQL. It's packed with features such as code completion, script summaries, and SQL reformatting, that make working with SQL a breeze. Try it now.

In This Issue

Stairway to SQL PowerShell Level 5: SQL Server PowerShell Building Blocks

You should be on your way with PowerShell with the previous levels and now the topic shifts to using Functions and Assemblies built in to .NET for use with automation. This topic will be used in your PowerShell adventures throughout your career and this Stairway. More »


Data Science Laboratory System - Instrumentation

It is sensible to check the performance of different solutions to data analysis in 'lab' conditions. Measurement by instrumentation makes it easier to develop systems that are efficient. More »


From the SQLServerCentral Blogs - The Axis Of Evil

Views, table variables and local variables are great in terms of programming principles. But In terms of performance, they can be... More »


Editorial - Car Data

I really like cars. In my lifetime I have owned more than my share of vehicles, and I always look forward to renting new makes and models when I travel, just to drive something different. As cars have evolved over the last few decades, there are some things about the changes I love, and some things I dislike. Personally I like the idea of a key fob that enables me to unlock the car and start the engine with a button without pulling the keys out of my pocket. However, as someone that's lost my share of keys, I'd prefer a real key as a backup mechanism. Unfortunately that doesn't seem to be an option many manufacturers want to provide. I dread having to replace a $200 "smart key" at some point in order to drive my car.

Cars have implemented a wide array of technology over time, some of which drivers are not even aware. These days cars gather an impressive amount of data, though probably not quite at the scale of the new Dreamliner. Or maybe they do. According to this article, cars can produce "hundreds of MB/s" in data acquisition. I would guess most of that data is thrown away, but some may not. I was quite impressed with the amount of data Tesla logged during the recent test drive controversy with one of their vehicles.

All this data, and the potential need to manage it, mine it, and perhaps make it available to other applications, is another sign of just how important our jobs as data professionals may be in the future. More and more of the things we encounter on a daily basis are creating data that we may turn into information for business decisions through creative uses of software. While some parts of our database systems may become easier to use, I suspect there are no shortage of new skills we will need to learn in the future to make sense of our data.

Mobile technology, whether with cell phones or transportation (planes, trains, and automobiles), will become more prevalent in our lives in the next decade. I suspect this will mean many more opportunities for data professionals. Especially as I'm sure there will be new regulations and requirements that will keep software developers and database professionals busy modifying applications as legislation tries to catch up with the creativity of technologists.

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


The Voice of the DBA Podcasts

We publish three versions of the podcast each day for you to enjoy.

Everyday Jones

The podcast feeds are available at sqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there. Overall RSS Feed: or now on iTunes!

Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

You can also follow Steve Jones on Twitter:

» To submit an article, rant or editorial, log in to the Contribution Center


Question of the Day

Today's Question:

Consider the following script consisting of 5 batches, choose the best answer for what happens.

CREATE TYPE dbo.MyTable AS TABLE
     (CountryCode CHAR(2)
     ,CountryName VARCHAR(250)) 
GO

CREATE FUNCTION dbo.Test (@MyTable MyTable READONLY) 
 RETURNS TABLE 
RETURN SELECT CountryCode, CountryName
        FROM @MyTable 
GO

DECLARE @T MyTable
INSERT INTO @T 
 SELECT 'US', 'UNITED STATES' 
 UNION ALL 
 SELECT 'CA', 'CANADA'

SELECT * FROM dbo.Test(@T) 
GO

DECLARE @T MyTable

INSERT INTO @T 
 SELECT 'US', 'UNITED STATES' 
 UNION ALL 
 SELECT 'CA', 'CANADA'

SELECT *
 FROM dbo.Test((SELECT CountryCode, CountryName FROM @T)) 
GO

DROP FUNCTION dbo.Test DROP TYPE dbo.MyTable GO

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

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

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

SQL Server 2012 Query Performance Tuning

SQL Server 2012 Query Performance Tuning leads you through understanding the causes of poor performance, how to identify them, and how to fix them. You’ll learn to be proactive in establishing performance baselines using tools like Performance Monitor and Extended Events. You’ll learn to recognize bottlenecks and defuse them before the phone rings. You’ll learn some quick solutions too, but emphasis is on designing for performance and getting it right, and upon heading off trouble before it occurs. Delight your users. Silence that ringing phone. Put the principles and lessons from SQL Server 2012 Query Performance Tuning into practice today.

Get your copy from Amazon today.


Yesterday's Question of the Day

Which styles of datetime or smalldatetime are deterministic when using Convert? This is in SQL Server 2005 and greater? (choose 3)

Answer:

  • Styles greater than 100
  • not 106,107,109,113
  • 20,21

Explanation: They way I understand deterministic results of CONVERT, I look at the nondeterministic results, by setting different styles. Anything less than 100 doesn't use century(yy) with century(yyyy). Except 20 and 21, they are ODBC canonical, they have century(yyyy).

Here is an example of 106,107,109,113, being nondeterministic. Run this on a test database and server.

SET LANGUAGE Italian
SELECT DATENAME(month, GETDATE()) AS 'Month Name'


SELECT CONVERT(VARCHAR(10),GETDATE(),102)
SELECT CONVERT(VARCHAR(10),GETDATE(),106)
SELECT CONVERT(VARCHAR(10),GETDATE(),107)
SELECT CONVERT(VARCHAR(10),GETDATE(),109)
SELECT CONVERT(VARCHAR(10),GETDATE(),113)

SET LANGUAGE us_english
SELECT DATENAME(month, GETDATE()) AS 'Month Name'

 
SELECT CONVERT(VARCHAR(10),GETDATE(),102)
SELECT CONVERT(VARCHAR(10),GETDATE(),106)
SELECT CONVERT(VARCHAR(10),GETDATE(),107)
SELECT CONVERT(VARCHAR(10),GETDATE(),109)
SELECT CONVERT(VARCHAR(10),GETDATE(),113)

GO
As you can see 10 the results do not differ , when using a different language.

Ref: http://http://msdn.microsoft.com/en-us/library/ms178091(v=sql.110).aspx
http://msdn.microsoft.com/en-us/library/ms187928.aspx

» Discuss this question and answer on the forums

SQL Server 2012 Integration Services Design Patterns

SQL Server 2012 Integration Services Design Patterns is a book of recipes for SQL Server Integration Services (SSIS). Design patterns in the book show how to solve common problems encountered when developing data integration solutions. Because you do not have to build the code from scratch each time, using design patterns improves your efficiency as an SSIS developer. In SSIS Design Patterns, we take you through several of these snippets in detail, providing the technical details of the resolution.

Get your copy from Amazon today



Featured Script

Monitor current transactions and get all required information

Check which transactions are running, query text, percent complete , query_plan, wait_type, and how long they have been running etc. - with one command. More »


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 2005 : Administering

need script for space util - Hi, Can anyone share TSQL script for displaying each drive wise Total, Free , Free% space utilizaiton report so that if it...

SQL Mirroring didn't failover - Hey All, I was wondering if anyone came across the following issue... We have mirroring setup with automatic failover. Last night our...

SQL Server 2005 : Backups

Maintinace Backup job taking longer than expected.. - Hi, In one of my production envirnment Maintinace Backup job is taking longer than expected. I have maintinace plan to take the...

SQL Server 2005 : Business Intelligence

Import Excel into SQL Server using SSIS dynamically - Hi Experts, Can anyone explain me step by step how to import excel into SQL server database using SSIS dynamically and...

SQL Server 2005 : Data Corruption

Time-out occurred while waiting... Reset to device warning - Hi guys and girls, I hope you can help me. I´m running SQL2005 since a couple of years on server, and...

SQL Server 2005 : Working with Oracle

Cannot Query Oracle Linked Table - SQL Server 2005 on Windows 7 box. I have the Oracle 11g client installed. I also have an ODBC connection...

SQL Server 2005 : SQL Server 2005 Security

use of guest account - Hi All, What is the Guest account in SQL Server 2K,2k5 and above? What is the use of this account?...

SQL Server 2005 : T-SQL (SS2K5)

T-SQL - Hi SQL Gurus, I need some help .. I want only the last part of IP from the string.For example :- jdbc:inetpool:inetdae:10.25.81.123:17100?database=us_nnnn i...

SQL Server 7,2000 : Strategies

Data conversion data validating using information_schema.columns - I am looking for an efficient to validate data for a data conversion. The path I am taking is importing...

SQL Server 2008 : SQL Server 2008 - General

fragmentation in database - Hi apart of avg_fragmentation_in_percent in dm_db_index_physical_stats what other factor should be considered while considering table to be defragmentated eg table...

instead of triggers - I came across this example regarding instead of triggers CREATE OR REPLACE VIEW dept_employees AS SELECT dname, ename FROM emp, dept ...

instead of triggers - I came across this example regarding instead of triggers CREATE OR REPLACE VIEW dept_employees AS SELECT dname, ename FROM emp, dept ...

Best Approach to Archieve DB - Example - We have a DB which have loads of data & we want to archiev it to some Server in such...

SQL Server 2008 : T-SQL (SS2K8)

Only allow users to see their own records - I'm working on the database from hell, so bear with me... (they think 'normalization' is a dirty word, or a...

nvarchar - From my understanding nvarchar allows you to store more than one language compared to varchar. Does nvarchar only take up...

Remove Duplicates from a Table using specific columns - Hi Gurus, I am new to Teradata and working on a requirement. Below is the table Col1 Col2 Col3 Col4 A Y 1 Z B...

System M Derived in SQL Server ? - All, I have recently read the below articles. [url]https://www.simple-talk.com/sql/performance/join-reordering-and-bushy-plans/ http://www.benjaminnevarez.com/2010/06/optimizing-join-orders/ http://en.wikipedia.org/wiki/Query_optimizer[/url] [quote] Most query optimizers determine join order via a dynamic programming algorithm pioneered by...

How to report on historical movements within a changing hierarchy - I'm in an IT environment where we're wanting to: 1. run queries which will show rates of consumption of entities which...

How to call a batch file to execute from an SP - Hi All, Need your assistance please, I am not very good with scripting. I have created a draft of SP, and I...

SQL Server 2008 : SQL Server Newbies

Unique Constraints involving two columns. - I have a Table called EmployeeDepartment,it has EmployeeDepartmentId as Primary Key as well as Identity,EmployeeId which is a foreign key...

Database email - I setup database email and get this Message [260] Unable to start mail session (reason: System.TypeInitializationException: The type initializer for 'System.Data.SqlClient.SqlConnection'...

SQL Server 2008 : Security (SS2K8)

How to impersonate an AD Login within a AD Group defined in SQL - We have active directory groups defined within our SQL 2008 server. I have another AD login defined on the server....