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

Featured Script

Fast Table Valued Function to return all prime numbers within a range

The Voice of the DBA

Byte Me: Functional Fashion

Bob Lang from SQLServerCentral.com

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

NEW SQL Clone - version 1 available now!

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. Try it free.

The industry standard for comparing and deploying SQL Server database schemas

Trusted by 71% of Fortune 100 companies, SQL Compare is the fastest way to compare changes, and create and deploy error-free scripts in minutes. Plus you can easily find and fix errors caused by database differences. Download your free trial.

Featured Contents

Package Parts – First Impressions

Shubhankar Thatte from SQLServerCentral.com

This article gives an overview of some good and not-so-good features of SSIS package parts. More »

SQL Clone Trivia Quiz - win a $10 gift card every weekday in April

To celebrate the recent launch of their new database provision tool, Redgate are giving you the chance to win a $10 Amazon or Starbucks gift card every weekday this month. To enter the prize draw, just answer the daily SQL Clone trivia question on the right-hand side of the homepage. More »

Free eBook: Inside the SQL Server Query Optimizer

Press Release from SQLServerCentral.com

This free eBook from Redgate Software will take you from the fundamentals of Statistics, Cost Estimation, Index Selection, and the Execution Engine, and guide you through the inner workings of the Query Optimization process, and throws in a pragmatic look at Parameterization and Hints along the way. More »

Working with the BigInt Type in Node and SQL Server

Additional Articles from SimpleTalk

Node.JS and SQL Server are a good match for creating non-blocking, event-driven database applications. Though you can use ODBC or JDBC to communicate between the two, it is possible to run such applications on platforms such as Azure, Linux, OSX and iOS by using Node together with the JavaScript TDS library called 'Tedious'. 'Tedious' is a mature product but it is still possible to get things wrong in converting SQL Server datatypes such as BigInt to native Javascript data. More »

From the SQLServerCentral Blogs - T-SQL Tuesday #89 – The times they are a-changing

Koen Verbeeck from SQLServerCentral Blogs

This month’s T-SQL Tuesday is hosted by yours truly! The topic this month: how do you keep up with the... More »

Question of the Day

Today's Question (by Steve Jones):

In my R console, I've assigned the value of 42 to the variable x, like this:

x <- 42

Now I want to assign the value "42 is the best answer" to the same variable. How do I do this?

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: R Language.

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

Securing SQL Server: DBAs Defending the Database

Protect your data from attack by using SQL Server technologies to implement a defense-in-depth strategy, performing threat analysis, and encrypting sensitive data as a last line of defense against compromise. The multi-layered approach in this book helps ensure that a single breach doesn't lead to loss or compromise of your data that is confidential and important to the business. Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Steve Jones):

When I run a query, if I need a resource that is locked, meaning I am blocked, there is a timeout for how long I will wait for a lock to be released. What is the default setting for this when I connect to SQL Server?

Answer: -1, meaning wait forever

Explanation:

The default is -1, which means wait indefinitely.

Ref: SET LOCK TIMEOUT - click here

» Discuss this question and answer on the forums

Featured Script

Fast Table Valued Function to return all prime numbers within a range

Jonathan Roberts from SQLServerCentral.com

This is an improvement to my last script: http://www.sqlservercentral.com/scripts/Tally+Table/155213/

Instead of deleteting rows from a table variable it uses a NOT EXISTS to filter out rows that are not prime, it also has a better filter to eliminate some obvious non-primes when populating the table variable @N.

Initially I tried writing the process as a script using temporary tables instead of table variables; when the script is written like this it runs approximately twice as fast (a demonstration of why temporary tables are better than table variables). But a table vaued function cannot have temporary tables in its code.

Of course, the fastest way to get lists of prime numbers, in SQL Server, is to create a permanant table of primes on your database which can be achieved this using this table valued funtion:

IF OBJECT_ID('dbo.Primes','U') IS NOT NULL

DROP TABLE dbo.Primes

CREATE TABLE dbo.Primes(N int PRIMARY KEY CLUSTERED);

INSERT INTO dbo.Primes(N)

SELECT Prime

FROM dbo.FastPrimes(100000000,2)

-- Wait for 10 minutes to get all 5,761,455 prime numbers less than 100 million into a permanant table table, using 1.3 GB, that you can then use with great speed at your leisure.

SELECT * FROM dbo.Primes

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 - Administration

A little tale and a seven questions about SQL partition - The tale Once up a time a small software shop had a application used Firebird DBMS. The small shop managed to do...

SQL Server 2016 : SQL Server 2016 - Development and T-SQL

Trying to a Restrict Query by Max Value on Max Date - I already have most of the query I am working with correct Select Clients.rid, Clients.r_fname, Clients.r_lname, Status.StatusFrom Clients Inner Join...

ERROR using CharIndex : Invalid length parameter passed to the LEFT or SUBSTRING function in sql - Hi, I'm using the below function in select query and getting error msg as "Invalid length parameter passed to the LEFT...

concatenate using dynamic SQL - I want to concatenate below table rows using dynamic SQL empno | jobid | vesselno | portid 1 121 1455 1231 1 122 1486 &nbs

SQL Server 2014 : Development - SQL Server 2014

SQL Query Help - Hey all, I need a little help with a SQL query. I have a table that has a uniqueidentifier column, a...

Help with SQL Loop Query - Hi Guys, I am using below While Loop Syntax but for some reason, I can't update the records or it...

SQL 2008R2 to SQL 2014 Upgrade - Hi guys, I am planning to upgrade my SQL Server 2008r2 to SQL 2014. As a developer I want to know...

SQL Server | Architecture design - - Hi Experts, I have couple of basic queries regarding the way SQL Server is designed/works: 1) Schemas in SS is bit different from...

SQL Server 2012 : SQL 2012 - General

SQL help - Need to know the first date of previous month and last date of previous month... - Should be an easy one right ? I like it in YYYYMMDD format

How do I sort this set of records ( SQL HELP please ) - Take a look at the attached screen shot for you you to understand what I need.... 1. ) I need you to...

Query to find when was the database last used - Hello Team, Can you please let me know how to find when all databases on SQL Server were last used. Is...

creating new partition files on table - hello all, I have a table that has 2,948,231,398 trillion rows...I know...last week I had to figure out how to...

SQL Server 2008 : SQL Server 2008 - General

Table size and indexes - How many rows does a table need to have before you start thinking about adding an index?

SQL Server 2008 : SQL Server Newbies

query puzzle - Hello - I have an existing SQL database that keeps track of student information. We use a field called STU.NID to...

SQL Server 2008 : SQL Server 2008 Administration

SQL server back up - We are currently using sql server 2005. For back up, we use sql server maintainance job to back up all the...

Reporting Services : SSRS 2014

Convert Date Time Parameter to String - Hi, is it possible to convert a date time parameter to a string? I have parameters @StartDate and @EndDate which are DateTime...

Reporting Services : SSRS 2016

SSRS Parameter Search on a long list ids - I am creating a searchable SSRS report where staff can search the data sest on client ids. Staff should be...

Data Warehousing : Strategies and Ideas

No PKs on fact tables - I just inherited a data warehouse where none of the fact tables have a primary key (all fact tables are...

SQLServerCentral.com : Articles Requested

Find Database Dependencies in Jobs and other databases - I'm looking for an article that will help someone determine if a job or other object depends on a database....

SQL Server 2005 : Administering

Deadlocks on Clustered Index - hi all i have a very simple control table with 1 record, this record manages number increments for various ranges such...

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.