The SQL Internals Viewer

Danny Gould from SQLServerCentral.com

Danny Gould has written a very interesting piece of software that gives you a view into the storage of your data inside SQL Server. And it's FREE!!! He brings us a description of this software in this short article. More »

SQLBits Community Day

Press Release from SQLServerCentral.com

If you're in the UK in October, you might want to sign up for the SQLBits Community day put on by MVP Simon Sabin. More »

Accessing and Updating Data in ASP.NET 2.0: Accessing Database Data

Additional Articles from 4 Guys From Rolla

This article shows how to use the SqlDataSource and AccessDataSource controls to query data from a relational database.More »

SQL Server 2005 Express Edition - Part 4 - Surface Area Configration

Additional Articles from Database Journal

This article demonstrates how you can use Surface Area Configuration utility in order to modify some of the security-related settings that have been applied during installation of SQL Server 2005 Express Edition.More »

One Year Ago - Monitoring Drive and Database Free Space

Mark Nash from SQLServerCentral.com ( published: 08/25/2006)

I maintain a few blogs, well I maintain a personal one. The one here at SQLServerCentral.com doesn't get so much work, but I'm trying to get better at that. It's hard for me since this editorial is almost like a blog, so finding a whole separate set of things to write about, and then separate them from articles, is tough.

Lots of companies these days are looking to get their employees to blog, with various degrees of success. Some want the exposure, some want to show off, some think of it as another marketing channel, and some just do it because everyone else is.

However it's not that easy a thing to maintain for a company. I follow lots of blogs and I've seen so many die after a few months because people don't want to be bothered to write. I've got almost as many dead blogs to watch as I do live ones. Thank goodness for Google Reader to allow me to only see new items.

But allowing everyone to blog and express themselves doesn't always work out well. I saw this mention about Google and some employees that write something about the movie Sicko. I could see what the employee was trying to say, but it didn't sound good. And it makes you think that Google puts their profits ahead of the "right" thing to do by supporting companies that we feel aren't being fair with us.

There's also the side of things where bloggers talk about all types of things and get some benefit for their employers because more people read the blog. I guess that's what I kind of do here. I call this an editorial, and it is, but it's also a blog in some ways. It also keeps me busy enough that I tend to neglect my SQLServerCentral.com blog.

There are many thoughts on successful blogs and how to build one for your company. I think that can be good for the company, but what about you personally? What happens if you leave the company? Is your blog lost forever? Most likely it will be, slowly dropping out of the massive cache that Google maintains.

My advice has always been for friends that they should have a professional blog, but keep it professional. No stories about your cats, dates, etc. I know I blur the line here, but I think this forum is a bit of an exception.

My new advice is as follows: keep 3 blogs if you have a corporate blog.

Sounds like a lot of work, but really it's two blogs giving you some disaster recovery. First keep a personal blog if you want. Actually just set one up and put stuff in there that's not related to your career. Put the stuff in there that you wouldn't want someone to ask you on your next interview.

I think it makes some sense to have a corporate blog if you're asked. Use it to put out interesting technical things related to your job or company. But don't expect this blog will get you your next job. It probably works for Raymond Chen and Linus Torvalds, but for most of us this won't help. And you don't want to lose your great technical posts, so set up a third, professional blog for your career. Copy over those posts you want as a part of your online career CV and reference them when you are looking for a new job. Give out this blog to recruiters, interviewers, etc.

You should easily know where a new post should go and try not to mix your professional and personal documentation. Write a new SQL CE app? It could go on all three blogs, but your dog munching on your shoes definitely should only be in one place: the personal blog.

Steve Jones

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

» To submit a tip, rant or editorial, email articles@sqlservercentral.com.

Today's Question:

If you execute this in SQL Server 2005's standard AdventureWorks database, what happens?

select 
	Identity(smallint, 100,1) as ReportID 
	, c.AccountNumber
	, h.SalesOrderID
	, h.OrderDate
	, h.TotalDue
 into Sales.CustomerReport
 from Sales.Customer c
	inner join Sales.SalesOrderHeader h
		on c.CustomerID = h.CustomerID
 where h.SalesPersonID = 279

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

This Question is worth 1 Point(s) in this category : T-SQL. We keep track of your score to give you bragging rights against your peers. If you have problems with submitting you can go to the QOD section inside the Test Center directly at http://www.sqlservercentral.com/testcenter/QOD.asp. We'd love to give you credit for your own question and answer. To submit a QOD, simply send the question, answers and explanation to articles@sqlservercentral.com

Today's Question is sponsored by:

SQL Professional Gift Pack for August

How can you find out how your buffer cache is being used for which types of objects?

Answer: Check the sys.dm_os_memory_cache_counters view.

