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

Stairway to PowerPivot and DAX - Level 2: The DAX COUNTROWS() and FILTER() Functions

Bill Pearson from SQLServerCentral.com

Bill Pearson, business intelligence architect and author, exposes the DAX COUNTROWS() and FILTER() functions, while generally exploring, comparing and contrasting the nature and operation of calculated columns and calculated measures, in the second Level of our Stairway to PowerPivot and DAX series. More »

Database Deployment: The Bits - Copying Data Out

Additional Articles from SimpleTalk

Occasionally, when deploying a database, you need to copy data out to file from all the tables in a database. Phil Factor shows how to do it, and illustrates its use by copying an entire database from one server to another. More »

From the SQLServerCentral Blogs - SQL Server Thumbnail Metrics – OS Memory

Steve Jones from SQLServerCentral.com

I’m gathering a few metrics around the Internet for SQL Server from people that I think really know how to... More »

This editorial was originally published on Jan 11, 2008. It is being re-run as Steve is traveling.

This past week at the Consumer Electronics Show, the Keynote speaker was Bill Gates. He's given the speech a number of times, and he said this was his last one. He's given that keynote quite a few times, along with many others, over the years as the face of Microsoft.

This being a Friday, and with me finding out that I'll be at 3 events (at least) this year, I'm looking forward to hearing some good speeches. With that in mind, this weeks poll is

Who's the best keynote speaker you've seen?

I've been lucky enough to see Bill Gates speak quite a few times and I've always enjoyed it. He's a good speaker and they put together some good presentations for him. He's definitely got to be one of the best that I've seen.

I'm not a big fan of Ballmer and don't think he's a great speaker, and there have been a few speakers from Microsoft that I didn't really like over the last year or so. I will say that Bill Baker always does a great job and he's one that I hate to miss at PASS or any other event. Jim Gray was wonderful when I saw him 10 years ago and

While Microsoft is at many events I've attended and sent quite a few people, I'll say that both Dr. Michael Treacy and Dr. Robert Kaplan, both speakers at the Microsoft BI Conference in 2007, were excellent. They were business speakers that had some very interesting things to say and did a great job presenting their views. I would highly recommend either of them for a business based lecture.

I'm not sure who my favorite would be, though Bill Gates might be up there. I'll say that at the height of their popularity and success, I saw Jim Barksdale give a great speech. He's one of the few I'd definitely go see again.

Steve Jones

The Voice of the DBA Podcasts

The podcast feeds are now available at sqlservercentral.podshow.com to get better bandwidth and maybe a little more exposure :). Comments are definitely appreciated and wanted, and you can get feeds from there.

The RSS Feed: or now on iTunes!

• Windows Media Podcast - MB WMV
• iPod Video Podcast - MB MP4
• MP3 Audio Podcast - MB

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.

I really appreciate and value feedback on the podcasts. Let us know what you like, don't like, or even send in ideas for the show. If you'd like to comment, post something here. The boss will be sure to read it.

Steve Jones from SQLServerCentral.com

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

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

Today's Question:

Suppose I have two schemas: person and beneficiary. I create multiple tables with the name of "malls" with different schemas. Which statements will succeed? The answers are given as success or failure in the order of the statements listed below.

create table person.malls(id int)
go
create table beneficiary.malls(id int)
go
create table malls(id int)
go

By malls reddy

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

This question is worth 1 point in this category: Schema. 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 Data Integration Recipes

SQL Server 2012 Data Integration Recipes provides focused and practical solutions to real world problems of data integration. Need to import data into SQL Server from an outside source? Need to export data and send it to another system? SQL Server 2012 Data Integration Recipes has your back. You'll find solutions for importing from Microsoft Office data stores such as Excel and Access, from text files such as CSV files, from XML, from other database brands such as Oracle and MySQL, and even from other SQL Server databases. You'll learn techniques for managing metadata, transforming data to meet the needs of the target system, handling exceptions and errors, and much more.

