SQLServerCentral - www.sqlservercentral.com

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

Featured Contents

Featured Script

The Voice of the DBA

Access Disdain

Today we have a guest editorial from Andy Warren as Steve Jones is at SQL Bits.

I learned databases back in the days of DOS and DBF files. If you ever worked with dBase III or anything that used the DBF format you wound up with a folder full of various files. I remember trying out Access v1.1 (May 1993 for those who wondered). It was I think the first Windows database I had used and I remember thinking how nice it was to have all the “files” in a single container, the MDB. I went on to use it a lot. I built a couple apps that used it for a data store, I used it a lot for ad hoc reporting and data entry/fixing, and I worked at a company that used it as their primary ETL solution.

Later I moved on to SQL Server 6.5 and 7.0. I remember learning to create indexes in SQL and being struck that I had to give them names - in Access you just entered the columns, no name required. That was a worthwhile tradeoff just to be through with doing the compact/repair operation that seemed to be required all too often in Access.

The strength of Access has been that it made databases approachable and affordable - both important attributes for the beginner or for the very small business trying to find a way to solve relatively small data problems. Access also made it easy to build a data driven “application”, perhaps at the time second only to FoxPro, and perhaps superseded now by Lightswitch, though I haven’t tried it to know for sure. Ease of use matters, something we can see clearly in Excel.

The pain of Access comes when the database gets too big, too slow, or the app just gets too unwieldy. Then we as DBA’s and developers get called in to “fix” the problem and the fix often requires moving data to SQL and substantial changes or replacement to the code along with it. Access. I’ve never minded that work - upgrades and refreshes are a big part of what we do, all across the enterprise. Which isn’t to say I haven’t seen horrible things done in Access that were truly a pain to figure out and upsize.

Maybe it’s because I ‘grew up’ with Access but I don’t share the disdain that many have for it. Access is the logical next step when a user needs to move beyond Excel.They may not normalize as much as we would like, they probably have a lot of cursor-ish looking code, but they are making that first step to working with data. That’s ultimately good for the business and good for us, even if it means we have to step in to do some clean up sometimes.

Andy Warren from SQLServerCentral.com

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

ADVERTISEMENT
SQL Monitor

An accidental DBA? Try SQL Monitor

Use the 30-day full product free trial to get easy-to-understand insights into SQL Server, and get suggestions on how to solve the type of issues that are uncovered. Begin your free trial.

SQL Developer Bundle

10 must-have SQL Server tools

The award-winning SQL Developer Bundle contains 12 tools for faster, simpler SQL Server development. Download a free trial.

Ricky Leeks on Learning .NET Memory Management

Ricky Leeks on Learning .NET Memory Management

Pick up all six free articles in one free download. Find out what Ricky has to teach you about garbage collection, memory management gotchas, and more. Download the article pack free.

Featured Contents

 

Convert Rows into Columns

M Patrick Dillon from SQLServerCentral.com

Convert the rows of a SELECT statement into a predetermined number of columns. More »


 

Developing a Custom SSIS Source Component

Additional Articles from SimpleTalk

SSIS was designed to be extensible.Although you can create tasks that will take data from a wide variety of sources, transform the data is a number of ways and write the results a wide choice of destinations, using the components provided, there will always be occasions when you need to customise your own SSIS component. More »


 

From the SQLServerCentral Blogs - Execution Plan Details

Grant Fritchey from SQLServerCentral Blogs

I wouldn’t say it’s common knowledge that you should look at execution plans when tuning queries, but it’s not exactly... More »

Question of the Day

Today's Question (by free mascot):

Is it possible to change SQL Server's page size other than 8KB? e.g. 4KB, 16KB, 32KB etc.

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: pages.

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

Introducing SQL Server 2014

Get a head start evaluating SQL Server 2014 - guided by two experts who have worked with the technology from the earliest beta. Based on Community Technology Preview 2 (CTP2) software, this guide introduces new features and capabilities, with practical insights on how SQL Server 2014 can meet the needs of your business. Get the early, high-level overview you need to begin preparing your deployment now.

Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Phil Factor):

Is a Primary Key an index?

Answer: No

