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

Stairway to SQL Dialects Level 1: Overview

Joe Celko from SQLServerCentral.com

In the first level of his Stairway to SQL Dialects, Joe Celko looks at the origins of the SQL language and how the language has spawned the various different dialects in use today. More »

SQL Server Reporting Services 2012 Permissions

Additional Articles from MSSQLTips.com

As you begin developing reports for deployment to a Report Server, what security considerations need to be taken into account in order to grant users access to run a report. More »

From the SQLServerCentral Blogs - Microsoft Surface

Grant Fritchey from SQLServerCentral.com

I’m going to do with this what I should have done with the Piece Of Fruit (POF), blog about the... More »

From the SQLServerCentral Blogs - Is PowerPivot the Next Access?

pturley from SQLServerCentral.com

I’m impressed with the current generation of semantic modeling technology from Microsoft.  I’ve had the opportunity to use both PowerPivot... More »

Today's editorial was originally published on Dec 10, 2007. It is being re-run today as Steve is at DevConnections.

I definitely used to not trust developers. At least not with my database code, requiring them to send requests to me and I'd build tables, stored procedures, etc. Over the years as I've talked to others and become a little more mature, I've started to believe that I can trust them more and let them build objects. I still think I need to review their work, but they can be trusted to get the job done their way.

Apparently I'm not the only one and this is a good blog entry on software teams and some of the issues with managing developers. In the post Jeremy Miller talks about the need for teams to work together with great communication and not be bound or regulated by external groups that just want things to flow a certain way.

It's probably a lot of common sense, but it's really good advice that you have knowledgeable people within and outside of the development team. You include people in discussions about architecture or implementation, you invite collaboration and you trust your people.

Trust.

That's the big word and despite the fact that many of us say we trust our teammates, too often we just do something outself rather than ask or expect someone else to do it. Or trust that they will.

We may say it's because it's quicker, we don't have to explain it, we were already working on something similar, or many other reasons. But often it's that we trust ourselves more than others.

That's understandable, but it doesn't build a strong team for the long term. Working with others, letting them make some mistakes, maybe even take a little longer than you would, will pay off over time as you learn to trust each other, count on each other, build that bond that only a team that has all members working as one unit can achieve.

And achieve a synergy that produces from work from the team than would come from the sum of each individual's efforts.

Steve Jones from SQLServerCentral.com

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

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.

• Video Podcast - 18.5MB Quicktime
• Video Podcast - 8.5MB WMV
• Audio Podcast - 3.2MB MP3

Today's podcast features music by Joe Sibol. If you like it, check out his stuff on iTunes or at www.joesibol.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.

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

Today's Question:

Halloween protection in database systems has to do with which type of T-SQL statement?

By Steve Jones

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

This question is worth 7 points in this category: Design and Theory. 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 T-SQL Recipes: A Problem-Solution Approach

SQL Server 2012 T-SQL Recipes is an example-based guide to the Transact-SQL language that is at the core of SQL Server 2012. It provides ready-to-implement solutions to common programming and database administration tasks. Learn to create databases, insert and update data, generate reports, secure your data, and more. Tasks and their solutions are broken down into a problem/solution format that is quick and easy to read so that you can get the job done fast when the pressure is on. Get your copy from Amazon today.

I have the following table

CREATE TABLE #Users1(UserId INT IDENTITY,UserName VARCHAR(8),Sales Decimal(6,2))

Which contains the following data:

UserId     UserName      Sales

1          Joe           100.00

2          Baker         700.00

3          Charlie       400.00

4          Able          800.00

5          XRay          1000.00

6          Easy          50.00

I then execute the following T-SQL statement

SELECT UserId,UserName,LEAD (Sales,1) OVER (ORDER BY Sales) sales FROM #Users1 ORDER BY SALES

Select the 2 answers that apply

By bitbucket

Answer:

• Sales value for User Easy does NOT appear in the results
• In one row the salesgoal is returned as a NULL value

Explanation: From http://msdn.microsoft.com/en-us/library/hh213125.aspx

Accesses data from a subsequent row in the same result set without the use of a self-join in SQL Server 2012. LEAD provides access to a row at a given physical offset that follows the current row. Use this analytic function in a SELECT statement to compare values in the current row with values in a following row

» Discuss this question and answer on the forums

 Inside the SQL Server Query Optimizer

This book 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. Grab your copy today from Amazon!

Last Updated Data

Aadhar Joshi from SQLServerCentral.com

This SQL query gives you last updated date time for database tables. More »

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

SQL Server 2005 : Administering

Get job list - Need a query which gets me the list of jobs which is scheduled to run on a particular day and...

help with this query!!!! - My goal here to apply the condition to the query while joining the table instead of doing it at the...