Get your copy from Amazon today.

Which function is not an aggregate function?

By Chirag Patel

Answer: All of these are aggregate functions.

Explanation: All of these are aggregate functions.

Ref: http://msdn.microsoft.com/en-us/library/ms173454.aspx

» Discuss this question and answer on the forums

Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions

Optimize your queries—and obtain simple and elegant solutions to a variety of problems—using window functions in Transact-SQL. Led by T-SQL expert Itzik Ben-Gan, you’ll learn how to apply calculations against sets of rows in a flexible, clear, and efficient manner. Ideal whether you’re a database administrator or developer, this practical guide demonstrates ways to use more than a dozen T-SQL querying solutions to address common business tasks.

Get your copy from Amazon today.

Backup All Databases

Bahram Shayan Sarcheshmeh from SQLServerCentral.com

this script helps you to backup from all database except of sysDatabases More »

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2005 : Administering

SQL SERVER LOCKING ISSUES - I found a blocking issues using the DMV . [url=http://postimage.org/image/4eibr8uvn/][/url] from the captured result . I use DBCC IND('DBNAME', 'M_SYSTEM_CONFIG',-1), but i did...

sp_who2 output explanation pls - Dear all, I would like be able to understand the meaning of a couple of values when launch sp_who2 procedure from...

Linked Server Collation issues - Running SQL2005 sp4 and I have linked server to DB2400 UDB for iSeries using IBMDASQL provider. I use openquery method...

Help needed with analyzing this deadlock - Hi all: Here is the output from the errorlog on a deadlock that occurred this morning: 12/04/2012 09:15:54,spid18s,Unknown,waiter id=process8d9e28 mode=S requestType=wait 12/04/2012 09:15:54,spid18s,Unknown,waiter-list 12/04/2012...

Upgrade from SQL Server 2005 Standard to Enterprise - Hi, Im having some problems with this, it is the first time im attempting to upgrade to a different edition. The...

SQL Server services failing - Please assist my SQL Server service is failing to start after power cut. TITLE: Surface Area Configuration ------------------------------ An error occured while performing...

SNAPSHOT Isolation with (NOLOCK) being used all over - Recently, my applications architect decided that we needed to change the level of the database Isolation from the the default...

Exporting SQL Job - Hi, How to export SQL Jobs which under SQL Server Agent -> Jobs -> (All the jobs). Just want to export these...

Delete old Backup files using T-sql script - Guys do any one have script to delete old backup files more than 5 days worth using T-SQL Code,if so...

SQL Server 2005 : Backups

Create a policy to avoid backups withou copy_only option - Hi. We have several databases in full recovery model. Daily we make full backups, but sometimes we need take a...

Netapp SnapManager for SQL - Hello, Is anyone using Netapp's Snapmanager for SQL to do backups/restores? Have you had any issues with it? All comments are...

SQL Server 2005 : Business Intelligence

SSIS - copyied original package, changed name, but still reading original dtsConfig file - I have an SSIS package, that I did not write. It is used to transfer production data from a configurator...

SQL Server 2005 : Data Corruption

checkdb error on index ID 0 - I need help on fixing checkdb error. Here's the output of checkdb: Server: Msg 8909, Level 16, State 1, Line 1 Table error:...

BugCheck Dump: Non-yielding Resource Monitor - Has anyone ever experienced the following? in sql server log: 2008-11-04 22:25:39.06 Server **Dump thread - spid = 0, PSS = 0x0000000000000000, EC = 0x0000000000000000 2008-11-04 22:25:39.15...

SQL Server 2005 : Development

how to calculate row1 - row2 - row3.... - I have a table which has some n no of rows. The table has Key, value1, value2, value3, etc fields....

How to copy production DB into New Developmant Database? - Hi, We have Sql server 2008 R2 and i would like to set up my new test server and copy production...

SQL Server 2005 : SQL Server 2005 General Discussion

Active Directory - Hi I am trying to link to our active directory but without much success. I have found some useful articles on...

Datatype performance - Hello This may be a stupid question but are there any performance difference between different data type. For this query : select * from...

Make Bulk Changes to DTSX package? - I have a DTSX package with probably... hmmmm....50 steps. For development purposes, everything was written to a temporary database/schema called...

Microsoft SQL Server Management Studio Express - Hello All, We are trying to create a centralised DB which could be used from a client location. Please let me...

Different domains causing poor query performance? - I think my powers of google-fu must be letting me down - can anyone help with the following:- It has been suggested...

Client application generating time-outs - Hello! We are having problems with an application which accesses our SQL Server DB. The application generates time-out errors. The application...

Your Thoughts on ORM Tools - We're at a potentially critical point in one of our major software products, where we're (finally!!!) switching from VB6 to...

SQL Server 2005 : SQL Server 2005 Security

SQL Jobs - Hello, Is it possible in SQL Server 2005 Standard Edition SP1 to see who disable a SQL job? (C2 Audit is disable...

SQL Server 2005 : SS2K5 Replication

Setting up replication and dealing with a time lag - We have a replicated database (transactional replication) that is primarily used for reporting, although this also could be a secondary...

Transactional Replication for Reporting DB Server and Archival of Data - We are planning to have a reporting server where data is replicated using transactional replication from production server. If I...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Very long avg queue length for SQL Server - I am looking into a very unwell data warehouse running on sql server 2005. For the size of the DW...

locks issue - DESCRIPTION: The SQL Server performance counter 'Lock Waits/sec' (instance '_Total') of object 'SQLServer:Locks' is now above the threshold of 0.70 (the...

SQL Server 2005 : SQL Server 2005 Integration Services

how to create ssis package to copy table from one server to another server - how to create ssis package to copy table from one server to another server using BIDS

Logon failure: unknown user name or bad password - Hi, I'm completely lost here... I have a SQL Server Agent job that execute a SSIS package. The same job...

SQL Server 2005 : T-SQL (SS2K5)

Using a variable for a linked server name in stored procedures - Hi, We have a number of SQL db environments for our CRM system, e.g. LIVE, TEST, DEVELOP, etc. and occasionally we...

SQL Server 2005 : SQL Server Newbies

creating a table to show month and year - I need to refer to a temp table which has all the months and dynamically shows the years. for example...

SQL Server 2008 : SQL Server 2008 - General

Suspended process in msdb database - Hello gents I have right now a suspended spid 53 at msdb which is in suspended status for over 2 hours....

script all indexes on a table - Hi, I need to script only indexes on one sql2008 database table only. how to do this in management studio? thanks.

Replication options - merge or peer-to-peer - I have read a lot of articles on this but still have trouble seeing the differences in these and when...

sql service cannot start - [b][u]* when I connect to sql server it displaying the following error:[/u][/b] TITLE: Connect to Server ------------------------------ Cannot connect to NOURHAN-PC. ------------------------------ ADDITIONAL INFORMATION: A network-related...

How to Identify when a specific sql server build was installed on production - I have an issue identifying the version of software that was installed on my production server. When I run [code="sql"]select...

Best way to sync two databases? - Hi, I have two SQL 2008r2 servers. They are mirror imges of each other (IP and name different). I need to sync...

Views -between operator in where clause - How to specify a where clause in a view with between operator??? I have a view which shows empname and sal...

update statement where SET value is variable - PROBLEM: Table J.JTypeID is full of NULLs. I would like to run a statement to update J.JTypeID with the relevant...

SQL Trigger for multiple columns - Insert / update - hi Team, I want to create a trigger, that that should fire when ever particular columns are updated/inserted. am using below query.....

Query to find the start day of the week as Monday - Hi All, How to specify the start day of the week as Monday for the below records I have tow fields Record...

A quick and easy way to pull up table names, column names, and specific data structure - This is another one of my fun little tools that I decided to share. There are times that I need...

Iterating thru a Tables columns - I have a dataset that i need to iterate thru in a row wise manner where each row has columns...

Handy and Simple tool for SSMS 2008 R2??? - Hello, I was wondering why SSMS does not have the standard builtIn plugin Quick Math (for example: sum, avg, max, min)....

SQL Server 2008 R2 Merge Replication IDENTITY field issues - We've recently migrated from a SQL 2005 configuration over to 2008 R2 and are now experiencing an error on publications...

SQL Server 2008 : T-SQL (SS2K8)

error in stored procedure - ALTER PROCEDURE [dbo].[usp_delete] @tablename sysname, @pid int ,@pidname varchar(10) AS DECLARE @SQL varchar(500) SET @SQL = 'delete from ' + @tablename + ' where '+ @pidname + ' = '+ @pid [b]Conversion failed...

Simple SQL Question... - Hi Guys, Simple question but don't know the answer, In my database i have 99 table that don't have any...

Solve Problems Using Recursive CTE - Problem: Bank requires to find out number of months customs have spent money more then certain amount in consecutive month or...

Execute a dynamic select statment - Can anybody help me actually execute the resultant DELETE statements in this query [code="sql"] drop table #incoming CREATE TABLE #incoming ([id] [bigint] IDENTITY(1,1) NOT...

How to improve query with multiple LIKEs with leading wildcards - I have an app that I'm adding a generic search function to. The user types in a word, or phrase,...

Straight & Reverse Check - kcehc eserveR & Straight - All, I have two table as below. [code="sql"]create table currency ( currencypair varchar(30) not null, rate_value numeric(18,6) null ) insert into currency select 'BNGCZK',null union select 'CHFCLP',null union select 'CHFLKR',null union select 'CHFPEN',null union select 'CHFSIT',null union select...

Breaking up a data load to prevent log file growth - Im coming here to get some options for breaking up a data load. the situation is that i have to...

Exercises in t-SQL - I'm in the process of setting up a training environment here, it's a mechanism for me to bring high flyers...

SQL Server 2008 : SQL Server Newbies

Remove Grand Total Row...... - Hi all, I am trying to see if any one can help me on how to remove a grand total row...

Backup failing - Hello Masters, I have SQL Server 2008, My Maintenance plan for backup(Full, Diff and Log) has been failing from last two...

Add Many-to-Many all at once - I have a construct that looks like this: tblItemMaster (ItemID int Identity, other columns); tblTPItemMaster (tpItemID int Identity, other columns); tblXrefItemtpItem (ItemID, tpItemID,...

xp_cmdshell works and then doesn't work - A stored procedure intermittently fails, and it appears to be because xp_cmdshell stops working. If I try it manually, here is...

Precendence Constraint Logical AND/OR Confusion - Hi All, I am wet behind the ears with all things SQL and I'm currently just beginning to study for my...

Getting variable name from cursor as part of query results - Before you chastise me on not doing cursors, since I scanned articles before submitting this request, I know cursors are...

SQL Server 2008 : Security (SS2K8)

Third-Party TDE? - I work for a small company and we have a lot of very sensitive data in our (legacy) database applications...

Login Auditing - I have been asked to provide documentation on where SQL Server displays messages for login auditing. We set the auditing...

SQL Server 2008 : SQL Server 2008 High Availability

Log Shipping failing when the primary DB is taken offline - We have Log Shipping setup on SQL 2008 instances and everything was running fine until the primary database was accidentally...

SSIS package execution using proxy account - Unusal issue during SSIS package execution using proxy account. I am guessing it is related to memory issue on the...

tools to track disk or folder used space - I would like to know the size increase rate of a disk drive that stores database backups. I have overheard from...

Replication between SQL 08 r2 enterprise & standard - Hey probably a very common question but can you Replication between SQL 08 r2 enterprise & standard editions ? Thanks

Mount Point quick question - Hi, I am working on the design of a MS-SQL 2012 failover cluster, running on top of Windows 2008. I am...

Setting up log shipping for multiple databases - Hi, I am new to this forum but I have just had to deal with setting up log shipping for a...

SQL Server 2008 : SQL Server 2008 Administration

Not able to connect db user in standby database - For the Reporting purpose, we need to use another server standby database and logshipping have been configured for DR setup exec...

SQL 2008 R2 SSIS package execution using Proxy account - Unusal issue during SSIS package execution using proxy account. I am guessing it is related to memory issue on the...

SQL server agent restart by itself - Recently we have two SQL servers 2008R2 (SP1) experiencing a random restart of SQL agent. We are sure no one...

database log file - If a database is setup to readonly and in simple recovery mode. Will the log file grow or not? thanks

Getting Error when trying to start the SQL Agent - I even tried manually through below command EXEC XP_SERVICECONTROL 'START','SQLSERVERAGENT' StartService() returned error 1053, 'The service did not respond to the start...

Programming : Powershell

Inserting into table with Powershell - I have a script that I'm adapting from something I found on the internet where I'm getting database size information...

SQLServerCentral.com : Anything that is NOT about SQL!

What are you planning to do in 2013 in the database domain? - 2013 is on the way. Warm greetings to all of you first. I am posting the questions here to collect and...

Talking baseball - Okay, a topic that has NOTHING to do with SQL . . . Came across [url=http://www.sqlservercentral.com/Forums/Topic447796-4-1.aspx]this link[/url] describing the SSC point scoring descriptions,...

Today's Random Word! - HI When you woke up today, or logged-onto Opera Forums, you may have had a dream, a thought, a scene...

Are the posted questions getting worse? - Is it me, or are the posted questions getting worse these days? I just read a post by someone apparently in...

Reporting Services : Reporting Services

SSRS 2008: IsNothing Expression when displaying different fields - Hi All, I'd like to display different fields/columns in a graph based on a parameter. If the parameter is Null I...

Hide columns if Null - I have a report with parameters for 2011 and 2012, if I select 2011 the columns for 2012 are null...

Better Looking Reports - Hi geniuses, Does anybody know any sql component to improve the look of a reporting services report? Thanks to all. Have a...

Database Design : Disaster Recovery

SAN replication - Hi There, We are implementing SAN level replication for our SQL Databases. Can you direct me to any links that guides...

Disaster recovery plan - I have a production server and a log shipping server which are acting as a primary and secondary server respectively...

Database Design : Design Ideas and Questions

List of Database Naming Convention Decision Points - I am looking to find or create a robust list of decision points for database naming conventions. I'll start with...

Database Design : Relational Theory

ER vs ORM and Other Modeling Notations - Which is the dominant modeling language/notation? Is one more effective than the other and which are most often used in...

Data Warehousing : Integration Services

How to force an Abort upon data condition check - What is the best way to make your SSIS Data Flow to fail upon a data condition check. What I'm...

send email of all defaulters - Hi, Please help on below scenario to send mail. I am having a table which contains student attendance. If student is absent...

Cannot use MDX WITH MEMBER statement in ADO NET source - Hi, I tried the following MDX statement in ADO NET source but get a failure message WITH MEMBER CountOfRows AS ( IIF(ISEMPTY(MEASURES.[Fact...

Data Warehousing : Analysis Services

Hierarchy from two different dimensions - Is it possible to have a hierarchy that contains attributes from two different dimensions?

Loading change data from AS400 (or other source systems) - Greetings all! So I will keep this concise as I can; I have a source as400 system I am extracting data...

Need Help on MDX Query.. - I have grouped by state and now i want the totals in the same dataset for country which is nothing...

Microsoft Access : Microsoft Access

Access 2003 ADP runs SQL 2005 SP to selectively delete rows - does not remove any rows - Run through the VBA code, SP removes no records. Run through the database queries window, SP removes no records. Run the SP...