Explanation:

A primary key is not an index but a logical constraint, to indicate the default candidate key to be used to select particular rows of the table. Any candidate key must be unique, and must not allow nulls.

For a table to be relational, any scalar value must be addressable by specifying the table, column and the key value(s) of the containing row. This key, often referred to as a candidate key, defines a set of one or more columns that together can distinguish individual rows in a table.

The method used to enforce these requirements can vary between implementations of RDBMS. The most obvious method is a combination of unique index and NOT NULL constraint.

Unless you specify a unique nonclustered index to enforce this primary key, SQL Server will automatically create a unique clustered index on the column or columns unless a clustered index on the table already exists. Because the primary key column(s) cannot allow NULL values all columns are set to NOT NULL. You cannot assign an existing index to enforce a primary key. 

See: Primary Key Primer for SQL Server


» Discuss this question and answer on the forums

Featured Script

Fix orphaned users after restoring database to another server

Patrick Akhamie from SQLServerCentral.com

You can add it as a step to the restore job or run it right after the restoer to find and fix orphan users.

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 2014 : Administration - SQL Server 2014

database query need - guys please suggest me what can i do i am new in this field i don't understand what can i...

SQL Server 2014 Eval to Std downgrade warning - I have an instance of SQL Server 2014 Eval installed and I want to upgrate it to the purchased copy...

Online Restore of Databases - Dear All, I'm using 2014 SE. I know a backup of a database doesn't take the source DB offline, but then I...


SQL Server 2014 : Development - SQL Server 2014

Duplicate record count - I have duplicate records in table.I need to count duplicate records based upon Account number and count will be stored...

Generate column numbers using dynamic SQL and pivot command - I have the following SQL which i want to convert to a stored procedure having dynamic SQL to generate column...


SQL Server 2012 : SQL 2012 - General

Sorting calculated members - SSAS 2012 - Hi , I am using SSAS 2012 cube, and in Calculations Pane --> Script View I am trying to "sort" the calculated...

Suggestions on how to improve this View that is using correlated subqueries - Hello everyone, Can anyone please suggest me on how can I avoid using correlated subqueries in the following create view statement:...

best way to send emails - What is the best way to send emails with query results as attachment? There is a stored proc sp_send_dbmail but the...

Quorum connectivity lost on SQL 2012 - Hello all, I have a cluster using Always on on 2012 and i have set up a Witness file share...

SQL Server 2012 SP2 and read-only databases - Hello, I have just install the SP2 for SQL2012 on a server with AlwaysOn. After the update on the replica, in the...

TDE using External Certificate - Is it possible to use the Certificate which is available in the machine for TDE SQL server? if so how...

Linked server behaviour - Hi. I've been wondering about something I'm observing today, and wondered if anybody might be able to explain it to...

SSIS - Dynamic Column mapping - Hi, I am new to SSIS and i got 1 assignment. Requirement: In my destination table i am having some 30 columns...

Can SQL server 2012 BI data tools co-exist with SQL server 2008 BI studio? - After I install SQL server 2012 client tools on my computer that has SQL server 2008 client tools installed, the...

Locking behavior on indexed views - Hey, I have a question regarding the locking behavior of indexed views. We have a 3rd party application and something like...

log write denied when using Maintenance Plan - Hi All Under the maintenance plan, i run full backup at the same path and it was successful. The maintenance plan...


SQL Server 2012 : SQL Server 2012 - T-SQL

Exclude rows where value in column not found in another row - This is a followup to a previous question to a previous but in reverse of Find rows where value in...