Explanation: The sys.dm_os_memory_cache_counters view will show you a list of types of objects that are in cache along with the total size and object count.

Ref: sys.dm_os_memory_cache_counters - http://msdn2.microsoft.com/en-us/library/ms188760.aspx

Here's a few of the new posts today on the forums. To see more, select Discussion Forums in the Community menu on the site:

   Cannot login to server using SQL server authentication - I have a new installation of SQL Server 2005 running on Windows Vista Home Premium Version 6.0.6000. I have never been able to login to the server using SQL Server Management Studio using SQL Server Authentication. I can get ....(more online)

   SERVER PROB - Hi ALL,My server completely crash, but database restore all data in any other what are the steps we have follow plz telThanks subb....(more online)

   SQL Server 2005 Linked Server to Oracle - Ok people might see this post and think it is the same old thing about not being to delete rows from Oracle, but it is a little more complicated than that.  Nothing I have seen posted anywhere matches what we are having ....(more online)

   DATABASE PROB - Hi,My database is using nearly 150 users today morning some of the users are complaing my database performing very slow result & respose.How can increase the my database Performance what are ther steps i can follow to increas....(more online)

   sql job took very long to run - a job that is supposed to run for 1H. It took 4 hours to run during the weekend. any way to find out what made it to take 4 hours to run?It has never happened this before. Or maybe just once or twice during the last years.....(more online)

   Relational data into Excel by HTTP (like AS2005 data into Excel by HTTP) - Hi,It?s possible to get data in Excel 2003/2007 pivot tables by connecting to Analysis Services over HTTP as described here:http://www.microsoft.com/technet/prodtechnol/sql/2005/httpasws.mspxI tried it and it works fine.I?d l....(more online)

   Default Parameter Value in the Reporting Services - Does somebody have experience with reporting services? they are just killing me with their non-flexibility. For the report I am making now the request was not to do it in ASP or .NET but in reporting services w....(more online)

   Incremental Update on Very Large Table - We have a table with over 5 million rows ( soon to be over 20 million) that store nodes and the distance between them. Create table T1 ( colA int , colB int, colDist float ) These values are re-calculated every hour....(more online)

   Unusual performance issues - Hi, guys.This question (or something similar) may have been posted before, but I'm having difficulty using the search function on the site. It returns one page of results (claiming that there are six pages in total), but when....(more online)

   Indexed Views - EXACTLY what is stored... - Hi -I'd like to use some indexed views but am wondering exactly what gets stored.   Is it just the columns in the view which are indexed or is it all columns in the view ?  Stored meaning that all art....(more online)

   SQl Server on different VLANs - Is it possible to use linked server for a SQL Server that resides on different VLAN that is in the same domain?....(more online)

   Iterate over files in correct sequence according to creation date ?? - HiI'm using a ForEachFile iterator to iterate through a bunch of files in a specific folder. I need to make sure though that I iterate through the files in correct order - starting with the oldest one and ending with the last....(more online)

   Import Data from MS Access problems - Hi,First time user of SSIS....Am copying data from MS Access to SQl Server.The total number of rows in the destination was less than the source, so I output the error rows to a text file.The data in the rows looks okay, but t....(more online)

   Reaching ForEach variables from script task ? - Hi,I have a ForEach loop container that iterates over a set of flat files in a specific folder. For each iteration, I would like to include a script event that checks the first character in the file name. I have created an Ev....(more online)

   Table list in a package created by Import/Export Wizard - Newbie to SQL2005 and SSIS needs help....I created a SSIS package using the Import/Export wizard from Management Studio.  I now need to edit that package, specifically needing to remove some of the tables from it.  ....(more online)

   How do I allow remote access via an SSL certificate? - We have a few clients that needs to access our SQL database every so often. Currently we're using an IP security policy to block access to port 1433 except from permitted IP addresses. The only problem is, some of the clients....(more online)

   Joining Tables in Different DB''s - Does anyone have any ideas on how to join tables in different DB's without the possiblility of linked servers or replication? I am looking for something along the line of a Data Access Layer in .Net. ....(more online)

   Unable to generate user instance - I have an intel mac mini with Parallels to run XP Pro, plus i i have also tried the following on my P4 IBM laptop:I am learning VB and SQL so i'm very new to everything. I've got visual studio express, also visual web develop....(more online)

   SQL Express Reporting Service - I am trying to setup a Reporting Service instance using WinXP and IIS 5. When connecting to http://localhost/Reports/ I receive a metadata error. What can I do to fix this error?....(more online)

   SQL Server Express with Apple Intel - Has anyone had experience with using SQL Server Express on an Intel Apple computer? I'm in the process of designing an application for a friend's company and I'm currently stuck using Access but would like to utilize SQL Serv....(more online)

   SQL Express on MAC - I am using Virtual PC on MAC OSX (Power PC). I have Win2K3 running with IIS and SQL2000 running. Tried to Install SQL Express but it fails to start. Does anyone have a suggestion on how to get this to work?....(more online)

   DATABASE PROB - Hi,My database is using nearly 150 users today morning some of the users are complaing my database performing very slow result & respose.How can increase the my database Performance what are ther steps i can follow to increas....(more online)

   Major ideas needed - upon working for an organization that supports over 100 instances of SQL (7,2000, and 2k5) All of each type was installed on one single server license. Is there a way to go inside of these servers and find the product key to....(more online)

   Restoring Master Database - Hello All,I'm trying to restore the master database (SQL 2000 SP4) from a backup to a totally different server with different path structures. When I do this, I'm not able to bring up the database instance. I know what the pr....(more online)

   Tables to remove a publication - I restored a db over another db with some replication setup (test environment!). Then I went to add a new publication, but it said the name already existed. Under replication the publication is still lsited but it will not le....(more online)

   Reducing reads question - I'm trying to insert all the rows from a table to a new table.(insert A select * from AA)The reads on Profiler shows ar really high value (10253548).First I created a unique clustered index and the reads shows (3258445), then....(more online)

   How to change Unicode comparison style - Hello,I am trying to restore a database on SQL 7.0 using the datafiles from another database. I detached the source and target database then copied the datafiles on operating system level. The source has been reattached witho....(more online)

   How do I do I do a multi table update? - I am kinda a new at this and need some help.  I hope this is a simple question.  I know how to update a single table, but now I want to update two tables at the same time and am unsure how to proceed.  Here is ....(more online)

   presenting 25 sorted records from table with 150,000 records - I have a client table with 150,000 recordsfor presenting clients on a form (top 25) i use a procedure"select top 25 * from Clienttablewhere ClientName>=@nameorder by ClientName"with big over 50,000 records the actoin ....(more online)

   Creating view - Join on date - I am new to views and have create some straight forward ones.  I have a set of files I am tying to include in a join.  The access to the view will have a specific date, as the program output is date related.  O....(more online)

   Query suddenly runs slowly - I am having a performance problem with a step within a stored procedure that has been running reliably for over two years but has recently slowed to a crawl. This bit of code ran in a few seconds up until about two weeks ago.....(more online)

   NT Authentication - Can I Change Credentials? - I have inhereted a SQL Server 2000 application with an Access 2003 front end.  Currently users are using SQL Server authentication and that works.  Due to corporate policy I need to switch them to NT Authentication.....(more online)

   Sqlserveragent service started and then stopped - When attempting to start SQLserveragent service, then servie starts and then stops issuing an informaitonal message "SQLServeragent servie on Local Computer started and then stopped.  Some services stop automatically if ....(more online)

   Column Extraction - There are around 40 columns in my table. I want to get all the table data excluding a particular column; without mentioning all the column names.  I used following code :declare @sql varchar(2000)select @sql=''Select @sq....(more online)

   RunDTSPackage - The situation:We have 2 servers, one for web and one that runs SQL Server 2000. Last week our server that runs SQL Server crashed and we had to reinstall the OS and all. We recovered from this and lost a few days of data but ....(more online)

   How To Get this done - Hi,I'd like to get "best practices" methodology. I have an invoice and line items from two tables in one database. I now have to merge the invoices and line items into a temp table. My question what would be the best appro....(more online)

   Search DTS for string - Hello. Does anyone know of a way to search DTS packages for specific strings? I've come across a couple of cool grep scripts in the past but neither covered DTS objects.Thanks.....(more online)

   SQL Script to find when data... - Hi, Thanks in advance.Does any one have SQL Script to find out when "data" was modified/updated in the table.Regards.....(more online)

   Are these SP''s still available in 2k8? - The following system SP's are available in SQL 2k and 2k5... and the privs for their usage hasn't changed between the two.  Are they still available in 2k8?  I don't have 2k8, yet, so can't verify myself.  Than....(more online)

   Want SQL query - pls help me in constructing a query for the following dataI am having a table with only one fieldSay table name is Test and field name is Typeand i an having following data in the tableTypeABBBCANow i want result by query asA....(more online)

   Recomendations and opinions please - Hi, I want to take the exam for the MCTS Certification in SQL SERVER 2005 (70-431).Actually I'm studyng using the Microsoft's Mcts Self-Paced Training Kit.My questions are:Do I need something else to get ready for the exam?Do....(more online)

   The other side of outsourcing - Here's an interesting article I found on Bloomberg.com:http://www.bloomberg.com/apps/news?pid=20601109&sid=aNtGr9ytmeYI&refer=exclusiveReminds me of the tech boom in the 80's & early 90's.....(more online)

   Relational data into Excel by HTTP (like AS2005 data into Excel by HTTP) - Hi,It?s possible to get data in Excel 2003/2007 pivot tables by connecting to Analysis Services over HTTP as described here:http://www.microsoft.com/technet/prodtechnol/sql/2005/httpasws.mspxI tried it and it works fine.I?d l....(more online)

   Curisoity about a connection string oddity - Hi, all:I've been up the past five hours wrestling with an oddity, which while it is now functionally resolved, has left me curious. I'll describe what occurred, and then I have a simple question.I fitted a new, faster develo....(more online)

   Am I Old-Fashioned? - I function as DBA and developer, both. I find myself running into conflict with other developers on DB server access. My paradigm is that stored procedures are a tier in the n-tier development model, and should form the SOL....(more online)

   Issue w/ SQL Server 2005 Native XML Web Services - I am playing around with SQL Server 2005 Native XML Web Services; only I can't get them to work....I've created a stored proc and tested it.  It works fine.CREATE PROCEDURE [dbo].[GetVerizonProducts]ASBEGIN-- Insert stat....(more online)

   Lotus Approach - I have been in contact with a potential customer, it is a trade association that is currently using Lotus Approach for their membership database of about 3,000 members.I've been offered an opportunity to join the "IT Committe....(more online)

   Does anyone know anything about the company SetFocus? - This company posted a job in Monster.com so I applied. Then I found out if they accepted me, I had to take their business intelligence class for 9 weeks (no pay). Then I have two options - one becomes their employee working ....(more online)

   Blogging in your career - I maintain a few blogs, well I maintain a personal one. The one here at SQLServerCentral.com doesn't get so much work, but I'm trying to get better at that. It's hard for me since this editorial is almost like a blog, so ....(more online)

   1968 - Database software pre-dates Dr. Codd and the 1986 SQL standard. According to Ken North in this paper (PDF), there were a great many people working on coming up with some sort of standard for storing and working with data.....(more online)

   A Quick Count - I get accused of not writing enough technical editorials and it's probably true at times. Every once in awhile I wander down some strange path and get a bunch of writing done on subjects not necessarily related to DBA work, b....(more online)

   Die Access, Die - I had to lead with this one because I have a similar view as this post. Access is handy, quick, does a few things nicely, but creates headaches and many more poorly written applications than I think are necessary. And why....(more online)

   Exporting a report - Is there a way to export a report to a txt delimited (tab preferable) file?   My output can exceed the 65,536 rows that Excel allows, so this format will not work for me.   I also can't get csv to work wit....(more online)

   Table with large number of fields - Refine and implement One to One relations? - Hello everyone,This would be my first post here. I am developing a medical web application. Have started with the database design in sql svr 2005. Apart from the tables for security like User, Role, UserRole, Center etc I hav....(more online)

   Many Intersection Tables vs. Many Entity Tables - All,I am interested in feedback on which of the following is the more efficient and better performing design.  Please disregard syntax and datatype issues.  This is only an example.Also, I am very much leaning towar....(more online)

   Slow performance of Analysis Services queries due to high disk usage during queries - Hi, we have some problems with slow queries in Analysis Services. We recognized extremly high disk usage on the server when browsing OLAP cubes. This happens even when many users touched the server with the same reports, firi....(more online)

   "too large for the specified buffer size" - I'm writing data to an excel spreadsheet from SQL2000 DTS package. I've read plenty about this problem moving data from excel into a table. I've changed the excel guessing in the registry on both client & server. Same problem....(more online)

   The Pitfall of "Not Equal To" Operator in Queries! - Comments posted to this topic are about the content posted at temp....(more online)

   Finding Column Dependencies - Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/cbuettner/3214.asp....(more online)

   Server Side Paging With SQL Server 2005 - Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/jSebastian/3181.asp....(more online)

   Calculating Work Days - Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/jmoden/calcul....(more online)

   SQLBits Community Day - Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/pressrelease/3212.asp....(more online)

   Reverse Engineering Alias Data Types in SQL Server 2000 - Part II - Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/yShlafman/3213.asp....(more online)

PARSENAME Enhancement

By: Anonymous - Posted: 2/12/2007 6:00:16 AM (  (Stand-Alone SQL))

The ParseName function is very useful for getting parts out of a string of characters between delimiters. But a limitiation is that you only can get four parts out, and the function only accepts dots as delimiters.

The function I have written below overcomes that limitations, and add a new feature to enable "from left" and "from right" character extraction.


Example code

declare    @var varchar(200)

select    @var = 'a.ab.abc.abcd.abcde'

select    @var,
    dbo.fnParseString(4, '.', @var),
    dbo.fnParseString(-4, '.', @var)