SQL Server Maintenance Solution SP doesn't seem to work as expected - Hi, I'm trying to figure out how to use this IndexOptimize SP I got from http://ola.hallengren.com/ to run against just a...

IS (with nolock) is useful in (multiple join update) Query!! - hi all, IS (with nolock) useful in (multiple join update) Query!! below is an raw example of the update statements. UPDATE xx set...

Best Practice for Database Owner? - I have a SQL 2005 server with a dozen databases in production use. I'd like to know what is recommended...

SQL Server 2005 : Business Intelligence

How do I know - SSRS - I believe if SSRS has been installed/enabled on my server, I can find out be looking in the sys.databses table...

Installing BIDS 2005 and SSMS 2005 on Windows 8 - I know Windows 8 doesn't support SQL 2005, but has anyone tried to install BIDS 2005 or SSMS 2005 on...

SQL Server 2005 : SQL Server 2005 General Discussion

sqlncli.msi of 2005 will support sql server 2008 R2 Express edition or not - Will sqlncli.msi of 2005 will support sql server 2008 R2 Express edition or not?

Testing Secondary database in Log Shipping - Our customer wants to test all secondary databases in our Log Shipping environment. Since the test require writes/updates, I will...

Invoked Schedule Id of the Job : Urgent! - Hello: When a job step is being executed from SQL Server Agent, I need to determine the "schedule_id" that invoked the...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Group membership results in slow query result - We are running a client application from which the query gives back results very slow or it times out if...

SQL Server 2005 : T-SQL (SS2K5)

try..catch and union all - Hi all, I have the following construct: [code="sql"]select name, value from ( select name, value from view1 union all select name, value from...

Job Instance ID - Does anyone know if there is a function [like getdate()] that you can use to get the Job's instance ID,...

Insert into stmt with parameters - Hi, I have been checking all over for this solution and I know the follow: Server SQL will not reference ms...

SQL Server 7,2000 : Data Corruption

MDMP files - Hi, In one of my production server, am seeing many MDMP files created on Oct 24. I just refreshed my mind...

SQL Server 2008 : SQL Server 2008 - General

combine 2 rows into 1 row - Hello All, I hope someone can help me out here. Currently my script will bring back 2 rows, all the fields in...

finding char based on ascii value in Ireport - Dear All, How to find a ASCII characters based on the ASCII values in Ireport .. Like in Crystal Report Chr(33)...

Memory Pressure - Hi, We have Production server win 2003 with Sql server 2008 running on it. Ram --4GB. We have performance issue for a database...

SQL Log file - I have 2 log files for a database(D and E drive) and i need to delete one which is in...

Speed up very slow sql database - I have a 1.2 GB database, and it is responding very slow. Is there steps i can take to speed...

Online Trainings & Placements - Radappz - online IT training Radappz offers courses in Java/J2EE, Weblogic, .Net, SAP BO, SAP, SQL Server, Oracle, UNIX, BA, PMP,...

Why backup size is suddenly grow - Why full backup is growth and then back to normal size wht is it? Time BackupSize 17:15 84gb 23:56 255 gb after...

sql query pie chart - CREATE TABLE [dbo].[Acct_sum]( [Acct_no] [varchar](7) NULL, [Total] int null ) insert into Acct_sum(Acct_no,Total) values (11,100) insert into Acct_sum(Acct_no,Total) values (12,200) insert into Acct_sum(Acct_no,Total) values (13,10000) insert...

show/hide parameter tab in ssrs 2008 - Hi all, Why does the parameter tab gets hidden automatically when we perform an action from one report to another. Say,...

Query CPU Performance - Hi there, We have been having issues with CPU performance of a new server when running queries. The worst example was...

Implementing SQL SERVER 2008R2 in a clustered environment - We are currently running sql server 2005 in a clustered environment. We have three nodes with only one node active...

does temp tables are created unique for each sp call. or will it make a queue on server ? - Hi, I am working on a software in which there a SP which is called very frequently, all most all users...

Replication process - Hello All, We have a replication job which was configured some time back.I could see some tables sysarticles,syssubscriptions,sysreplservers related to the...

Reporting Services Service - Can reporting services service and the Reporting dbs reside on different servers. I'm hoping the answer is NO.

similar function in Ireport like IIF in Crystal Reports - Dear All, Any function available in Ireport which has same functionality like Crystal Reports IIF Function.If its so then tell...

SQL query help - Hi, I had posted the query earlier but did not get any response so I have no option but to post...

While Import data into Sql server 2008 from excel sheet - While Import data into tables(in Sql server 2008) from excel sheet i got this error - Executing (Error) Messages Error 0xc020901c: Data Flow...

Suspended Queries - Hi Team, I am getting blocking in one of my sql server. I can query which is suspened causing the block. It is...

Use of IIF function in Crystal Reports - Dear All, I wish to know the use of IIF function in Crystal Reports with some example .please anyone help...

How would you convert this date in SSIS or after lodaed in database 2007-02-20 05:20:36.449923 - Have google a lot of different formats and I must be missing something. How would one look to convert this...

SSAS 2008 R2 Deployment from Visual Studio 2008 - When regularly deploying an Analysis Services project from Visual Studio to Analysis Services 2008 R2, the database must frequently be...

How to give a user ONLY permission to a view, and nothing else in the database - I need to give user Joe select permissions to a view. The view has columns from two tables from the...

SSRS 2008 R2 page break after every 4th person in group - Hi Everyone, I need to add a page after the 4th Patient in a report. The report has two groups. Organization and...

create table variable dynamically - Hello Friends. What is the best way of creating table variable dynamically? Thanks & Regards,

How show datetime select only in Sql Server? - I need show datetime when I select datetime only. Because I try to run SQL but show all datetime. [b]Table Emp[/b] EmpNo...

Help with slow query - I have a query that is taking 1 minute to complete. I would like to imporve its performance. I have...

100 Most famous interview Questions and Answers - Please go to the following link:- http://sqlcheatsheet.wordpress.com/2012/10/26/sql-server-2008-2008-r2-cheatsheet/ Download the PDF (sqlserver2008r2_cheatsheet_v1-01.pdf). Password: harinam This is very good document for the freshup your memories...

Unable to connect SQL Server using AD group access - Experts i needhelp on this : I am using SQL Server 2008,we have Active Directory groups created and added to database.My...

Question on Lock wait types - Hello Everyone, Our production server experiences lot of blocking and locking from application. I have attached the top waits from...

Do not wait for Query to completed - Hello Experts, I have an application that does many things, at the end, it calls a stored procedure to perform some...

Hexadecimal to Binary Conversion in SQL Server 2008 R2 - Hello, I am using the following code to try to convert my hexadecimal string to binary format: [code="sql"]DECLARE @MYHEX AS VARCHAR(MAX) SET...

Automatic backup task - I recently completed setting up what I thought was a pretty good system for performing automated backups, but I can't...

Stored procedure execution from within a data retrieval select statements. - I have a stored procedure that calculates a quarter number. It accepts an input date and returns a quarter number...

Connot connect to server via name but IP works, not a SQL Browser service issue :) - Ok, here is the issue and my steps to troubleshoot: I can connect to my SQl server using SSMS by IP...