Simple Linked Server Query from SSMS to datatable syntax - Hello I have a linked server and when I execute this query, I can see the Database, ex: ( http://msdn.microsoft.com/en-us/library/ff772782.aspx ) Select...

Find rows where value in column not found in another row in the same table - Can't seem to make this SQL query work! I have searched for the answer to this and although some posts...

Cross Tab, UNION or Different solution for this problem? - Hello, I have a requirement to join actual sales data to projected sales data based on periods, quarters and years. Each...

Composite Index performance - Hello, thanks in advance for any help. I have a large table with 500M records. It has a primary clustered...

Calculating working hours between 2 dates. - I Googled around for a while for a solution on this and not finding one I decided to write/share my...


SQL Server 2008 : SQL Server 2008 - General

microsoft.sqlserver.sqlclrprovider version=10.0.0.0 - Hi, I cant able to create new databases from application end. The given below error is getting. [b]could not load file...

Deadlock - I have a procedure, which process multiple documents and finance postings. So when i execute it will take 1 mins...

Read .docx file through SQL - Hi, I need to store word document data into database table .This is not one time process.I dont want to use...

The same index but different fragmentation number - Hi All, I wonder why I got data that consist of the same index name with the same table and schema...

"Business Rules Engine" - Any advice on the best ways to approach the following scenario? - A table consisting of customers; one row for each...

Memory Grants and Varchar(max) - I think I have this straight, but I'm posting here because I want to be sure. I'm advocating moving away...

Data masking in SQL Server 2008 R2 - What is the best way to do Data masking in SQL Server 2008 R2? or use any 3rd party tools? Thanks,

delete large chunk of data on replicated DB - Hi, I have 4 SQL Servers 2008 with a merge replication. Replication runs every 10 minutes. Now, my client asked me to...

Replication woes - Hi. I apologize for posting twice but just noticed that I originally posted to the SQL Server 2005 forum. Maybe...

how to increase resource pool memory - Hi, I have a database server with SQL Server 2008R2 32 bit and Windows Server 2008 32 bit, having total of...

SQL databases info - Hi, I am looking for a query where i can get alll the information for all databases, users, linkedservers etc......

Can't start Analysis Services on cluster after restoring system databases - Hi all, Last night we had a suspected network issue which caused all connections to several of our SQL servers to...

Log files running out of space after rebuild - Hi .... I have an issue after running the rebuild/reorganize index maintenance job based on demand ... the log files grows very...

High Fragmentation Index ... - Hi All, At the moment I am tuning the indexes with high fragmentation ..I created a maintenance plan for rebuild...

Script Help - Selecting last record - Hello everybody! I have the need to bring the past records of certain releases. It is + / - so ... I need the...

SSIS Data Flow "bulk insert" VS. INSERT INTO - We are currently pulling in 1+million rows of data for a monthly process, all records processed in the prior month....

Reporting Services scheduling- hourly between 8 and 5 impossible? - Trying to get a report to run daily between certain hours. I can set a start time and an "end...

Restore Failed Error - Urgent Reply Please! - Hi. I have a database backup file (.bak) taken from SQL Server 2005 Express which was running on a 32-bit Windows...


SQL Server 2008 : T-SQL (SS2K8)

Search Query Including Security - I'm trying to integrate the security settings for our system into the reports and search and its a nightmare trying...

Join 2 table based on Date - Hi forum, I have 2 tables: Table Transaction ----------------- EmpID TransDate 00001 1/1/2014 00001 1/2/2014 00001 1/3/2104 00001 1/4/2014 00001 1/5/2014 00001 1/6/2014 00001 1/15/2014 00001 2/1/2014 00001 2/2/2014 00001 2/20/2004 .... Table Master --------------------------- EmpID...

Replace WHILE Loop with Recursive CTE(?) - Hi Everyone, I would like to replace the code below with a set based solution. Any help is appreciated. [code="sql"] DECLARE @Cat TABLE...

How do I drop the DROP DATABASE ENCRYPTION KEY & DROP MASTER KEY ? - How do I drop the DROP DATABASE ENCRYPTION KEY & DROP MASTER KEY ? I tried but I was unsuccessful.

Use a Like in a Replace Cast sentence? - Hi, i would appreciate suggest the SQL sentence, thanks CREATE TABLE [dbo].[instructions]( [site_no] [int] NOT NULL, [instructions] [text] NULL ) Select top 3 * from instructions Output Site_no...

Datetime conversion issue - Hi: I would be very grateful for your help with the following query. All source and target date fields are defined...

Encrypt SSN Example (TDE) - Does anyone have an example on encrypting a SSN Field using TDE? Any help would be greatly appreciated. Thank you,:-)

