SQL Server 2005: How To Restore a Database from a Backup


SQL Server 2005: How To Restore a Database from a Backup

Posted February 7, 2007 by Rob Rogers in Database

Your database is down, your users are in a panic, what do you do. If you have been performing routine backups of your database, you can restore it to the point of the most recent backup. To restore your database, follow these steps:

1. Open SQL Server 2005 Management Studio.

2. Connect to the appropriate server.

4. Right-click the desired database, mouseover Tasks. mouseover Restore and select Database .

5. The Restore Database window will appear.

6. Ensure that the correct database name appears in the To database field. If not, select it from the dropdown.

7. Under Source for restore. select the From device radio button.

8. Click the button next to the textbox to select the device to restore from.

9. The Specify Backup window will appear.

10. Click Add and locate and select the backup file from the Locate Backup File Window. Click OK.

11. Under Select the backup sets to restore. select all three backups (full, transaction log, and differential).

12. In the left pane, select Options .

13. Under Recovery state. ensure that the Restore with Recovery radio button is selected.

14. Click OK to begin restoring the database.

15. Once the restore is complete, a notification box will appear. Click OK to close the box. You have now restored your database to the most recent state.

I taken backup of database from Sql server 2005 from other PC and try to Restore it on my PC. Both having Windows authentication. But i am facing following error

System.Data.Sqlclient.sqlError:The media set has 2 media families but only 1 are provided.All member must be provided.(Microsoft.Sqlserver.Smo).

IS it problem of Ownar. because owanr of .bak file is different from where i try to restore it.

This is not the problem of owner, it is just because while selecting the restoring device there must be two paths (1. Default by Microsoft, 2. Given by you).
So delete the default path given by SQL Server itself, so it remains only one path that is given by you.
Do let me know in case this doesn t work also

i have stored mdf file and ldf file in dotnet solution file. i have copied my project from another system. i have to restore the database inorder to run my dotnet project.
i have followed your procedure, but when i click the ok button, it shows error.

please help me .

How To attach database sql

mdf and ldf files are not sql2005 based. its sql 2000. so install that and not need to restore. you right click the database in enterprise manager in sql2000..attach the MDF file from location. then run the program

Great step by step guidance. Worked a treat for me who is a complete novice

i need to restore my database in sql server 2005 and as i follow the procedure above there still an error. it shows this:

system.data.sqlclient.sqlerror:directory lookup the file C:program filesmicrosoft sql serverMSSQLDATASTUDENTS INFOREMATION.MDF failed with the operating system error 3(the system cannot findthe path specified.).(microsoft.sqlserver.express.smo)

im using windows 7 and visual studio 2008

while restoring my bakup file in MS SQL 2005 it gives

System.Data.SqlClient.SqlError: Directory lookup for the file c:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAtest_infoguideDatabase.mdf failed with the operating system error 3(The system cannot find the path specified.). (Microsoft.SqlServer.Smo)

Me too findind this error while restoration what does it means plz suggest any suitable way to remove this

i restore my database in sqlserver after restore default it display some path(D:\OTHERS\PROJECTS\TENDERMANAGEMENTSYSTEM\APP_DATA\TMSDATABASE.MDF) but i store my db in some other location..

plz help me how to clear this error

sql server by default provides some path u can restore ur db in ur own location jst click on that path and specify ur own path.


Posted In: NEWS

Tags: , , , ,

Leave a Comment

SNMP Monitoring Software #network #monitor, #network #monitoring, #server #monitor, #exchange #server,


SysUpTime Network Monitor

Current Version: 6.1 (build 6204)

SysUpTime network/server monitor is a powerful agentless network management software. It provides users out-of-box capabilities to efficiently and proactively manage any network of any size.

SysUpTime network monitor is easy to set up and use. It can monitor the availability and performance of your Windows and Linux/UNIX servers, workstations, software applications (SQL, Exchange, Web Servers, etc), and network devices (routers, switches, etc). When a failure is detected, SysUpTime network monitor can alert the administrators by email, sound, running a script or other methods, as well as taking corrective actions such as executing remote Windows/Linux command, rebooting the machine, restarting the Windows/Linux service or running a script.

SysUpTime network monitor’s server runs on both Windows and Linux.

Key Features:

Automatic Network Discovery and Accurate Topology Map

Automatically discovers layer 1, 2 and 3 networks, gathering topology data to deliver complete and accurate maps of devices and connectivity across networks. Powerful map editing function allows user to add or change discovered map easily.

The combination of unique extensible mediation layer technology and CIM (Common Information Model) based object model enables SysUpTime network monitor to support new devices and applications on an ongoing basis with minimum efforts.

Major features of network discovery and topology maps:

  • Support for a wide range of network devices and new devices can be easily added.
  • Configurable filters to narrow down the scope of discovery.
  • SNMP v1/v2c/v3 discovery.
  • Scheduled discovery.
  • Support for VLAN, HSRP, VRRP.
  • Custom views, which are user-defined maps based on IP address, location or other selection criteria.
  • Automatically discovered topology maps contain properties of network devices such as servers, routers, etc. Device list can be exported to a CSV file.
  • Devices can be manually added to topology map.
  • New properties or notes, such as license info, installed software, purchase price, can be manually added to devices. You can do search to find device with certain property.

Trended Performance, Status, and Service Monitoring

Detailed monitoring of network availability and performance.

  • Fully Automated Performance Monitoring

Core monitoring capabilities include:

  • Ping monitor
  • SNMP monitor(supports SNMP v1/v2c/v3 monitors)
  • MS Exchange server monitor
  • WMI monitor (Windows Services, Processes, CPU/Disk Utilization, etc.)
  • TCP/UDP port monitor
  • URL (HTTP/HTTPS) monitor
  • Telnet/SSH-2 monitor
  • FTP/File/Directory monitor
  • DNS/LDAP/RADIUS server monitor
  • Windows Event Log monitor
  • SMTP/POP3/IMAP4 server monitor
  • Database SQL monitor for all major database servers

More network/server monitoring capabilities are scheduled to be added soon.

  • Self-learning Dynamic Statistical Baseline (based on historical data)
  • Multi-level Fixed Thresholds

Real-time, End-to-End Event Management

SysUpTime network monitor collects and consolidates events and alarms. Event de-duplication, event auto-clearing, robust alerting, automated actions, and alarm escalation help organizations identify potential problems faster, shorten mean-time-to-repair and reduce IT and operations costs.

Core capabilities of event management include:

  • Supports SNMP v1/v2c/v3 traps and informs
  • Event reduction
  • Event clearing
  • Alarm escalation
  • Event forwarding
  • Event categorization