Difference between Profiler and SSMS - Hello, I run a profiler trace to identify bootleneks. I found a stored procedure which have many reads, but when I...

Where clause on indexed column using a variable - I have a SQL 2008 R2 table with a few composite indexes and a few single column indexes. I've narrowed...

How do I join tables, but select top 1 from 1-many tbl? - Essentially I have a Contact table and a History table. I'd like to get the join the tables, but only show...

Missing Index - Hi I'm currently trying to dig around to tweak the performance of our database. One of the tasks being removing unused...

Can't browse databases in SSMS 2008 - Walked in this morning and was able to log into our cluster just fine but when I try to expand...

2005, 2008, 2008 R2 on a cluster - Hey all, Has anyone installed R2 on a cluster already running 2005,2008? Unfortunately I don't have a test environment and I'm...

SQL Server 2008 : T-SQL (SS2K8)

char(??) vs varchar(??) - Hi all. I have a database with over 200 tables. They all use char(xx) instead of varchar(xx). I heard that...

Script Needed to pull the data - I need to pull the data from two tables called Table1 & Table2. Table1 has following columns: ServerName ApplicationName ApplicationID Table2 has...

derived column - hi, this is my derived column expression , SUBSTRING([Column 0],FINDSTRING([Column 0],"PAT",1),FINDSTRING([Column 0],"~",1)) for below line TH*4.2*857463*01**20091015*1045*P**~~IS*7564* PHARMACY~PHA*1234567890~PAT*MA*06*987544****SMITH*JOHN****1234 MAIN ST**SOMEWHERE*MA*54356**19500101*M*01*01*INDIA**BURGER~ my result...

A maths puzzle for anyone up for a challenge! - Hi, I've been given the task of creating a single unique numerical value from two composite unique alphanumeric values. It needs...

Get last row based on duplicate column - I have a table that contains: [u]userid email name[/u] 1 a@a.com Bill 2 a@a.com Billy 3 a@a.com William 4 b@b.com John 5 b@b.com Johnny 6 c@c.com Jimmy 7...

Alphanumeric number generation - Hi, I need to write a SQL query to print the following aphanumberic sequence in SQL 2008. 0001, 0002, ... , 0009, 000A, ... , 000Z,...

