|
|||||||||||||||||
In this issue |
|||||||||||||||||
|
|||||||||||||||||
In This Issue |
|||||||||||||||||
|
|||||||||||||||||
|
|
|||||||||||||||||
|
|||||||||||||||||
|
|
|||||||||||||||||
From the SQLServerCentral Blogs -
|
|||||||||||||||||
|
|
|||||||||||||||||
Editorial - 2013 Goals |
|||||||||||||||||
It's the last Friday of 2012. If you're reading this, then I assume the world hasn't ended and you are planning on starting the next year next week. This is the time of year where we often look forward and make resolutions with the best of intentions. Most people don't keep many of their resolutions, though I'm not sure if this is because they aim too high or too far from their current path. As this year ends, I'd like to ask you to think smaller, and think about your career. For the last poll of 2012, I wanted to ask this: What's your goal in 2013?For your goal, I'd like you to think about one thing that you'd like to improve in your skill set in 2013. Is there one area of improvement that you'd like to tackle across 2013? I'm sure there is. SQL Server is a huge platform, and we all have holes in our knowledge. With that in mind, I'd like you to think about something you wish you could do. Perhaps you want to understand how a tally table works, or you'd like to be able to get the versions for all your instances using PowerShell. Maybe you want to tackle something like The OLAP Sprint and improve your BI skills. Choose one area, and then make a goal. Give yourself a concrete project to tackle. The project shouldn't be large, and it shouldn't take the entire year to complete. Drop a note in the discussion and then set an appointment for yourself that repeats every week, every two weeks, or every month and work on the project. Next year I'll ask you if you actually accomplished your goal. » Join the debate, and respond to today's editorial on the forums The Voice of the DBA PodcastsWe publish three versions of the podcast each day for you to enjoy.
The podcast feeds are available at sqlservercentral.mevio.com. You can also follow Steve Jones on Twitter: 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. They have a great version of Message in a Bottle if you want to check it out. 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. » To submit an article, rant or editorial, log in to the Contribution Center |
|||||||||||||||||
|
|
|||||||||||||||||
Question of the Day |
|||||||||||||||||
|
Today's Question: Using SQL Server 2012, create this simple stored procedure. USE AdventureWorks2012 SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROC [dbo].[TestProc3] AS SELECT * FROM [Sales].[vSalesPerson] Executing the above SP returns 17 rows of data. Without further work it does NOT tell me the meta data of the columns returned. I have a requirement that requires knowledge of the column meta data. I then execute the following T-SQL
/* SELECT A */
SELECT * FROM sys.dm_exec_describe_first_result_set_for_object(OBJECT_ID('TestProc3'), 0)
/* SELECT B */
SELECT * FROM sys.dm_exec_describe_first_result_set_for_object(OBJECT_ID('TestProc3'), 1)
The question is: Which SELECT statement (SELECT A or SELECT B) returns NON NULL values for the columns: source_database, source_Schema, source_Table,source_Column. 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. |
|||||||||||||||||
|
|||||||||||||||||
|
|
|||||||||||||||||
Yesterday's Question of the Day |
|||||||||||||||||
|
What will be the output of the query in the "quantity" column?
declare @doctor table(
doctor_id int identity(1,1),
name nvarchar(50),
primary key(doctor_id)
);
declare @service table(
service_id int identity(1,1),
name nvarchar(50),
price int,
primary key(service_id));
declare @doc_serv table(
doctor_id int,
service_id int
primary key(doctor_id, service_id)
);
insert into @doctor values('doc1'),('doc2'), ('doc3'), ('doc4')
insert into @service values('serv1', 1000),('serv2',500),('serv3',700),('serv4',1200);
insert into @doc_serv values(1,3),(3,2),(3,3),(2,1),(2,4),(1,2);
select quantity = count(*)
from @service s
inner join @doc_serv ds on s.service_id = ds.service_id
right outer join @doctor d on ds.doctor_id = d.doctor_id and s.price < 1000
Answer: 6 Explanation: The key to understand this question is logical processing order of the select. Filtering is made at the same time as right outer join is executed. 4 rows from left set meet price's requirements (s.price < 1000). Although "doctor 2" also has connections in bridge table, none of them meets requirements which leaves him as ad additional row from "doctor" table without connections. Because "doctor" table is on the right side of right outer join "doctor 2" and "doctor 4" will be included in output with null values, which will give us 6 rows in total. This example shows that the query isn't logically the same as if it would contain filtering in "where" which is executed after joins are made. |
|||||||||||||||||
|
|||||||||||||||||
|
|
|||||||||||||||||
Featured Script |
|||||||||||||||||
Table Information View -- No Cursors!Row counts and storage space information for all tables in the database. (SQL Server 2005). 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 : AdministeringIssue installing SQL Server 2005 - Hi. I am having a problem installing SQL SERVER 2005 on my windows 7 enterprise 32 bit operating system. The installation... The replication agent encountered an error and is set to restart within the job step retry interval. See the previous step history message or replication monitor for more information - Hi, Im new with SQL, please help me on how to trouble shoot this error "The replication agent encountered an error and... CALL PHONE from SQL Stored PROC/ Alerts - Is there a way to call phone and give automated msg from SQL server; as notification / alert methd; I can do... Changing the initial size of db file - I need to shrink database and log files to less than their inital size, thus change the initial size of... How to Create Linked Server for a MySQL database using SQL Server Management Studio - It took me about a day to figure this out, so I thought I'd try to save someone else the... SQL Server 2005 : Business IntelligenceSSIS Transfer data ON INSERT - I have 2 SQL servers: SQL_Local and SQL_Remote. I have SSIS on SQL_Local. I want to transfer records from SQL_Remote.Customers... Parallel processing not supported on Standard edition of Analysis - Hi, We have a SQL server standard edition in our project. for performance tuning, one of the client SME has recommenced... Process Cube Failed - Help - Hi! Help to problem.... SELECT [dbo_VW_Fato_Inspecao_Barra_Defeito].[dbo_VW_Fato_Inspecao_Barra_DefeitoQtdBarrasDefeito0_0] AS [dbo_VW_Fato_Inspecao_Barra_DefeitoQtdBarrasDefeito0_0],[dbo_VW_Fato_Inspecao_Barra_Defeito].[dbo_VW_Fato_Inspecao_Barra_DefeitoBitola_Inspecao0_1] AS [dbo_VW_Fato_Inspecao_Barra_DefeitoBitola_Inspecao0_1],[dbo_VW_Fato_Inspecao_Barra_Defeito].[dbo_VW_Fato_Inspecao_Bar Difference between browser in SSAS and Excel cube - EDIT: I'm not sure how to delete topics but I found out what my issue was *** please disregard Hi I posted... SQL Server 2005 : DevelopmentReg: Date Inerval - Hi i am working examination schedule project.now my scenario is university already given two dates.examination start date and end date.i... SQL Server 2005 : SQL Server 2005 General DiscussionSSRS parameters override - There is an SSRS Report with some parameter.for example let us assume abc is name of one of the parameter... SQL Server 2005 : SQL Server 2005 SecurityCannot run query as job, but can run it as a query - I'm having issues running a job in SMSS 2005. The queries themselves work fine on their own through SQL queries,... SQL Server 2005 : SQL Server 2005 StrategiesHow implement object orientation features in sql server? - I know features of object orientation could not implemented, but i want to find a way to implement them. For... SQL Server 2005 : SQL Server 2005 Integration ServicesPossible to determine the datetime range of loaded data to staging table? - Hi all, I have created a SSIS package. In the control flow i have a Execute SQL task which will truncate... How to make group of continuous tasks to execute based on single condition - Hello All, Could you please help me out here, i am totally stuck here, i have total 20 tasks in my ssis... How to know which DTSX file was deployed in a server. - Hi all!!! I inherited an interface process (Oracle text file to SQL Server table) which uses a SSIS 2005 package... cannot open datafile - Hi, i'm creating a ssis package to access a flat file. i created a batch script to obtain the filename of the... SSIS Error to use the oracle String - HI all i m getting 1error when i connect my oracle Table using connection String when i click on table ,its... SQL Server 2005 : T-SQL (SS2K5)The maximum allowable avg_user_impact for any missing index - Hello everyone, I am required to create some indexes on the table to improve the performance of the database queries i have... exec master.dbo.xp_cmdshell - [b][/b]:ermm: Its been working all this while but for some reason it has stopped working for only 1 of our... Starting a Job - Hi, I would like to have a procedure start a job, is that possible to do and to do it... SQL Server 7,2000 : In The EnterpriseLog shipping a distribution server - Hi, I'm trying to get log shipping to work with a distribution server used in transactional replication. Shipping the distribution... SQL Server 7,2000 : ReplicationThe subscription(s) have been marked inactive..... - Hallo, from time to time my transactional (push) replications on SQL2000 SP3a servers send me the following error message: Error Message : SQL Server 7,2000 : SQL Server AgentHow to migrate sql scripts from 08 to 05 - [font="Times New Roman"] Hi, We have developed some scripts in SQL Server 2008. Now, we need to migrate them to SQL Server... SQL Server 2008 : SQL Server 2008 - GeneralSSIS loading only 5 of 42 fields to SQL - Greetings all. I am learning SSIS and having great fun with Sequence Containers, Control flows and Data flows. I believe I... SQL Server Pivot tables - I have a table that keeps track of a store open, closed, reopen, closed (again) , reopen(again) etc dates. The table has... PROBLEM WITH DATE CONVERSION - I have a column called "UP_DATE". When the code below trys to read "UP_DATE" I get the following riders.UP_DATE = CDate(reader("UP_DATE").ToString) error.... SSRS Report aggregate and collate pages by items - Here's the scenario: Old system has several documents which are generated in Word and can be viewed individually. If user selects multiple... Performance Issue: Execution Plan different (involves cursors) - On our production server SQL 2008 R2 10.50.4000 our ERP system runs this query (nothing we can do about the... SSIS, SSMS extremely slow to start with Network Cable pluged in - Starting last Friday developers starting having trouble with in some cases SSIS and in others SSMS. For instance one developer would... SSMS not opening - we are unable to open SSMS from the windows box ,however we are connecting to the server from the other... Not null Foreign key self-referencing identity column - Hi all, I'm wondering if there's a way to do the following: IF OBJECT_ID('test') IS NOT NULL DROP TABLE test GO CREATE TABLE test (nId INTEGER IDENTITY... send sms to a mobile phone from sql server 2008 - hey everybody ! please can someone tell me if we send sms to a mobile phone from SQL SERVER 2008 if... Using Varchar(max) to add to a field? - Merry Holidays everyone, I've created a basic table which includes a text field. I then created a SP which basically... Procedure execution taking longer time - I have a procedure which is getting called on click of button. It takes around 5 sec to complete .The... Cisco certification exams object - Certification exam candidates require a relatively high must be mastered quite basic computer knowledge, have the computer skills to apply.... Examination proceeds - I think that:To return the training and certification investments by providing a standard to determine training needs and test the... The value of Microsoft certification exams? - MCP tutorial is the technical manager of the world is seen as a sign of quality.The potential for MCP professionals... How through Microsoft certification exam - Microsoft certification exam is different from the general computer literacy exam, its main purpose is to develop advanced computer expertise.... how to Pivot this - hi i am having two tables one table is having header details and another is having data Table_id Col_1 Col_2 Col_3 Col_4 Col_5 Col_6 1 Mytab_1_1 NULL Mytab_1_2 NULL ... High availability related issues. - Hi guys, Logshipping , Mirroring and Replication operate at which level sql server instance level or database level and cluster of sql... Keeping all three environment(Dev,Stag,Prod) in sync - In our office we run jobs to keep these environment in sync. We get data from outside source and run... Need Suggestions on creating new table - Hi, I am having a condition that i need to create tables on run time that is Say for customer 1, if... Scripts of logins are not done properly - Hi Friends, When am generating scripts of my database in sql 2008r2 am not getting the given permission of sql logins... SSIS PACKAGE DOUBT - Hi Guys, I need a small help, I need to execute package with input parameter. The command which i have... Unable to connect sql server - hi guys, While connecting to sql server named instance we are getting following error: A connection was successfully established with the server,... Applying Patches - Hi , I have to apply patches on SQL server in cluster ACTIVE/ACTIVE. On 2008R2 (RTM) need to apply SP2 On 2008... Blocked Process Alert issues - This is really beginning to annoy me. I have Blocked Process Alerts on every single server in our environment. The alerts... tempdb fileplacement - Hi all, I have a 1T, high I/O database in a SAN environment on which I have planned the database files... SSIS Expressions - How to write a DATEDIFF function inside a Derived column of an SSIS Package. Im trying to have theses functions... SQL Server 2008 : T-SQL (SS2K8)how to fetch records from multiple tables - i have Demo,elections and electionshist tables where i should neglect the pepole who have status='t' from demo table and for... i want to add column in exiting view - Hi i am trying to use design mode. but it is throwing error . is there other wayt to add column in... SELECT * INTO Table without propagating IDENTITY attribute? - Using the simplest SELECT INTO syntax, the IDENTITY property of a column is part of the definition of the destination... Problem related to transaction - Hi all, My friend ask me the following transaction problem. Consider the following structure of tables as [b]Table1(int)[/b] [b]Table2(int)[/b] [b]Table3(int)[/b] Now consider the following problem. [code="sql"] CREATE... Updating an Entire Column in a 10 GB table - Hi Folks, I have a very big table with size around 10 GB(Data+NC Indexes). There is a need to update an... Maintenance Plan issue - hi all, while executing the plan [b]Maintenance Plan [/b] it's giving error like the log for database 'tempdb' is not available.... Stored Procedure looses execution plan. - I have a stored procedure and when I run it First time it creates an execution plan and it takes... NOT EXISTS - tortoise or horse ? - All, I have often worked on the below requirement. There are two tables and fetch the record from TableA which doesn't exists... Remove the first two characters in a column value if they meet a certain condition - I have a column of data in a table that I need to update and remove the first 2 characters... Finding a quote in a string - What can I use to find if a string contains a '? I know how to replace the quote with a... ORDER BY Should be same as my input in IN() - DECLARE @T TABLE(ID INT,NAME VARCHAR(10)) INSERT INTO @T VALUES(1,'PPP') INSERT INTO @T VALUES(2,'AAA') INSERT INTO @T VALUES(2,'ZZZ') INSERT INTO @T VALUES(3,'XYZ') INSERT INTO @T... SQL Server 2008 : SQL Server NewbiesSQL Server 2012 Developer - I have a book that uses SQL Server 2008 and requires at least a developer edition to run SSIS. My... download sql server management studio RTM 9.00 1399.00 - Can anybody show me the download link sql server management studio RTM 9.00 1399.00 (not express version). Ignore/Skip data coming from falt file and loading into SQL table - Hi, I have issuse to figure out how to skip a data from a column in a flatfile to SQL table.... Sql Server 2005 doesn't connect for windows xp SP3 - I have installed sql server 2005 in Windows XP SP3. Installed successfully but [b]a) server didn't connect gave error message... Create Table and Bulk Insert - Hi guys, I have a .csv file wich has the following information (exactly like this): List Name: User Format Version:1.2.5.0 Date:12/11/2012 12:00:34... SQL Server 2008 : SQL Server 2008 High AvailabilityEMC Upgrade caused SQL server to crash - I have a two node cluster for my production system that is running SQL Server 2008. We were upgrading our... Extremely slow commits - I am a .NET dev working with have a high-availability SQL2008-R2 database, which is a bit of an OLTP/OLAP mix.... ODS - Operation Data Source with small data comapring to Production to gain performance. - Hi, we are in the process of building ODS - Operational Data source, ODS will contain only current 3yrs data, whereas Production... Is database mirroring used much in the industry? - I'm curious because some features of SQL Server are not used that frequently in businesses. What types of business environments... Suspending Database Mirroring - Hi All Does anybody know if suspending db mirroring prevents automatic failover should the primary fail ? I know that Suspending stops... SQL Server 2008 : SQL Server 2008 AdministrationSQL server Agent has failed - I am installing SQL server on cluster but SQL Agent has failed to comeonline with bellow errors in event log. Cluster... database Mirroring with suspend ?? - Hi team, we configured dataabase mirroing with in the cluster . Now one of the database in suspend mode . configured mirroing on... Export data failed? - Hi, SQL - SQL Server 2008 Standard Edition with Service Pack 1 I have tried to export all tables to another new database... ALTER AUTHORIZATION hangs and causes blocking - Hi, I'm trying to change the owner of a database using a command like the following, but it doesn't complete and... Define CPU terms/keywords ? - i am bit confused in following CPU keywords 1) dual-core 2) quad-core 3) [b]n[/b]-core processor 4) sockets 5) "number of processors" means ? It's... MAXDOP setting - I know that this is slighty a 'depends' question but our server is heaver user transactional server. Our SQL 2008R2 server... JobName Not found error - HI All, I'm seeing a intresting error on one of my SQL Instance. Below is the script I'm running,, connecting to... Using ::fn_dblog() to find who deleted the rows in a table. - Hi All, Recently some one deleted some rows from a table. I was asked to find out who did it. Since... Career : Employers and Employeesadivce about career - Hi, my self sudarshan,I am currently working in automation company from last 6 moths on sql server 2008, we generate... Programming : PowershellQuery local SSMS server group with Powershell? - This may be a totally off the wall question, but it seems like it should be possible to do. Just... SQLServerCentral.com : Anything that is NOT about SQL!Hardware investment - I need to talk to someone knowledgeable and with no intere$t - Hi there guys. So i'm shaking... I've been hired as an IS guy, but with time and budget constraints, i was forced... WordPress Question - I've got WordPress on my blog and need some help. When I'm blogging about SQL Server, I frequently go "below... Fantasy football 2012 - Only 2.5 short months to football, can you believe it? I'm not ready, hardly feels like it's summer time yet. You... 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 ServicesPermission Issues - Hi, SSRS configured on ABC server I do not have permission to access physically to that server. I have to access from... Need help in Building a parameterized Report. - Hi All, I have a ETL design which has 58 Tasks that it does every day. Each ETL is assigned... Hide data label for pie chart slices under 10% - I'm working on a pretty simple pie chart in BIDS 08. I have 6 slices of pie and want to... Database Design : HardwareStorage Subsystems and RAMDisks - We're setting up a new database server for our OLTP and smaller website databases, and disk performance is a big... Data Warehousing : Integration Servicesssis 2012 package - I have successfully deployed my first package to sql 2012. The package runs fine in visual studio but when deployed... how to read dynamically changing source excel files - Hi All SSIS Gurus i need help. i have a ssis package which loop through the excel files and import the... Data Warehousing : Analysis ServicesFailed Cube Process - Help - Hi! Help to problem.... SELECT [dbo_VW_Fato_Inspecao_Barra_Defeito].[dbo_VW_Fato_Inspecao_Barra_DefeitoQtdBarrasDefeito0_0] AS [dbo_VW_Fato_Inspecao_Barra_DefeitoQtdBarrasDefeito0_0],[dbo_VW_Fato_Inspecao_Barra_Defeito].[dbo_VW_Fato_Inspecao_Barra_DefeitoBitola_Inspecao0_1] AS [dbo_VW_Fato_Inspecao_Barra_DefeitoBitola_Inspecao0_1],[dbo_VW_Fato_Inspecao_Barra_Defeito].[dbo_VW_Fato_Inspecao_Bar |
|||||||||||||||||
|
|
|||||||||||||||||
|
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 ©2012 Red Gate Software Ltd. All rights reserved. Contact: webmaster@sqlservercentral.com |
|||||||||||||||||
Tally OH! An Improved SQL 8K “CSV Splitter” Function
Index Create Memory Setting for SQL Server
SQL Server – Skipping Function Execution when parameters are NULL