SysUpTime network monitor offers a wide range of alerting actions. Major actions include:

  • Send to different email accounts at different time.
  • Forward to another trap receiver.
  • Post to a web site using GET or POST methods.
  • Run remote command via SSH, Telnet or Windows RPC.
  • Run command.
  • Play sound.
  • Built-in Reporting and Graphing

    SysUpTime network monitor offers real-time and historical reporting and graphing capabilities for performance monitors, and top-N reports for users to identify problem elements. Scheduled reports in PDF format can be generated by month, week or day.

    Application monitoring helps you diagnose application problems, and prevent downtime or network performance degradation. SysUpTime network monitor provides out-of-box support for MS Exchange Server and SQL server. It can be configured to monitor critical parameters of both servers and their services.

    High Availability and Scalability

    SysUpTime’s server clustering technology allows two or more servers that are interconnected to form a unified virtual computing resource.

    Clustering servers increases the availability of a system by ensuring that if a server becomes unavailable because of failure or planned downtime, another server in the cluster can assume the workload, ensuring that the monitoring service is uninterrupted. Clustering also enhances scalability by automatically distributing workloads across all the available servers.

  • Key Benefits

    • Intuitive graphical user interface minimizes training requirements and increases productivity
    • Advanced fault management minimizes equipment downtime
    • Measure and report real time performance against designated SLA
    • Preempt performance problems by identifying potential trouble spots before they affect service quality
    • Increased operational efficiency
    • Use trending data to make the most of network resources and forecast CapEx needs
    • Protect existing investments — can be integrated with existing management systems
    • Implement high-quality management capabilities priced to suit enterprise IT budgets

    Professional and free personal editions

    The free personal edition is intended exclusively for private use on a single workstation. You may copy the complete program package and pass it on to others for private use only. The free personal edition may not be used for commercial or professional purposes.

    The professional edition has many more features and can be used for commercial or professional purposes.

    Features Table


    Posted In: NEWS

    Tags: , , , , , , , , , , , ,

    Leave a Comment

    Using SQL Developer to connect to SQL Server databases #sql #server,sql

    Using SQL Developer to connect to SQL Server databases

    Apparently, being able to use Oracle s SQL Developer to connect to SQL Server databases has been around for a while (via third party drivers), but I only started using it today for the first time, with SQL Developer 4 EA2 after my colleague mentioned it to me this afternoon

    First of all, thanks to DJ s blog here as a base for the instructions that follow. Here s how you can set this up:

    1. Download the jTDS 3rd party JDBC driver, version 1.2.8 from here (I ve not personally tested with other versions).
    2. Save and extract the contents of the jtds-1.2.8-dist.zip file somewhere safe (I extracted mine under C:\oracle\sqldeveloper\add-ons\jtds-1.2.8-dist\ ).
    3. Start SQL Developer go to Tools Preferences Database Third Party JDBC Drivers.
    4. Click “Add Entry”, point to the jtds-1.2.8.jar file location, then select it and click OK.
    5. Create a new connection, and choose the SQLServer tab.
    6. Enter the following details: Connection Name (required for database retrieval), Username, Password, Hostname, and Port.
    7. Click Retrieve database and select the appropriate database, then Save/Test/Connect as you would do normally.

    You ll notice from the screen shot above that you can also use this to connect to Sybase databases, although I ve not tried this out myself.

    UPDATE: If you at any point decide to use Windows authentication to connect (like I just did), then you might hit this error:

    Status. Failure -I/O Error: SSO Failed: Native SSPI library not loaded. Check the java.library.path system property.

    …I quickly found a work around, which involved copying the ..\jtds-1.2\x64\SSO\ntlmauth.dll file from the jTDS directory to the %JAVA_HOME%\jre\bin folder. Then I restarted SQL Developer and it all worked fine

    This works, but unfortunately there are a number of shortcomings. Some key words used in T-SQL and SQL Server scripts like GO and EXEC don t play nicely with Oracle SQL Developer. Also issues with Stored procs that return multiple result sets etc. It really depends on your environment, but if you re dealing with a lot of large scripts that were written in T-SQL you ll find issues.

    Thanks for the heads up I never really spent too much time using SQL Developer for managing SQL Server (defaulted to using SSMS), so that s good to know. Hopefully the development team will pick up on such issues and fix in later releases though


    Posted In: NEWS

    Tags: , , , ,

    Leave a Comment

    SQL Server – SQL INSERT Stored Procedure #stored #procedure, #sql #server,


    SQL Server Stored Procedure – INSERT – Example

    Source code to create and add sql insert stored procedure to catalog

    The following example is for creating a simple insert stored procedure. You can run it through an explicit call from a host language program or directly from a DBMS query execution shell like SQL Server Management Studio or dbOrchestra.

    You will note that just like in a INSERT statement you do not have to use all of the columns available when creating a stored procedure. You must however populate all columnar data associated with the PK (primary key), and columns associated with unique indexes (note: there are exceptions to this, but they will not addressed here), and columns defined in the ddl as NOT NULL .

    Executing the sql insert stored procedure

    Execute insert stored procedure

    To run the insert stored procedure you need to supply a value to the student_id variable as well as populate all required data. In this example I have included a cross section of columns for your reference, as well as the datatype associated with the columns in our SQL INSERT.

    A few thing to point out regarding the code above. Datetime and character data is entered using single quoted string. Integer data is entered without quotes. I also included all columns that were defined as NOT NULL. I also included the password column which is part of a unique index. You will note that I did not include columns for some of the non-unique indexes (soc_sec_num, other_id_num, and driver_license_num). I did this intentionally to demonstrate that they are not required columns. Having said this, in the real world one would only put indexes on columns where the intent was to actually collect the data for that column. I just wanted to make a technical point here. The pragmatic point is that you would want to expose columns that are part of indexes.

    What stored row looks like after the SQL INSERT

    I want to call you attention to the fact that all columns that are not referenced in the query get set to null. Also, if the schema had default values defined these would also get stored for that column when the rows gets inserted.

    SQL Server Information and Resources


    Posted In: NEWS

    Tags: , , , , ,

    Leave a Comment

    What is Microsoft SQL Server Parallel Data Warehouse (SQL Server PDW)?


    Microsoft SQL Server Parallel Data Warehouse (SQL Server PDW)

    Microsoft SQL Server Parallel Data Warehouse (SQL Server PDW) is a pre-built data warehouse appliance that includes Microsoft SQL Server database software, third-party server hardware and networking components.

    Download this free guide

    SQL Server Import Export Wizard Step-By-Step Tutorial

    In this expert-led tutorial, senior DBA and technical trainer Basit Farooq provides a step-by-step guide for using the SQL Server Import and Export Wizard to transfer data between SQL Server databases and Microsoft Excel worksheets.

    By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.

    You also agree that your personal information may be transferred and processed in the United States, and that you have read and agree to the Terms of Use and the Privacy Policy .

    Parallel Data Warehouse has a massively parallel processing (MPP ) architecture. As such, Microsoft has billed Parallel Data Warehouse as being well-tuned for big data processing.

    Like other server appliances, one of the main features of Parallel Data Warehouse is that it is easier to set up when compared to buying commodity hardware and software and configuring them in house. There are currently two versions of Parallel Data Warehouse: one uses Hewlett-Packard servers and the other uses Dell hardware.

    This was last updated in August 2013

    Continue Reading About Microsoft SQL Server Parallel Data Warehouse (SQL Server PDW)

    Related Terms

    columnstore index A columnstore index is a type of index used to store, manage and retrieve data stored in a columnar format in a database. See complete definition database (DB) A database is a collection of information that is organized so that it can be easily accessed, managed and updated. See complete definition SQL-on-Hadoop SQL-on-Hadoop is a class of analytical application tools that combine established SQL-style querying with newer Hadoop data. See complete definition

    Dig Deeper on SQL Server Data Warehousing


    Posted In: NEWS

    Tags: , ,

    Leave a Comment

    Using MERGE in SQL Server to insert, update and delete at


    Using MERGE in SQL Server to insert, update and delete at the same time

    By: Arshad Ali | Read Comments (34) | Related Tips: 1 | 2 | 3 | 4 | 5 | 6 | More > T-SQL

    In a typical data warehousing application, quite often during the ETL cycle you need to perform INSERT, UPDATE and DELETE operations on a TARGET table by matching the records from the SOURCE table. For example, a products dimension table has information about the products; you need to sync-up this table with the latest information about the products from the source table. You would need to write separate INSERT, UPDATE and DELETE statements to refresh the target table with an updated product list or do lookups. Though it seems to be straight forward at first glance, but it becomes cumbersome when you have do it very often or on multiple tables, even the performance degrades significantly with this approach.� In this tip we will walk through how to use the MERGE statement and do this in one pass.


    Beginning with SQL Server 2008, now you can use MERGE SQL command to perform these operations in a single statement. This new command is similar to the UPSERT (fusion of the words UPDATE and INSERT.) command of Oracle; it inserts rows that don’t exist and updates the rows that do exist. With the introduction of the MERGE SQL command, developers can more effectively handle common data warehousing scenarios, like checking whether a row exists, and then executing an insert or update or delete.

    The MERGE statement basically merges data from a source result set to a target table based on a condition that you specify and if the data from the source already exists in the target or not. The new SQL command combines the sequence of conditional INSERT, UPDATE and DELETE commands in a single atomic statement, depending on the existence of a record. The new MERGE SQL command looks like as below:

    The MERGE statement basically works as separate insert, update, and delete statements all within the same statement. You specify a “Source” record set and a “Target” table, and the join between the two. You then specify the type of data modification that is to occur when the records between the two data are matched or are not matched. MERGE is very useful, especially when it comes to loading data warehouse tables, which can be very large and require specific actions to be taken when rows are or are not present.

    Putting it all together

    In this example I will take a Products table as target table and UpdatedProducts as a source table containing updated list of products. I will then use the MERGE SQL command to synchronize the target table with the source table.

    First Let’s create a target table and a source table and populate some data to these tables.

    MERGE SQL statement – Part 1

    Hi, thank you so much this is a very nice article because i was facing a problm how to differentiate wich row is updated in final target table and i got an idea by reading your article so i declared Modified_date in target table and in mathed condition i checked if any row values is updated at the time modified_date will change and easy to identify in final result.

    Friday, February 20, 2015 – 6:54:03 AM – Shmuel Milavski

    This was very helpfull to me ,

    Monday, February 09, 2015 – 11:24:58 AM – Paul

    Is there a way to show only the items that are not matched in the target table from the source table? I run the below script in sql 2008 and get the following which is great but I want to be able to see which record did not match in the source table so I don’t have to go through 120 lines of data manually. The only table and row I am interested in is “stgitem.avgunitcost” which is the source table

    use test
    truncate table dbo.stgitem

    insert dbo.stgitem
    FROM ‘C:\PriceUpdate\priceupdate.csv’

    use test
    merge dbo.item
    using dbo.stgitem
    on item.code = stgitem.code
    when matched then
    set item.avgunitcost = stgitem.avgunitcost,item.issuecost = stgitem.issuecost;

    (120 row (s) affected)

    (1 row (s) affected)

    (1 row (s) affected)

    (119 row (s) affected)

    Friday, November 21, 2014 – 1:58:45 PM – Kimberly Ford

    This was a fabulous article. I needed to share a few tables from my data warehouse with another team but didn’t want to give them access to the entire database. So, I create another database with just the tables I needed, used PART 2 and I have a fantastic method of keeping the tables in the “off database” updated! Now to just create my stored procedure and I’m set. Definitely a huge help.

    Friday, November 07, 2014 – 4:05:03 PM – ola

    Nice Article Ashad,

    Am currently imploring the merge function in my code, and for some reason, the matched rows are not updated in the target table when the update was done, only the Not matched rows that got inserted into the target table.

    Any suggestions please.

    Monday, November 03, 2014 – 12:56:05 PM – Tejas

    Nice article – very good example.

    In your example above, when row does not exist in Source (WHEN NOT MATCHED BY SOURCE ) You are deleting the row from Target table.

    In my case, I just want to update a flag on target that the row was marked as deleted (in Source).

    SQL 2008R2 wont allow this:



    Posted In: NEWS

    Tags: , , , ,

    Leave a Comment

    SOLUTION High Availability SQL Server Interview Question? #microsoft #sql #server, #microsoft


    High Availability SQL Server Interview Question?

    That means an always on witness server that is talking to the same disks on a local SAN unit. You can’t really do that from a distance.

    Really Jimpen? I can’t believe you confidently posted this.
    First, by centralised storage, I meant a SAN, not a NAS.

    Secondly, anyone can implement geographical redundancy for both 1. servers and 2. the storage. as well long as there’s a high-speed fibre network to support this kind of implementation.

    How do you think organisations do carry on with business uninterrupted when there’s an entire site shutdown or an entire shutdown of one of their key datacentres?

    I will explain how to implement a two node HA solution using two sites over a GAN:
    One can implement HA across a metropolitan area network with two SAN site replicated across the two sites over a high-speed fibre connectivity.

    We’ve done this using both EVA NetApp. What’s key is implementing storage replication across the two sites. With NetApp, you can use snap-mirroring.
    You need two SAN directors, one at each geographically separated site and this is where you will connect your fibre to the servers (via HBA cards).

    So site 1 will have a server, a SAN director and your replicating SAN.
    Site 2 will also have the same thing.
    Then clustering will take place conveniently and this is the true meaning of HA where even your storage is also highly available.

    Let me know if you have any questions or if you need help to implement this. I can help.

    Come on Jimpen,
    You’re contradict yourself but I will help to shed some light get you back on track.
    When I stated that the servers can be geographically separated, you responded, Not really , meaning that the servers Cant be geographically separated .

    You certainly were not talking about the cost of implementation. If you had wanted to talk about expenses, you would have stated so.

    Are you probably trying to run away from what you’ve already written here? lol. Of course you can’t.
    Keep things simple: when you state something at EE, always let it be what you mean because we’re resolving a query.

    If I am to examine everything you’ve stated, it looks like you didn’t know know that the implementation I talked about is possible so you’ve mixed things up.

    If a client has a single SAN, two data centres (meaning two geographical locations) and two SAN directors, then the LUNs scanned (via HBAs) will be the same presented to the different servers and they will be off the same SAN irrespective of where the servers are connecting from.
    I figured out that you didn’t know this is possible basing on your response in response ID: 38971591. where you stated that Also SQL needs the HBA’s to be the same to work correctly.

    That’s why you talked about a NAS while responding, when you said that You want a SAN unit, not a NAS. in the same comment ID: 38971591 ; you didn’t consider the networking required across the WAN/GAN/MAN i.e, between the different data centres.
    The good news is that two or more SAN directors can be configured to work this way, hence you cannot say not really in response.

    Lastly, I would like to hint on your concept: a remote location SAN system as you state above. This rather is more of implementing HA at storage level, which also comes in handy for DR purposes, based on how you look at it and use it at a point in time.

    Bottom line: if you were trying to give an answer that is in a small normal range as you state, then you would have not said that that seems abnormal is not possible. It is indeed possible.

    Please let me know if you need any more info, or if you want to implement this anywhere in the world. so feel free.
    I hope we’re now on the same page 🙂


    Posted In: NEWS

    Tags: , , ,

    Leave a Comment

    What – s the difference between a CTE and a Temp


    This is pretty broad, but I’ll give you as general an answer as I can.

    • Are unindexable (but can use existing indexes on referenced objects)
    • Cannot have constraints
    • Are essentially disposable VIEW s
    • Persist only until the next query is run
    • Can be recursive
    • Do not have dedicated stats (rely on stats on the underlying objects)
    • Are real materialized tables that exist in tempdb
    • Can be indexed
    • Can have constraints
    • Persist for the life of the current CONNECTION
    • Can be referenced by other queries or subprocedures
    • Have dedicated stats generated by the engine

    As far as when to use each, they have very different use cases. If you will have a very large result set, or need to refer to it more than once, put it in a #temp table. If it needs to be recursive, is disposable, or is just to simplify something logically, a CTE is preferred.

    Also, a CTE should never be used for performance. You will almost never speed things up by using a CTE, because, again, it’s just a disposable view. You can do some neat things with them but speeding up a query isn’t really one of them.

    answered Feb 15 ’12 at 16:54

    Please see Martin’s comments below:

    The CTE is not materialised as a table in memory. It is just a way of encapsulating a query definition. In the case of the OP it will be inlined and the same as just doing SELECT Column1, Column2, Column3 FROM SomeTable. Most of the time they do not get materialised up front, which is why this returns no rows WITH T(X) AS (SELECT NEWID())SELECT * FROM T T1 JOIN T T2 ON T1.X=T2.X. also check the execution plans. Though sometimes it is possible to hack the plan to get a spool. There is a connect item requesting a hint for this. – Martin Smith Feb 15 ’12 at 17:08

    A CTE creates the table being used in memory, but is only valid for the specific query following it. When using recursion, this can be an effective structure.

    You might also want to consider using a table variable. This is used as a temp table is used, is also in-memory only, and can be used multiple times without needing to be re-materialized for each join. Also, if you need to persist a few records now, add a few more records after the next select, add a few more records after another op, then return just those handful of records, then this can be a handy in-memory structure.

    Temp Table

    A temp table is literally a table created on disk, just in a specific database that everyone knows can be deleted. It is the responsibility of a good dev to destroy those tables when they are no longer needed, but a DBA can also wipe them.

    Temporary tables come in two variety: Local and global. In terms of MS Sql Server you use a #tableName designation for local, and ##tableName designation for global (note the use of a single or double # as the identifying characteristic).

    Notice that with temp tables, as opposed to table variables or CTE, you can apply indexes and the like, as these are legitimately tables in the normal sense of the word.

    Generally I would use temp tables for longer or larger queries, and CTEs or table variables if I had a small dataset already and wanted to just quickly script up a bit of code for something small. Experience and the advice of others indicates that you should use CTEs where you have a small number of rows being returned from it. If you have a large number, you would probably benefit from the ability to index on the temp table.

    The accepted answer here says “a CTE should never be used for performance” – but that could mislead. In the context of CTEs versus temp tables, I’ve just finished removing a swathe of junk from a suite of stored procs because some doofus must’ve thought there was little or no overhead to using temp tables. I shoved the lot into CTEs, except those which were legitimately going to be re-used throughout the process. I gained about 20% performance by all metrics. I then set about removing all the cursors which were trying to implement recursive processing. This was where I saw the greatest gain. I ended up slashing response times by a factor of ten.

    CTEs and temp tables do have very different use cases. I just want to emphasise that, while not a panacea, the comprehension and correct use of CTEs can lead to some truly stellar improvements in both code quality/maintainability and speed. Since I got a handle on them, I see temp tables and cursors as the great evils of SQL processing. I can get by just fine with table variables and CTEs for almost everything now. My code is cleaner and faster.

    answered Feb 17 ’12 at 9:49

    The primary reason to use CTEs is to access Window Functions such as row_number() and various others.

    This means you can do things like get the first or last row per group VERY VERY quickly and efficiently – more efficiently than other means in most practical cases .

    You can run a similar query to the above using a correlated subquery or by using a sub-query but the CTE will be faster in almost all scenarios.

    Additionally, CTEs can really help simplify your code. This can lead to performance gains because you understand the query more and can introduce more business logic to help the optimizer be more selective.

    Additionally, CTEs can boost performance if you understand your business logic and know which parts of the query should be run first – typically, put your most selective queries first that lead to result sets that can use an index in their next join and add the option(force order) query hint

    Finally, CTEs don’t use tempdb by default so you reduce contention on that bottleneck through their use.

    Temporary tables should be used if you need to query the data multiple times, or alternatively if you measure your queries and discover that by inserting to a temp table and then adding an index that your performance is improved.

    answered Oct 8 ’13 at 0:50

    There seems to be a bit of negativity here towards CTE’s.

    My understanding of a CTE is that it’s basically a kind of adhoc view. SQL is both a declarative and a set based language. CTE’s are a great way of declaring a set! Not being able to index a CTE is actually a good thing because you don’t need to! It’s really a kind of syntactic sugar to make the query easier to read/write. Any decent optimizer will work out the best access plan using indexes on the underlying tables. This means you could effectively speed up your CTE query by following the index advice on the underlying tables.

    Also, just because you defined a set as a CTE, it doesn’t mean that all rows in the set must be processed. Dependent on the query the optimizer might process “just enough” rows to satisfy the query. Maybe you only needed the first 20 or so for your screen. If you built a temp table then you really do need to read/write all those rows!

    Based on this I would say that CTE’s are a great feature of SQL and can be used anywhere they make the query easier to read. I would only think about a temp table for a batch process that would really need to process every single record. Even then afaik it’s not really recommended because on a temp table it’s far harder for the database to help you with caching and indexes. It might be better to have a permanent table with a PK field unique to your transaction.

    I have to admit that my experience is mainly with DB2 so I’m assuming that CTE’s work in a similar way in both products. I will happily stand corrected if CTE’s are somehow inferior in SQL server. 😉

    answered Mar 24 ’14 at 22:30

    2017 Stack Exchange, Inc


    Posted In: NEWS

    Tags: , ,

    Leave a Comment

    Hybrid Business Intelligence with Power BI #sql #server, #powerbi, #hybrid #business


    Hybrid Business Intelligence with Power BI

    This week in the social media chatter, I noticed tweets regarding a new Microsoft white paper by Joseph D Antoni and Stacia Misner published to TechNet on Hybrid Business Intelligence with Power BI. This white paper is a fantastic technical overview and a must-read for groups looking at Power BI, wondering how to best implement it with existing on-premises business intelligence BI, or Azure Infrastracture as a Service (IaaS) hosted BI. Covered topics include:

    • hybrid BI technical architecture options
    • data management gateway
    • best practices for:
      • integrating security
      • identity management
      • networking
      • Office 365

    Aside from small businesses that may only have cloud hosted solutions, many businesses currently have a combination of cloud and on-premises data sources. Just think about how many groups use Salesforce.com, Google Analytics, Constant Contact, and other departmental cloud applications. Typically, I see those groups leveraging APIs or connectors to bring cloud data back on site into a local data warehouse for creating reports. We are taking those same concepts quite a bit further with Microsoft Azure and Power BI.

    Ideally, we are no longer moving all of the data in our big data world. Concepts like data virtualization, for example, are becoming more popular. Most likely, we are now tasked to deliver a transparent Microsoft BI experience across Office 365 and existing on-premises SharePoint portals or data sources.

    Understanding how to architect hybrid-BI scenarios is becoming a more important skill to master in our profession. However, prior to this new white paper, finding the answers and best practices for it was fairly challenging.

    Security in a Hybrid World

    Upon a brief skim through this new technical whitepaper, I noticed a lot of content around networking and identity management. Historically, identity management and security in Microsoft BI has not been easy to master. In a hybrid BI world, these topics appear to be comparable or even a bit more complex.

    Let s face it, getting through a SharePoint 2013 BI farm installation and configuration can be a daunting process for even the top talent in the world. I usually advise to folks considering a new SharePoint 2013 BI farm installation to first read Kay Unkroth s incredible white paper to understand SharePoint security, Microsoft BI security, and Kerberos delegation concepts.

    Managing user security in Office 365 looks comparable to on-premises SharePoint security. There are options to federate Active Directory (AD) to Office 365 and use Single Sign On (SSO). There are additional alternatives for multi-factor authentication in scenarios where you require additional layers of security.

    In hybrid BI scenarios where you have Analysis Services or Reporting Services hosted on Microsoft Azure VMs, you might also need to configure Azure AD, AD Federation Services (ADFS), and the Azure Active Directory Sync tool to synchronize passwords, users, and groups between on-premises AD and Azure AD supporting the Office 365 installation. The new Hybrid Business Intelligence with Power BI white paper goes into detail on those concepts and includes links to a plethora of excellent resources.

    Data Management Gateway for Power BI

    At the moment, Data Management Gateway appears to be the key to hybrid BI with Office 365 Power BI. The Data Management Gateway is a client agent application that is installed on an on-premises server and copies data from internal data sources to the Power BI cloud data source format.

    Office 365 Power BI data sources are a bit of a cloud data island per se, but over time it should continue to evolve. Present Power BI Data Refresh capabilities, basically Excel workbooks deployed to a Power BI site, can have a single data refresh schedule from the following supported data sources:

    • On-premises SQL Server (2005 and later)
    • On-premises Oracle (10g and later)
    • Azure SQL Database
    • OData feed
    • Azure VM running SQL Server

    Now, if you have a VPN connection and Azure virtual network, it opens up many more potential data sources for Power BI. In that case, accessing data sources with Power BI data connections and scheduled refresh is similar to on-premises Power Pivot except it sure looks like you still need Data Management Gateway to get that data into Power BI-land. The white paper section labeled Power BI Data Refresh goes into deep detail on supported data sources, data refresh schedules, and various data location scenarios.

    Sending Feedback to Microsoft

    We are just beginning to see Microsoft BI and Power BI in a cloud and hybrid world. Groups that are using Power BI and hybrid BI today are early adopters. We would all benefit from hearing about their tips, tricks, and lessons learned. I see a lot of continual changes in Azure and total confusion out here especially around Azure cloud BI and Power BI with on-premises data sources.

    If you have Microsoft technical content requests, you can send feedback to the teams that develop these resources to get new topics on their radar. Don t assume someone else has already expressed a need. If no one asks or complains, the folks in Redmond may be completely unaware of that need. It really is that simple.


    Posted In: NEWS

    Tags: , , , , , , , , , , , , , , , ,

    Leave a Comment

    Dynamic PIVOT in Sql Server #concatenate #in #sql #server



    Dynamic PIVOT in Sql Server

    In the Previous Post PIVOT and UNPIVOT in Sql Server explained how PIVOT relational operator can be used to transform columns distinct values as Columns in the result set by mentioning all the distinct column values in the PIVOT operators PIVOT columns IN clause. This type of PIVOT query is called Static PIVOT query, because if the PIVOT column in the source table get s extra unique values after the initial query then that will not reflect in the PIVOT query result unless it is mentioned in the PIVOT Columns IN clause. Static PIVOT queries are fine as long as we know that the PIVOT column values never change, for instance if PIVOT column values are MONTH or Day of the Week or hour of the day etc.

    In this Article will present how we can write a Dynamic PIVOT query with an example, where we don t need to mention the PIVOT columns each unique values and no need to worry if PIVOT column gets extra unique values after the initial query.

    First Create a Temporary Table #CourseSales with sample records as depicted in the below image by using the following script:

    PIVOT #CourseSales Table data on the Course column Values

    Let us first understand the Static PIVOT query and then see how we can modify this Static PIVOT query to Dynamic.

    Static PIVOT query

    Below Static PIVOT script pivots the #CourseSales Table data so that the Course columns distinct values are transformed as Columns in the result set as depicted in the above image.

    Let us insert one more row in the #CourseSales table for the new course SQL Server with below insert statement.

    From the above result it is clear that the newly added course Sql Server sales data is not reflected in the result.

    Dynamic PIVOT Query

    To make the above Static PIVOT query to dynamic, basically we have to remove the hardcoded PIVOT column names specified in the PIVOT operators PIVOT columns IN clause. Below query demonstrates this.

    From the above result it is clear that this query is a True Dynamic PIVOT query as it reflected all the courses in the #CourseSales table without needing to write hardcoded course names in the PIVOT query.

    Examples of PIVOT and Dynamic PIVOT

    Below are the some of the examples of retrieving data in Sql Server using PIVOT and Dynamic PIVOT:

    Post navigation

    Deepak Bhise says:

    I executing below query, where error is Incorrect Syntax Near Pivot

    with taxdet as
    (Select tbi.bill_item_id,tbi.Sr_number, tbi.item_id,tbi.total_amt, tbt.tax_amt, tbt.sub_tax_id,
    tbm.Bill_no, tbm.Bill_amount, tbm.current_table_no, tstm.SubTax_Name
    from tblBill_Items tbi
    Right Join tblBill_taxes tbt on tbt.Corelation_id = tbi.bill_item_id
    Right Join tblBill_master tbm on tbm.Sr_number = tbi.Sr_number
    Left Join tblSubTax_master as tstm on tstm.SubTax_id = tbt.sub_tax_id
    where tbt.tax_id = 1 and tbm.isSettle = 1 and tbm.Bill_no 0)
    Select * from taxdet
    Select * into DPKTAX from (Select * from Taxdet)
    set nocount on

    this is great but,
    this is not dynamic at all, because at the top of the query you are trying to find the pivot columns and writting these columns hardcoded so this is not exactly dynamic but half dynamic :)) but however this is great post thanks.

    We Can eliminate Null values in the pivoted list as below

    Vu Hong Trieu says:

    Thanks tut,
    So i try this but my solution useful
    1. My data here:

    Execute the Dynamic Pivot Query

    4. So i want to my result that:

    Owais Ahmed Memon says:

    i have one table contain two columns one is Type_of_case and other is Stage Both

    i want to prepare lookup table

    i tried your example its working ok but i am un able to make it for my table
    heres the list of all colums for instance

    SELECT TOP 1000 [Case_id]
    FROM [WM_Cases].[dbo].[Cases_main]

    i need this result
    Type_of_case Total Of Case_id Case Closed Closure Finding Received
    Admin 6 1 5
    Age 70 1 65 4
    Decipline 54 2 35 8

    and here is the result (I am shortening field lables)
    Patient ID Document Number Claim N CARE U007
    1000 289 1 76.58 19.53

    However I need the result be like:

    Patient ID Document Number Claim N Insu1 Insu2 Paid1 Paid2
    01000 289 1 CARE U007 76.58 19.53

    My query is working successfully, but some of the columns are not showing up. For instance, in the Name Column, I have the values Object , Use , and Size that I am trying to use as column headers, but only the Use and Size are appearing as headers. I can see Object as a value in the table I m pivoting from, so I don t know why it s hidden. Can anyone help?

    Subscribe to Blog via Email


    This is my personal blog site. The opinions expressed here represent my own and not those of my employer. For accuracy and official reference refer to MS Books On Line and/or MSDN/TechNet. My employer do not endorse any tools, applications, books, or concepts mentioned on the blog. I have documented my personal experience on this blog.




    Sql Server Tutorial

    Recent Posts


    SqlHints.com © 2016. All Rights Reserved.


    Posted In: NEWS

    Tags: , , ,

    Leave a Comment

    BigQuery – Analytics Data Warehouse #big #data #sql



    Enterprise Cloud Data Warehouse

    BigQuery is Google’s fully managed, petabyte scale, low cost enterprise data warehouse for analytics. BigQuery is serverless. There is no infrastructure to manage and you don’t need a database administrator, so you can focus on analyzing data to find meaningful insights using familiar SQL. BigQuery is a powerful Big Data analytics platform used by all types of organizations, from startups to Fortune 500 companies.

    Speed Scale

    BigQuery can scan TB in seconds and PB in minutes. Load your data from Google Cloud Storage or Google Cloud Datastore, or stream it into BigQuery to enable real-time analysis of your data. With BigQuery you can easily scale your database from GBs to PBs.

    Incredible Pricing

    BigQuery separates the concepts of storage and compute, allowing you to scale and pay for each independently. It also gives you flexible pricing options to better suit your needs. You can either choose a pay-as-you-go model or a flat-rate monthly price for those who need cost predictability.

    Security Reliability

    BigQuery automatically encrypts and replicates your data to ensure security, availability and durability. You can further protect your data with strong role-based ACLs that you configure and control using our Google Cloud Identity Access Management system.

    Partnerships Integrations

    Google Cloud Platform partners and 3rd party developers have developed multiple integrations with BigQuery so you can easily load, process, and make interactive visualizations of your data. Our partners include Looker, Tableau, Qlik, Talend, Google Analytics, SnapLogic and more. The BigQuery Data Transfer Service automates data movement from partner SaaS applications to Google BigQuery.

    BigQuery Features

    A fast, economical and fully managed data warehouse for large-scale data analytics.

    Flexible Data Ingestion Load your data from Google Cloud Storage or Google Cloud Datastore, or stream it into BigQuery at 100,000 rows per second to enable real-time analysis of your data. Global Availability You have the option to store your BigQuery data in European locations while continuing to benefit from a fully managed service, now with the option of geographic data control, without low-level cluster maintenance headaches. Security Permissions You have full control over who has access to the data stored in Google BigQuery. Shared datasets will not impact your cost or performance (those you share with pay for their own queries). Cost Controls BigQuery provides cost control mechanisms that enable you to cap your daily costs to an amount that you choose. For more information, see Cost Controls. Highly Available Transparent data replication in multiple geographies means your data is available and durable even in the case of extreme failure modes. Fully Integrated In addition to SQL queries, you can easily read and write data in BigQuery via Cloud Dataflow, Spark, and Hadoop. Connect with Google Products You can automatically export your data from Google Analytics Premium into BigQuery, visualize it using Google Data Studio and analyze datasets stored in Google Cloud Storage. Automatic Data Transfer Service The BigQuery Data Transfer Service automatically transfers data from partner SaaS applications to Google BigQuery on scheduled, managed basis.

    Spotify chose Google in part because its services for analyzing large amounts of data. tools like BigQuery, are more advanced than data services from other cloud providers.

    – Nicholas Harteau, VP of Infrastructure, Spotify

    BigQuery Pricing

    BigQuery charges for data storage, streaming inserts, and for querying data, but loading and exporting data are free of charge. For detailed pricing information, please view the pricing guide .


    Posted In: NEWS

    Tags: , ,

    Leave a Comment

    SQL Server Integration Services SSIS FTP Task for Data Exchange #sql


    SQL Server Integration Services SSIS FTP Task for Data Exchange

    Connecting to an FTP Server Using Windows Explorer

    There are different ways to connect to an FTP Server for data verification and one of the easiest ways is to use Windows Explorer. Just specify the FTP Server name with FTP protocol as shown below and hit Enter .

    This will bring up a screen as shown below. Here we need to specify the credentials to connect to the FTP Server or choose to log on anonymously if the FTP Server allows anonymous access.

    Next click on the Log On button to log onto the specified FTP Server with the specified credentials. Based on permissions, it will display folders and files similarly to what’s shown below:

    Using the FTP Task in an SSIS Package

    In order to connect to an FTP Server, we first need to create an FTP connection manager which encapsulates information needed to connect to the FTP Server and the FTP task that uses that information at run time to connect to the server. In order to create a FTP connection manager create a new package and right click on the Connection Managers pane on the bottom and then select FTP as the connection manager type from the dialog box as shown below:

    Clicking on the Add button on the above dialog box will bring up an FTP Connection Manager Editor to specify the different information which will be used to connect to the FTP Server. For example, the FTP Server name, port, credentials to connect to the FTP Server, etc.

    Now you can drag an FTP Task from the SSIS Toolbox to the control flow, which will look similar to this:

    Double click on the FTP task to change its properties in the Editor. The editor has 3 pages. On the General page we need to select the FTP connection manager that we created above for connecting to the FTP Server and then specify an appropriate name and description for the component as shown below:

    On the FTP Transfer page, we first need to select the operation that we want to perform (more about these different operations can be found in the beginning of this tip) with this FTP task based on the Local Parameters and Remote Parameters options will appear or disappear. For example, when we select “Receive files” as the operation type then both Local Parameters and Remote Parameters options will appear, but if I select the “Create remote directory” option then only the Remote Parameters option will appear.

    For this example I want to download a specific file from the FTP Server so I will chose “Receive files” as the operation type and then specify the remote parameters like location and name of the file to be downloaded and the local parameters like local machine folder where the downloaded file will be saved. Here, I can either specify hard-coded values for these parameters or the values can come from SSIS package variables making this process more dynamic.

    As you might have noticed, we can select only one operation type for each FTP task and if we want to perform multiple operations we need to use multiple FTP tasks, one for each operation. Now if I execute the package we can see the file from the FTP Server getting downloaded and written to the folder that we specified. After the file has been downloaded I can then use a data flow task to load data from the locally downloaded file to the database.

    Downloading Multiple Files using a Wild Card Character

    In the above example, I simply downloaded one file from the FTP Server to the local machine. But what if I want to download or upload all the text files or all the XML files in one go. Well in that scenario, we can use a wild card character. For example, as you can see below I want to download all text files from the specified FTP Server location to the local machine and hence I specified “*.txt” for the file name.

    Now the above specified wild card character will download all the text files to the local machine from the FTP Server. To load each of these files to the database I can use a ForEach Loop container in SSIS as shown below.

    We can use the ForEach Loop Container with a ForEach File Enumerator and process all the data files from the locally downloaded folder; in other words, process each data file one at a time in a loop.

    The FTP Task in SSIS does not support SFTP (Secured FTP), but there is a task available at codeplex which can be used for this scenario.

    Next Steps

    Last Update: 2013-02-15

    Wednesday, May 03, 2017 – 10:28:49 AM – Scott

    To everyone wondering how to get a package to remember the FTP password: learn how to do package configuration (store password in an XML file, SQL table, commandline args, environment variables, etc) A full discussion is beyond the scope of this reply.

    SFTP: There are a number of third-party tools for this. I use Pragmatic Works Task Factory, and a large part of that decision was their licensing model. Any number of developers can freely use the tools in BIDS, you only have to license the servers you will deploy the packages to. There are other tools available that can do the job, most of them will let you use them on a trial basis.

    When I have to do multiple FTP actions in one package, I usually end up using a script task. Create and configure the FTP connection manager, then in the script task use AcquireConnection to get an FtpClientConnection object. You may find the available methods allow you to be much more creative in your solution than trying to string together and configure a bunch of separate tasks.

    One issue I had recently was an FTP package that had worked for 10 years started blowing up when the other party’s ISP started bouncing packets around Atlanta for an extra 10 hops and connection times went from 1 second to 45 seconds. It didn’t matter what number I used with the standard FTP connection manager for the Timeout property, the package failed at 30 seconds every time. If anyone has a solution for this I would love to hear it. My solution was to modify my script tasks to use FtpWebRequest objects instead of FtpClentConnection, which didn’t reduce the connection time but did respect a 60-sec connection timeout value.

    Thursday, September 22, 2016 – 3:38:00 AM – Mutiara

    Please help me. I’m using SSIS 2012 here, and I need to download a file from AS/400 using SSIS FTP task, it’s a fixed text file. I’ve already define the remote path with something like this, /QSYS.LIB/u38000UXX.lib/detail1.FILE. but it’s always response with this

    [FTP Task] Error: File represented by “/QSYS.LIB/u38000UXX.lib/detail1.FILE” does not exist.

    But if i try to download the same file using ftp trough command prompt, it’s downloaded successfully

    Any idea what I should check or change here?

    I am using SSIS package.

    FTP Task Editor=

    IsLocalPathVarible:- True
    Local Varible :- User::Final

    Operation :Send Files
    ISTransferASCII :- False

    Remote Parameter
    IsRemotePathVarible :-False

    REmote Path :- \Location
    OverwriteFileAtDest :- YES

    Final:\test20130724_230456_662000000.xls which having expression
    “D:\\test”+Replace(Replace(Replace(Replace((DT_WSTR,50)(getdate()),”-“,””),” “,”_”),”:”,””),”.”,”_”)+”.xls”

    But [Connection manager “FTP Connection Manager”] Error: An error occurred in the requested FTP operation. Detailed error description: The filename, directory name, or volume label syntax is incorrect. e SSMS formatting.

    Friday, June 07, 2013 – 1:44:08 PM – hjump51


    Posted In: NEWS

    Tags: ,

    Leave a Comment

    T-SQL String Manipulation Tips and Techniques, Part 1 #t-sql, #database #development,


    T-SQL String Manipulation Tips and Techniques, Part 1

    T-SQL is a language that was mainly designed to handle data manipulation tasks. Not much effort and attention were given to other kinds of tasks, such as string manipulation. Therefore, when you do need to manipulate strings in T-SQL, it can sometimes be quite challenging even for seemingly simple tasks. This article is the first of a two-part series in which I cover several common string manipulation needs. I d like to thank the following people who provided input regarding this topic: Ruben Garrigos, Kevin Boles, Fabiano Amorim, Milos Radivojevic, Peter Larsson, and Davide Mauri.

    Counting Occurrences of a Substring Within a String

    This code returns the value 3, indicating that the substring hello appears three times in the string abchellodehellofhello .

    The steps in this technique are:

    Because the solution is in the form of a single expression, it can be applied as part of a query using the underlying table s or view s attributes as inputs.

    Exactly N Occurrences of a Substring Within a String

    One obvious way to achieve this is to use the previous technique to count occurrences of a substring in a string, like so:

    Another way to handle the task is to use the LIKE predicate, like so:

    With both techniques, the validation is handled by a single expression. Therefore, you can easily embed the expression in a query filter or a CHECK constraint in a table definition.

    Replacing Multiple Contiguous Spaces With a Single Space

    ) or even multiple characters (e.g.

    (token plus space).

  • Replace in the result of the previous step each occurrence of
    (space plus token) with (an empty string).
  • Replace in the result of the previous step each occurrence of

    (token plus space) with (space).

  • All this translates to the following T-SQL expression:

    The output of this code is: this is a string with lots of spaces.

    Replacing Overlapping Occurrences

    The next challenge was brought up by Davide Mauri; he initially found it in the Italian SQL Server forums. It involves a certain misconception that some people have regarding the way the REPLACE function works. Consider the following code. Before executing it, see if you can guess what the output will be:

    Some people intuitively think that the output should be .y.y.y.y. however, it s actually .y.x.y.x. The reasoning behind the actual result is that REPLACE considers nonoverlapping occurrences from left to right. If we represent the string .x. with the symbol A, you can express the source string as AxAx.; then, replacing each occurrence of A with .y. gives you .y.x.y.x..

    Suppose you want to handle the replacement task while considering overlapping occurrences. One way to achieve this is to nest two calls to the REPLACE function, like so:

    Another option is replacing each occurrence of the separator with two, then applying the originally intended replacement, then replacing each two occurrences of the separator with one. Here s the expression in T-SQL:

    String Formatting Numbers With Leading Zeros

    There are several tasks related to formatting numbers as strings that people often inquire about. T-SQL perhaps isn t the best place to handle those, but I ll still present solutions mainly for their technical value.

    The output of this code is -0000001759.

    Another solution involves converting the absolute input value to a character string, concatenating a string with nine zeros (000000000) with the result of the previous step, extracting the 10 rightmost characters from the result, and finally adding the minus sign in front if the input is negative. Here s the expression that implements this logic:

    SQL Server 2012 (formerly code-named Denali ), is planned to support a function called FORMAT that will make your life really easy when it comes to formatting strings representing numbers and other types of values. The FORMAT function accepts a format string compatible with .NET format strings for similar purposes, indicating how you want to format the value. For our specific task, the expression is very simple:

    Left Trimming Leading Occurrences of a Character

    The technique to achieve this is quite straightforward when the source string doesn t contain spaces to begin with. You first replace each occurrence of a zero with a space, then apply LTRIM to remove leading spaces, then replace each occurrence of a space with a zero, like so:

    This code returns 1709.

    If spaces are allowed in the input, you want to first replace each existing space with a token that you know can t appear in the source data (e.g.

    ), then apply the previous technique, then replace each occurrence of the token with a space, like so:

    This code returns 1709 ABC.

    This just gives you an idea of dealing with one complication. Of course there can be several additional complications, such as support for negative values, spaces at the beginning, and so on.

    Checking That a String Is Made of Only Digits

    One solution in which you don t need to worry about special cases is to generate a string pattern in which you replicate the character pattern [0-9] (basically, a digit) as many times as the number of characters in the input string, like so:

    The problem with this approach is that if the input strings are very long, the pattern generated will be five times as long as the input.

    There s another solution that also doesn t require you to deal with special cases, and it uses a very short pattern. You use two levels of negation, verifying that the string doesn t contain any character that s not a digit, like so:

    Just like with the previous techniques I described, the last two validation techniques implement the logic using a single expression. This means that you can embed the predicate wherever predicates are supported in T-SQL, operating on a table or view attribute. For example, if you need to enforce this rule in a CHECK constraint as part of a table definition, you can, like so:

    Make the Most of T-SQL s Tools

    I covered several common string manipulation tasks: counting occurrences of a substring within a string, verifying that there are an exact number of occurrences of a substring within a string, replacing multiple contiguous spaces with a single space, replacing overlapping occurrences, formatting strings representing numbers with leading zeros, left trimming leading character occurrences, and verifying that a string is made of only digits. As you saw, T-SQL doesn t provide very rich capabilities in this area. You therefore need to be a bit creative with the limited set of tools that you do get. Fortunately, SQL Server Denali improves support for string manipulation by adding new functions, among them the FORMAT function that lets you format an input based on a format string.

    Next month, I ll continue the discussion about string manipulation, and I ll cover additional challenges related to string manipulation involving tricky type conversions. If you have your own cool and creative techniques to handle common string manipulation needs, I d love to hear about them.


    Posted In: NEWS

    Tags: , , , ,

    Leave a Comment

    Using the Microsoft SQL Server 2012 Best Practice Analyzer #sql #server


    Using the Microsoft SQL Server 2012 Best Practice Analyzer

    By: Ashish Kumar Mehta | Read Comments (4) | Related Tips: More > SQL Server Configurations

    Database Administrators are often asked questions like “are all the SQL Servers within the organization configured according to industry standards?” In this tip, you will see how a Database Administrator can quickly use Microsoft SQL Server 2012 Best Practices Analyzer to analyze a SQL Server instance to determine whether it is configured according to best practices or not.


    The Microsoft SQL Server 2012 Best Practices Analyzer (BPA) is an excellent tool which is available as a free download from Microsoft. Using the BPA tool, a DBA can quickly gather information from Microsoft Windows and SQL Server configuration settings. The BPA tool basically uses a predefined set of SQL Server 2012 recommendations and best practices to identify potential issues within the database environment. You can download the latest version of Microsoft SQL Server 2012 Best Practices Analyzer (BPA) for free from the following link .

    The following are required for using SQL Server 2012 Best Practices Analyzer:

    Prior to the installation of Microsoft SQL Server 2012 Best Practice Analyzer you need to download and install Microsoft Baseline Configuration Analyzer 2.0 otherwise you will see the below screen when you double click SQL2012BPA_Setup64.MSI or SQL2012BPA_Setup32.MSI based on your environment when trying to install Microsoft SQL Server 2012 Best Practice Analyzer.

    Once you have successfully installed Microsoft Baseline Configuration Analyzer 2.0 and Microsoft SQL Server 2012 Best Practice Analyzer. You can use the BPA tool by clicking Start Programs Microsoft Baseline Configuration Analyzer 2.0. In Microsoft Baseline Configuration Analyzer 2.0, select a product such as SQL Server 2012 BPA as shown in the below snippet.

    Next, click Connect to Another Computer and choose Local Computer and then click OK to close the “Connect to Another Computer” window. Then in the Microsoft Baseline Configuration Analyzer 2.0 window, click Start Scan to begin the scan.

    In the Microsoft Baseline Configuration Analyzer 2.0 Enter Parameters screen, you need to specify the SQL Server Instance Name, for a default instance you need to specify the instance name as MSSQLSERVER and for a Named Instance you need to specify the SQL Server Instance Name as shown in the below snippet.

    Using Microsoft SQL Server 2012 Best Practice Analyzer you can scan the SQL Server Database Engine, Analysis Services, Replication, Integration Services, Reporting Servers and SQL Server Setup. You can choose the required parameters and click the Start Scan at the bottom of the screen to begin the scan.

    In the below snippet you can see the Microsoft Baseline Configuration Analyzer 2.0 is scanning to identify potential issues.

    Once the Microsoft SQL Server 2012 BPA 1.0 has completed the scanning you will be able to see the Baseline Configuration Analyzer Report which will be categorized into Errors and Warnings as shown in the snippet below.

    Once you expand the Error category as shown in the below snippet, you will be able to see different errors that exist on the SQL Server Instance as per the rules configured in the Best Practices Analyzer.

    You can click on the Error to view the Detailed Explanation of the issue encountered and to resolve the issue follow the resolution steps mentioned.

    Advantages of Using SQL Server 2012 Best Practice Analyzer

    • Using this tool a DBA can determine whether the SQL Server is configured as per the best practices recommended by the SQL Server Products Team.
    • This tool validates your instance of SQL Server with certain built-in rules that can help you rectify common installation and configuration issues.
    • This tool recommends fixes for most of the potential problems on an instance of SQL Server and it’s an excellent free tool to identify potential bottlenecks within your SQL Server Instance.

    Disadvantages of Using SQL Server 2012 Best Practice Analyzer

    • I feel some of the resolution messages are not very clear and if you have any doubts I would recommend you to click the “More Information” option under each Error or Warning to get a complete understanding before implementing the suggestion.
    • This tool is a great starting point to identify issues, but this tools does not address every potential issue for a SQL Server instance. You still need to educate yourself on best practices for setting up SQL Server.

    Troubleshooting Issues with SQL Server 2012 Best Practice Analyzer Installation

    • As a best practice, a person who is installing SQL Server 2012 Best Practice Analyzer must have their Windows Account within the Windows Local Administrator Group and SQL Server Administrator Group on the server where this tool is installed.
    • If you receive any issues related to PowerShell, it is recommended to take a look at the Additional Information section of the Microsoft SQL Server 2012 Best Practice Analyzer Download Page.

    Next Steps

    Last Update: 2012-06-22


    Posted In: NEWS

    Tags: , , , ,

    Leave a Comment

    Indexing the Data Warehouse #sql #server #analysis #services, #storage, #sql #server,


    Indexing the Data Warehouse

    Indexing the data warehouse can reduce the amount of time it takes to see query results. When indexing dimensions, you ll want to index on the dimension key. When indexing the fact table, you ll want to index on the date key or the combined data plus time.

    Indexing a data warehouse is tricky. If you have too few indexes, the data loads quickly but the query response is slow. If you have too many indexes, the data loads slowly and your storage requirements go through the roof but the query response is good. Indexing in any database, transactional or warehouse, most often reduces the length of time it takes to see query results. This is especially true with large tables and complex queries that involve table joins. (See also, Design your Data Warehouse for Performance and Data Warehouse Workloads and Use Cases ).

    Some of the variables that you ll want to take into account when indexing the data warehouse are the type of data warehouse you have (i.e. primarily archive or primarily near real-time), how large the dimensions and fact tables are (and if the fact tables are partitioned), who will be accessing the data and how they ll do so, and whether access will be ad hoc or via structured application interfaces. These variables will determine how your indexing scheme should be structured. Here s a simple plan for indexing the relational tables that comprise a portion of your data warehouse. (Although I only explain how to index dimensions and fact tables in this article, I explain how to index the staging database in the Web-exclusive sidebar Indexing the Staging Database . ) Note that the relational tables are those that are managed by SQL Server s relational data engine, not those managed by the SQL Server Analysis Services (SSAS) engine.

    Indexing Dimensions

    You ll want to index the dimension key (primary key), which is a surrogate key, not a natural or transactional key such as customer name or customer ID. Note that you shouldn t cluster on the dimension key.

    The dimension will contain a natural or transactional key (e.g. a transaction number or identifier), which we ll call a business key, from the source system. Although the business key might not be unique as in the case of a type 2 response to slowly changing dimensions create a clustered index on the identity column, which you can see in Figure 1. The Customer and the Product dimensions have a clustered index built on the business key. By clustering on this key, you can enhance the query response when the business key is used in the WHERE clause. The expression in the WHERE clause is often used to search the dimensional data, and having the dimension records pre-sorted makes the query response faster.

    Clustering by the business key might also help you avoid lock escalation (i.e. row to table, intent-exclusive to exclusive) during the extraction, transformation, and loading (ETL) process, which could happen if the surrogate key was the cluster key and all the rows were being added at the end of the file. If the exclusive locks escalated from row to table, that would block read, other ETL load, or utility operations on the table and cause diminished query response and even application timeouts. If your users and applications can live with some latency, you can get around this problem by having them query database snapshots and reserving the original database exclusively for data loads.

    In Figure 1. the Date dimension and the Time dimension have no external datasource or business key. Instead of creating an identity-style primary key for these two tables, consider using a smart key, with a YYYYMMDD format for Date and an HHMMSSSSS format for Time (you can use fewer second positions, depending on how fine a time granularity you need to measure), and clustering on it. The values will maintain index order, range queries will be simplified in the fact table, and you ll need one less join when querying because the primary key will contain the date (or time).

    For large type 2 slowly changing dimensions (i.e. where you add a new row to record the change), you might want to create a four-part non-clustered index that includes the business key, the record begin date, the record end date, and the surrogate key. For efficiency and to prevent escalating storage requirements, INCLUDE the record end date and the surrogate key when creating the index instead of making them part of the index key, as shown in the following command:

    This command creates a covering index that can be useful during the ETL process and load operations and for historical queries. When you make RecordEnd- Date and SurrogateKEY INCLUDEs instead of part of the index key, the SQL Server engine stores these two values at only the leaf level of the index tree, thus reducing the storage requirements. By having these two columns in the index (i.e. creating a covering index), the SQL Server relational engine can get the data that it needs solely from the index during load and some query operations, without having to access data from the underlying dimension.

    If there are other columns in the dimension that will be used continuously for searching, sorting, or grouping, create non-clustered indexes on those columns as you would in a transactional database. If there s an embedded hierarchy in a dimension, such as the Category- SubCategory-ProductID hierarchy in the Product dimension, then consider indexing the components of the hierarchy if it will enhance query performance and won t inhibit data loading.

    Indexing the Fact Table

    Indexing the fact table is similar to indexing a dimension, although you must account for partitioning. You ll want to index and cluster on the date key or a combined date plus time. Because business intelligence (BI) analysis always seems to involve a date/time component, the fact table will have a date (or datetime) key, and clustering on this key will help with cube-building. Also, if the data records are already stored in date or datetime order, historical queries will have an execution advantage. If the fact table has more than one date or datetime column, cluster on the column that s used most often for querying or cube-building.

    If the fact table is partitioned on the date column, use that column as the clustering key. When you see the same column to create the clustered index that you used to create the partitions and creating the index in the same file group that holds the partitioned fact table, SQL Server will automatically partition the index the same way that the fact table is partitioned (i.e. the index will have the same partitioning function and column as the fact table). When the index is partitioned the same way the fact table is partitioned, the table and its index are said to be aligned, which makes for an optimal operational situation, especially if you anticipate creating additional partitions or making frequent partition switches.

    Next, create a non-clustered index on each of the foreign keys in the fact table, and consider combining the foreign key and the date key, in that order, similar to CustomerKEY + DateKEY in Figure 1. Creating a non-clustered key on the foreign keys works especially well if one or more of the associated dimensions is a type 2 slowly changing dimension. Rows with the same foreign key value will be searched in ascending date order, which will enhance the historical query response. Note that you ll want to retain relational integrity when dealing with the foreign keys. For more information about how to do so, see the Web-exclusive sidebar Retaining Relational Integrity in the Data Warehouse .

    Modifying Your Indexing Scheme

    Over time, your data warehouse will change to accommodate what s happening in your organization, and you ll have to modify your indexing scheme. Most data warehouse/BI systems will access these relational tables directly, so you can use tried-and-true transactional methods for tuning indexes, such as evaluating the query and data mix and adjusting it accordingly. If your relational data warehouse is used only to stage SSAS structures, then you might not need any more indexes than those we ve talked about. SSAS tends to use the same queries over and over again, so you can run the Index Tuning Wizard and tune exactly for that set of queries. Start simple, evaluate thoroughly, and build conservatively when indexing your data warehouse.


    Posted In: NEWS

    Tags: , , , , , , , ,

    Leave a Comment

    SQL Server Performance Monitoring through windows performance monitor counters #sql #server


    It seems your question is more related to Windows Performance Monitor Counters definitions, than with SQL Server, other than the fact that you collected counter objects into a SQL database table.

    what counters do i take into consideration.

    That depends on what type of performance condition or issue you are trying to troubleshoot. What exactly is the problem you are trying to solve?

    The types and definitions of Performance Monitor counters vary, depending on which version of Windows you are asking about and what products are installed (ie: Exchange, SQL Server, IIS, etc).

    Here’s what I came up with after a little research:

    svchost.exe is a generic host process name for services that run from dynamic-link libraries.

    HealthService is a database name on this server. svchost is probably some type of internal database used by windows to monitor all svchost processes.

    It appears that Exchange Server may be installed on this machine, is that correct?

    The items listed are the individual performance counter objects, but it would have been useful to see what parent object each are classified under. For example, Memory or Process are both parent performance counters with many instances of object types falling under each.

    You could probably benefit from studying a good overview of Window Performance Monitor . Hope that helps,

    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Friday, February 10, 2017 1:12 PM


    Posted In: NEWS

    Tags: , , , ,

    Leave a Comment

    BETWEEN in SQL #between #sql


    SQL BETWEEN Operator

    The BETWEEN operator is used when the filtering criteria is a continuous range with a maximum value and a minimum value. It is always used in the WHERE clause.


    The syntax for the BETWEEN operator is as follows:

    SELECT column_name
    FROM table_name
    WHERE column_name BETWEEN value1 AND value2 ;

    This will select all rows whose column has a value between value1 and value2.


    We use the following table for our examples.

    BETWEEN is an inclusive operator, meaning that ‘value1’ and ‘value2’ are included in the result. If we wish to exclude ‘value1’ and ‘value2’ but include everything in between, we need to change the query to the following:

    SELECT column_name
    FROM table_name
    WHERE ( column_name value1 )
    AND ( column_name value2 );

    Example 2

    We can also use the BETWEEN operator to exclude a range of values by adding NOT in front of BETWEEN. In the above example, if we want to show all rows where the Sales column is not between 280 and 1000, we will use the following SQL:

    SELECT *
    FROM Store_Information
    WHERE Sales NOT BETWEEN 280 and 1000;


    For these exercises, assume we have a table called User_Sales with the following data:

    1. Which of the following SQL statement is valid? (There can be more than one answer)
    a) SELECT * FROM User_Sales WHERE Total_Sales BETWEEN 200 OR 300;
    b) SELECT * FROM User_Sales WHERE Total_Sales IS BETWEEN 200 OR 300;
    c) SELECT * FROM User_Sales WHERE Total_Sales IS BETWEEN 200 AND 300;
    d) SELECT * FROM User_Sales WHERE Total_Sales BETWEEN 200 AND 300;

    2. How many records will be returned by the following query?
    SELECT * FROM User_Sales WHERE Join_Date BETWEEN ‘Apr-05-2015’ AND ‘Apr-10-2015’;

    3. How many records will be returned by the following query?
    SELECT * FROM User_Sales WHERE Gender = ‘F’ OR Total_Sales BETWEEN 50 AND 100;

    2. 4 records are returned. They are,

    3. 3 record are returned. They are,


    Posted In: NEWS

    Tags: ,

    Leave a Comment