Is there an accurate script for datediff format in years:months:days? - I've tryed several tsql scripts and none of them have proven to be accurate. If you got a neat script up...

SQL Server 2008 : SQL Server Newbies

Sum sales for current month - Hi All, I'm trying to sum the sales where the date parameter will automatically reference the current day/ month, which I...

How to import data from .csv file into a table in sql server using bcp in/out - Hi experts, I have a .csv file from which i have to do bcp out and bcp in, in command prompt...

MAX Function Problem - I need to query a table that has three columns 1st RecordID (Primary Key, Unique), 2nd ItemID (non-unique) and 3rd...

SQl Joins - Hi, I am new to SQL. I have question regarding joins. Let us say i have 3 different tables Table Workplace Workplacecode (PK)...

Multiple Rows in One Resultset - Hi All, Let's say I have a table like the following: row1 row2 row3 row4 0 6 0 0 0 0 30 0 4 0 0 0 0 0 0 18 but I want to return a resultset like: row1 row2 row3 row4 4 6 30 18 Does anyone have any...

connecting sql server over the internet - Hi all, i'm new to sql server 2008 and i would like help with connecting two pc's over the internet i've...

Adding record to db using stored procedure and identity counter. - While I've been using MS SQL for years, never really learned it. Using test environment SQL 2005, Windows 7 but...

SQL Server 2008 : Security (SS2K8)

Permission question - Suppose there is a tableA and trigger defined for Insert, Update and delete to populate audit table say tableB. Now...

Move logins from one to anothe - I would like to move a database from one server. In this move, I need a script to copy all...

sql server permission - I have a question about setting up permissions on a sql server 2008 r2 datbase. I basically wrote and enhanced...

SQL Server 2008 : SQL Server 2008 High Availability

Database Level Clustering in SQL 2008 - Hi Techs, is it possible to have DB level clustering in sql 2008? if so how can we achieve this? I...

Principal Disconnected, In Recovery... on both servers after restart - Hi, today I had the problem, that after a restart of my two mirrored database servers on both servers all databases...

SQL Server 2008 : SQL Server 2008 Administration

Question on CDC enabled DB - Hi All, I had an issue of log space running out of space. So as master Gail as suggested, I...

Performance question - Would there be any slowness in performance if a query involves dateadd(day,datediff(day,1,GETDATE()),0) in the WHERE clause?

sp_helpdb and sys.databases inconsistent for user_access - **moved from data corruption forum** The Master database on one of our servers is showing in SSMS as Single-user mode. I...

SQL Server Agent will not start after upgrade - Yesterday I upgraded a Windows Server 2008 R2 server from SQL Server 2005 SP4 to SQL Server 2008R2 SP2 in...

SQL Server Consolidation (25 servers) - Our company plans to consolidate SQL Servers. Currently there are about 25 servers. They try to minimize this number to 5-6....

EMC RecoverPoint and SQL Server - We are trying to implement EMC RecoverPoint with SQL clustering. What we had to do at the DR side after syncing...

Career : Certification

Join or Form Study Group --> MCSA >> MCSE SQL SERVER 2012 - Hi, I am interested to join an existing or form a new study group aiming toward certification in SQL Server 2012. The...

MS Training material for 70-457 and 70-458 (Upgrade MCSA Sql 2012) - Hello, I'm planning to upgrade to an MCSA 2012 by taking 70-457 and 70-458. Does anyone know of any good resource material...

70-462: Can the Hyper-V environment be downloaded somewhere? - Hi all, When I just opened the 70-462 TK I saw a six server Hyper-V environment is neccesary to complete the...

Would using the MS Press books be sufficient? - After being an MS SQL DBA for over a decade I'm finally looking at getting some certifications. Unfortunately though it'll...

Programming : XML

Returning multiple rows from XML - Greetings, I am new to XML and am struggling with shredding an XML column into a tabular format. Here is what I...

Programming : Powershell

Scrip to connect to remote windows box to get SQL version - Dear All, I am very much new to power shell scripting where i need to find out what version of SQL...

SQLServerCentral.com : Anything that is NOT about SQL!

How's this for a Job Description - So I recently got this job description in the mail and at first glance it looks pretty good, but at...

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

Sum with condition not working - Hopefully someone here can help me solve this problem. What I have so far is a list with two tables in...

Data Table Below Graph - Does anyone know if it's possible within Reporting Services 2008 R2 to automatically display a data-table below the graph like...

Data Warehousing : Integration Services

Upgraded Package from 2k5 to 2k8 with configuration having problems with driver swap - Alright, let me setup the scenario. I have a series of packages that run in a job that we're upgrading...

WMI task and WQL query - Hi guys, I am using a WMI task to watch for files. We get about 20 txt files. when they...