how to write a proc to upload the xml files into above tables - Hi There are two tables in my database HotelList_Temp, AsiaHotelChildrenAges_Temp I need to write a proc to upload the xml...

SQL Query Help needed - Thank you. - Hello Folks, I'm in need of your kind help here, I have two tables listed below, the first table has employees...

Group by performance FK / PK - Hello all, I have something I can't explain, hope some of you can? We have a medium sized database with the next...

Replace a Cursor - Hi All Could anyone help me on getting the below Cursor query convert to a Recursive CTE or with a while...


SQL Server 2008 : SQL Server Newbies

create tables and database - I am trying do the below. I can create simple table like in steps (a) and (b) but not sure...

daily report - I have to supply a daily report. I have written the correct query in T-SQL. Is there a way to...

Copy Data Base - Hello, I am currently trying to copy our test db from a virtual machine across the network to my local instance....

notify when a particular field changes - Hi everyone. I need to be able to list a record set where a particular field has changed, I've written...

not able to attach Database. getting file path error - TITLE: Microsoft SQL Server Management Studio ------------------------------ Attach database failed for Server 'xyz'. (Microsoft.SqlServer.Smo) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.2218.0+((SQL11_RTM_GDR).120612-1250+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&

Microsoft Query - I know this is the wrong forum to post but I have no idea where to post and this is...


SQL Server 2008 : SQL Server 2008 High Availability

truncating the log file with a log shipped enabled database - When log shipping is set up, are the log shipping backups then same as a standard backup and log backups? I.e....

Automatic Mirroring fail over and see Login failed errors in error log of current Mirror server - Hi, We have high safety with automatic failover setup in our Sql Server 2008 R2 environment, and automatic failover is functioning...

Re configure mirroring question - My Database mirroring went to suspended mode and log kept growing bigger. Now I am trying to remove mirroring and...


SQL Server 2008 : SQL Server 2008 Administration

Moving a database from sql server 2005 to a new VM - Hi Team, Currently we have a database in SQL Server 2005. Decided to move that DB into a new VM where we...

list every object on a data file - Inherited large database with over 26 data files. Looking for a query to show what objects are on which files....

Backing up a DB with log shipping turned on - I have a Db which has log shipping turned on. If I create a separate maintenance plan to do a...

Adding secondary file path - Hi, I want to apply this changes to my database.can anyone help me!! DECLARE @name1 VARCHAR(50)---Instance name DECLARE @path VARCHAR(50)----path for temp db DECLARE...


SQL Server 2008 : SQL Server 2008 Performance Tuning

Lost Execution Plan - My application's database has a single stored procedure (out of hundreds) that seems to loose its "good" execution plan weekly....

Force Parameterized Queries vs Missing Indexes - Working with a third party that would like me to enable the server option Force Parameterized Queries on MS SQL...

Checking the Performance of my SQL Server 2008 R2 - I am asked by my management to support my case saying my SQL is performing good, I tried to run activity...

confusion with DMV sys.dm_exec_procedure_stats execution_count database id - Hi, we have a separate database for each of our clients. All the databases are identical meaning they have same...

Working with large database tables - We are in the process of getting some converted data from a mainframe system into SQL server into our accounting...


Cloud Computing : Amazon AWS and other cloud vendors

AutoScaling SQL Server in EC2 - I am new to AWS, and I would like to understand how SQL Server autoscales on EC2. We are considering...


SQL Server 2005 : Administering

SQL server drive configurations on SAN - Use to be that you broke your database files out to different drives, even on a SAN. D: for data,...

database log file path and database data file path - Hi, In witch table can i find the information of what is the log file and the data file path...

where my Master Key and certificate is stored. - I want to understand today where my Master Key and certificate is stored. Can anyone help SELECT * FROM SYSOBJECTS WHERE name...

Replication with errors - Hi All, I run into problems: Error messages:


SQL Server 2005 : Backups

list of scheduled backup paths - Does anyone know of a way to list out all of the directory paths for backups that are currently scheduled?...


SQL Server 2005 : Business Intelligence

Custom SSIS sql task failed - I am developing a custom SSIS task for running sql task. But it fails with error - I'm trying to set...

SSIS help in deriving negative values - Hi, I have a requirement where from a string we need to derive the amount and also based on the alphabet...


SQL Server 2005 : Data Corruption

DBF corrupted database - Hey friends, I'm in great trouble here. I'm using Microsoft Visual FoxPro 9.0 on my Windows 7 machine. I had...


SQL Server 2005 : SQL Server 2005 General Discussion

1 Publisher 2 Distributor - Hello, I recently joined new environment and observe that one of the SQL Server is having Merge Replication Configured on SQL...


SQL Server 2005 : SQL Server 2005 Integration Services

Importing Poorly Formatted Text File - I am trying to import a text file to a SQL server table using SISS. I use SSIS but this...


Reporting Services : Reporting Services

SSRS Parameter issue (SQL Server 2008 R2) - Having an issue where I have created a report with a shared data source and several shared data sets. The...


Reporting Services : SSRS 2012

Export to PDF - fields do not increase as needed - I have a report with a bunch of text boxes that can be populated by paragraphs of info. Every text...

Report with header and detail data on differing lines - Hi. Apologies if this is so basic, but having scoured the net, I've been unable to find out how to...


Programming : General

location of '-' in the comments Please Define Or Explain It To me - Can anybody please help me on how to define the location of '-' in the comments? For example, I have a comment...


Programming : Powershell

Using a Helper Library from a powershell script that will be running via a SQl Agent job - Hi I am having a Powershell Script, [b]A.ps1[/b] that calls some functions from a Helper script [b]HelperFunctions.ps1[/b]. In order to...


Data Warehousing : Integration Services

ADO.Net Source in ssis 2005 - I dont see ado.net under Data flow sources in ssis 2005. How do i bring this source? Help

ERROR when Executing a Job within SQL Server Agent from an SSIS package. SQL Server 2012, Visual Studio 2010 - Hello, I created an SSIS package within Visual Studio 2010 that takes data from cURL, exports to a XML file...

Control Flow: selectively consuming multiple source files - I've got to find a way for my foreach loop container to recognize the difference between file1_2014_06_20 file2_2014_06_20 file3_2014_06_20 and file1_2014_06_21 file2_2014_06_21 file3_2014_06_21 then import just...


Data Warehousing : Analysis Services

MDX format date expression - Hi, could someone please assist a MDX novice with formatting the column values to "yyyy-MM" for the following MDX that...


SQLServerCentral.com : Anything that is NOT about SQL!

Fantasy Football 2014 - SQLServerCentral would like to host a Fantasy Football league again in 2014. As of now, we'll run this league the...

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


SQLServerCentral.com : SQLServerCentral.com Announcements

Issue with Oracle-SSIS integration - Hi, I'm trying to write the table name and their count of an oracle database. so i hard coded all the...


SQL Server 7,2000 : Administration

sql server 2008 not supporting sql server 2000 - Hello this code ok in sql server 2000, but in 2008 INDEX SELECT BUDEPT.DEPT, Dept_Description.DEPT_NAME FROM Dept_Description (create index =PK_Dept_Description) INNER JOIN ...

Looking to bakcup and restore application roles - Is there a way to backup and restore application roles from a SQL server 2000 instance and restore to another...


SQL Server 7,2000 : General

BCP and remote paths - Hello there: Does anyone know if BCP can write to a remote location? I mean in a server different to the...


SQL Server 7,2000 : SQL Server Agent

Scheduled Job fails with Process Exit Code -1073741819 - Dear all, I'm at my wits end with this problem, so any help/advice is greatly appreciated. Random steps within scheduled jobs are...

Agent startup error - Hi, I've got a really strange problem with SQLAgent. I get the following error when trying to start up SQLAgent (SQL...


Career : Certification

How to break into data warehouse management - Hi All, I'm hoping I can get some valuable input from people in the field. My current and past jobs have...

70-462 Trainning Kit: Cannot connect from SQL-A to SQL-CORE - 70-462 Training Kit : Cannot connect from SQL-A to SQL-Core Currently, I have set up lab environment on my lap top...

This email has been sent to {user_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 ©2014 Red Gate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com