The COUNT Function in T-SQL (SQLServerCentral 2/9/2018)

[SQLServerCentral - www.sqlservercentral.com]( A community of more than 1,600,000 database professionals and growing Featured Contents - [The COUNT Function in T-SQL]( - [Azure Cosmos DB Consistency Levels]( - [Bringing DevOps to the database. Part 2: Continuous delivery]( - [Webinar: How the GDPR will impact your software delivery processes]( - [Tinker as You Learn]( (From the SQLServerCentral Blogs) - [Maintaining Balance]( (From the SQLServerCentral Blogs) Featured Script - [Run SQL/T-SQL scripts on folder]( The Voice of the DBA What to Read in 2018 Last week I was looking for Database Weekly links and ran across Gail Shaw's [list of books from 2017](. It's an interesting list of fiction, and I might pick up a few this year if I run out of ideas. This reminds me of [Paul Randal's review]( (done annually) of his reading. I usually find a book or two in there that I'd like to read to enjoy a break from life or expand my horizons. I was thinking about this as I've seen a few other posts from software and database users with books that they recommend. This was [an interesting list]( from Xiaohan Zeng and I like [this general list]( from John Sansom for SQL Server. I've run across a few others, especially from all the learning plans that people built for [T-SQL Tuesday #97](. I have no shortage of fiction that I enjoy reading on a regular basis. This is a way for me to get a break from life, and I'm usually working my way through some book. This month I read the [Will Robie series]( from David Balducci (1-4) and then started re-reading the [Jesse Stone series](by Robert Parker and others. Those are my downtimes and breaks from life. In the professional area, I've been working my way through [R in a Nutshell]( as well as [Thoughtful Machine Learning in Python](. The latter has been ongoing for some time, and moving rather slow. It's a tough one to go through. There are classic texts in our industry, as well as some really interesting ones that many of us might enjoy. Perhaps there is something you've read in the last year you recommend, or there are some you want to tackle. Today I'm asking for your reading recommendations. In our careers, I know many of us like shorter, focused posts, some like videos, and others prefer learning in person from others. Those are all great ways to learn, but I still like working through a book to either focus my learning or escape from life. Let us know today what you plan to read this year or what you'd recommend for others, either to help them become better database developers or just enjoy a good story. Steve Jones from [SQLServerCentral.com]( Join the debate, and [respond to today's editorial on the forums]( --------------------------------------------------------------- The Voice of the DBA Podcast Listen to the [MP3 Audio]( ( 3.5MB) podcast or subscribe to the feed at [iTunes]( and [Libsyn](. [feed]( The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. ADVERTISEMENT [SQL Compare]( 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]( [Database DevOps]( Benchmark your Database DevOps maturity level Get a better understanding of how advanced your current processes are, receive recommendations for improvements, and see how your maturity level compares with that of your peers. [Complete the Database DevOps Maturity Assessment]( Featured Contents  [] [The COUNT Function in T-SQL]( Igor Micev from [SQLServerCentral.com]() The COUNT function is among the most used functions in the T-SQL codes. Even though COUNT is easy to use, it should be used carefully because it could often not return the desired result. For the big tables, the counting of the rows could cause blocking as well as take some more time.[More »]( ---------------------------------------------------------------  [] [Azure Cosmos DB Consistency Levels]( Additional Articles from [Database Journal]() The majority of traditional database management systems are responsible for making sure that any changes to their data are consistently reflected in the results of subsequent queries. Azure Cosmos DB extends the range of consistency options by providing support for bounded-staleness, session, and consistent prefix models.[More »]( ---------------------------------------------------------------  [] [Bringing DevOps to the database. Part 2: Continuous delivery]( Many application developers already use continuous integration to automatically test their code, and release management tools to automate application deployment. Database developers can join them.[More »]( ---------------------------------------------------------------  [] [Webinar: How the GDPR will impact your software delivery processes]( With the GDPR enforcement date looming, organisations are focusing on how data is used internally. This webinar will explain the principals of data protection, translate the GDPR jargon, and cover the steps you'll need to consider to ensure compliance in your software delivery process.[More »]( ---------------------------------------------------------------  [] From the SQLServerCentral Blogs - [Tinker as You Learn]( Brian Kelley from [SQLServerCentral Blogs]( In recent weeks I’ve seen my brother from another mother, Andy Leonard (twitter | blog), write or post about his learning...[More »]( ---------------------------------------------------------------  [] From the SQLServerCentral Blogs - [Maintaining Balance]( SQLEspresso from [SQLServerCentral Blogs]( Last week, I got the chance to give my I’m It Survival Tips for the Lone DBA  in a webcast for the first...[More »]( Question of the Day Today's Question (by Steve Jones): I've got this table and data: CREATE TABLE TheCounts ( intcol INT , charcol CHAR(10) , varcharcol VARCHAR(100) , datecol datetime2 ) GO INSERT TheCounts VALUES (1, 'West', 'Denver Broncos', '2018-01-01') , (2, 'West', 'Oakland Raiders', '2018-01-01') , (3, 'West', 'Los Angeles Chargers', '2018-01-01') , (4, 'West', 'Kansas City Chiefs', '2018-01-01') , (5, 'South', 'Jacksonville Jaguars', NULL) , (5, 'South', NULL, '2018-01-02') , (6, NULL, 'Indianapolis Colts', NULL) , (7, NULL, 'Houston Texans', NULL) , (NULL, 'North', 'Pittsburgh Steelers', NULL) , (NULL, 'North', 'Baltimore Ravens', '2018-01-03') , (NULL, 'North', 'Cincinnati Bengals', NULL) , (NULL, 'North', 'Cleveland Browns', NULL) , (13, 'East', 'Cleveland Browns', '2018-01-04') , (13, 'East', 'Cleveland Browns', '2018-01-04') , (14, 'East', 'Cleveland Browns', '2018-01-04') , (14, NULL, 'Cleveland Browns', NULL) What do I get when running this code? SELECT COUNT(ALL tc.varcharcol) FROM dbo.TheCounts AS tc 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: count(). We'd love to give you credit for your own question and answer. To submit a QOTD, simply log in to the [Contribution Center](. ADVERTISEMENT tag=redgatsof-20 linkCode=as2 camp=1789 creative=9325 creativeASIN=1484222709Design and configure SQL Server instances and databases in support of high-throughput applications that are mission-critical and provide consistent response times in the face of variations in user numbers and query volumes. Learn to configure SQL Server and design your databases to support a given instance and workload. Pick up your copy of this great book today at [Amazon]( tag=redgatsof-20 linkCode=as2 camp=1789 creative=9325 creativeASIN=1484222709) today. Yesterday's Question of the Day Yesterday's Question (by Steve Jones): I ran this code in my database: CREATE RULE dbo.DBASalaryRange AS @salary > 52000 AND @salary < 99000; GO EXEC sys.sp_bindrule @rulename = N'dbo.DBASalaryRange' , @objname = N'dbo.Employees.Salary'; GO Now I execute this: CREATE RULE dbo.NewDBASalaryRange AS @salary > 52000 AND @salary < 150000; GO EXEC sys.sp_bindrule @rulename = N'dbo.NewDBASalaryRange' , @objname = N'dbo.Employees.Salary'; GO What happens when I execute this statement? UPDATE dbo.Employees SET Salary = 120000 WHERE EmpID = 1 Answer: The row is updated and no information returned Explanation: When you use sp_bindrule to apply a rule to a column that already has a rule bound, the new rule overrides to original one. NOTE: Rules are deprecated. Check constraints should be used instead. Ref: sp_bindrule - [click here]( --------------------------------------------------------------- [» Discuss this question and answer on the forums]( Featured Script [Run SQL/T-SQL scripts on folder]( Daniel Marques from [SQLServerCentral.com]() A bat script to run all the sql/t-sql scripts in a specific folder. Features: - Using SQLCMD for running the scripts. - Logging the results of your sql to a file per script. - Input for scripts and log folders. - Beginning and end times for each script. - Check if the folders exist - Total running duration of each script saved in the logs. Usage: - Create a empty .bat or .cmd and copy and paste the code in it. - Change your machine and instance name(for clusters) for single instance you just need your machine name. - Put your credentials(if needed) - Run the script, you will be prompt with the scripts and logs paths(can be the same). - Just wait...the bat will do the rest :) There are other scripts out there that can do this, this just one more, simple and working. Use and abuse. Daniel Marques [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 2017]( : [SQL Server 2017 - Development]( [Calendar table]( - I have been trying to find out the best way to design a Normalized table for --------------------------------------------------------------- [SQL Server 2016]( : [SQL Server 2016 - Administration]( [Access users can't use SQL 2016 (?)]( - I'm migrating several Access backends from SQL Server 2005 to SQL Server 2016. I have successfully gotten the ODBC connections... [DB compression with San compression]( - Hi, We have table compression on for our big DW fact tables: Columnstore for some and traditional row store compression. Currently... [SSRS - Inherited Server - Unable to find instance - Is it possible to repair?]( - Hello! On the SQL 2016 Server I am a "DBA" of now, I was looking to setting up SSRS. At first... --------------------------------------------------------------- [SQL Server 2016]( : [SQL Server 2016 - Development and T-SQL]( [Split 60 character string every third character with pipes]( - Hi All, I'm needing to split a 60 character string every third character with spaces/pipes and filter out any set that... [EXISTS/ ALL?]( - Is there a way in SSRS to convert a delimited list to a series of "AND EXISTS()" subqueries? In SSRS,... [Calculating sum of days/weeks]( - Hi, I have a calendar table that have the days, I need to add the following columns Days in a... [Row-level security for users belonging to multiple AD groups?]( - Hi, I am new to RLS, and I am trying to solve the following problem: We have fact tables that have multiple... [Performance issue after migration from SQL Server 2012]( - Hello, I'm facing with a strange performance issue on SQL Server 2016 Service Pack 1 CU5 instance after migrating from a... --------------------------------------------------------------- [SQL Server 2014]( : [Administration - SQL Server 2014]( [View VS Select from Table]( - Experts, We have a procedure which select from a lot of tables. Will there be any performance improvement if we create... --------------------------------------------------------------- [SQL Server 2014]( : [Development - SQL Server 2014]( [help with substring]( - hello all, need help to split data as below. i try to use charindex to find occurrence of 'And Id in... --------------------------------------------------------------- [SQL Server 2012]( : [SQL 2012 - General]( [Help me with the SELECT statement please ?]( - Good Folks... My question is already in the code... See the code ( the very last SQL statement ) Basically I am asking... [TDE and Logshipping]( - Hi We already have an established Log Shipping environment.The secondary has the databases in the 'Non recovery mode' I have just enabled... --------------------------------------------------------------- [SQL Server 2012]( : [SQL Server 2012 - T-SQL]( [Get difference between dates year by year]( - Get difference between dates year by year [How to query with this date format ?]( - Hi , I have to query a column with this date format : 16-Nov-2017 00:000. The 00.00 refers to time stamp that... [Automate LogSHipping for multiple databases]( - I need to set up log shipping for about 100 databases. Log backup would occur ever 4 hours and would... [Insert data in multiple batches for millions of record]( - Hi, I have table x, table y table x have millions of record, so i want to insert data by multiple batches... --------------------------------------------------------------- [SQL Server 2008]( : [SQL Server 2008 - General]( [Upgrade Sql 2000 to SQL 2008]( - Dear Friends  I was able to back up my database in sql 2000 and restore in sql 2008, but the objects,... --------------------------------------------------------------- [Data Warehousing]( : [Integration Services]( [Moving zip files]( - Hi guys This sounds like it should be simple but I'm banging my head on the brick wall again. I have an... --------------------------------------------------------------- [SQLServerCentral.com]( : [SQLServerCentral.com Website Issues]( [Spam Spam Bacon and Spam]( - Wow the spam is just raging out of control again. It seemed to be under control for a few months... This email has been sent to {EMAIL}. 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](. --------------------------------------------------------------- This transmission is ©2018 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. Contact: webmaster@sqlservercentral.com