Browse by Tags

• access
• analysis
• api
• application
• application role
• backups
• best practices
• blocking
• blogs
• cache
• catalog
• clr
• columns
• community
• computed
• create
• crosstab
• cte
• cursor
• data file
• database
• database mirroring
• databases
• datatype
• datetime
• dmv
• dynamic
• dynamic sql
• enhancement
• enterprise
• enterprise manager
• expiry
• field
• forced
• grant
• heavy
• high availability
• information schema
• injection
• ip address
• joins
• kba
• learning
• LINQ
• location
• locks
• log file
• login
• long
• managed code
• mdx
• memory
• metadata
• Microsoft
• mirror
• object_name
• optimization
• parsename
• password
• perfmon
• performance
• permission
• pivot
• primary key
• principal
• privileges
• property
• query
• recursive
• running
• script
• security
• server
• SQL Server
• SQL Server 2008
• ssas
• SSMS
• statistics
• stored procedure
• sysmon
• system
• table
• tables
• transaction
• trigger
• tuning
• update statistics
• usage
• version
• view
• witness
• x64

• Be careful with constraints calling UDFs

  You might just not get what you think. I would be surprised if this hasn't been blogged already, but if so, it would be worth repeating. Here's the deal (example from a forum,, slightly re-worked): I want the values in one column to be unique, assuming...( Read More... Posted Friday, December 18, 2009 3:28 AM from Tibor Karaszi | (Comments Off) Filed under: tsql

• My datetime article updated for 2008

  Actually, the article has been out for about a month now, but I first solicited feedback on errors and typo's and then I forgot to mention the update here... Anyhow, the article now also covers 2008 and the new date, time, datetime2 and datetimeoffset Read More... Posted Wednesday, September 24, 2008 12:10 AM from Tibor Karaszi | (Comments Off) Filed under: tsql, SQL Server 2008, datetime

• Non-trusted constraints

  A discussion in the newsgroups before the holidays lead to trusted constraints and performance. This inspired me to blog about it, but I decided to have some vacation first :-). Instead of having one long article, I'll do a two-part. This one is about Read More... Posted Sunday, January 13, 2008 1:13 AM from Tibor Karaszi | (Comments Off) Filed under: tsql

• Sorted views...

  Here's one that pops up regurarly. Something like: "My views are no longer ordered in SQL Server 2005. I have ORDER BY in the view, but when I select from it, the rows are not returned according to the ORDER BY." Since I tend to post a reply often enough Read More... Posted Wednesday, November 28, 2007 7:21 PM from Tibor Karaszi | (Comments Off) Filed under: tsql

• Another Best Practices article: Identifying and Resolving MDX Query Performance Bottlenecks

  Best Practices are good enough to follow, but simply do not read and digest. Try to implement them within your environment to keep up the performance. Similary there are many things involved within the SQL Server 2005 Analysis Services such as MDX queries, Read More... Posted Wednesday, September 05, 2007 7:34 PM from SSQA.net | (Comments Off) Filed under: SQL Server, performance, query, tsql, ssas, tuning, best practices, mdx

• Triggers within CLR - advantage over TSQL

  You may be aware the DML and DDL triggers can be nested up to 32 levels, because any reference to such trigger code counts as one-level in the nesting limit. Even though it is possible to control whether AFTER triggers can be nested through the nested Read More... Posted Tuesday, September 04, 2007 7:10 PM from SSQA.net | (Comments Off) Filed under: cte, SQL Server, performance, query, tsql, clr, table, trigger, recursive

• TSQL to detect long running queries against the database

  When I'm performing a performance analysis on a 24/7 application and dealing with PSS I had been given the following TSQL to identify the long running queries against a database. select r.session_id, s.host_name, s.program_name, s.host_process_id, r.status, Read More... Posted Wednesday, August 29, 2007 9:37 PM from SSQA.net | (Comments Off) Filed under: SQL Server, performance, databases, query, tsql, running, long

• TSQL cursors vs API cursors - poor cursor usage

  Cursors are based for a definitive purpose, but heavy usage of such methods will prove as costly expense on database performance. The process of cursor is prolonged, as a cursor first has to be defined with its features set, then populated after positioning Read More... Posted Monday, August 27, 2007 11:54 PM from SSQA.net | (Comments Off) Filed under: SQL Server, performance, tsql, cursor, api, usage, heavy, application

• FIX: Error message when you run a stored procedure that starts a transaction that contains a Transact-SQL statement in SQL Server 2005: "New request is not allowed to start because it should come with valid transaction descriptor"

  The following pages were recently modified. Source: Knowledge Base Product: Microsoft SQL Server 2005 Enterprise Edition, SQL Server 2005 Standard Edition & SQL Server 2005 Enterprise X64 Edition Notification Contents: New and Major Modifications Read More... Posted Thursday, August 23, 2007 9:25 PM from SSQA.net | (Comments Off) Filed under: SQL Server, x64, tsql, transaction, stored procedure, kba, enterprise

• TSQL to find all triggers in a database including their count

  For SQL Server 2000 version: SELECT S2.[name] TableName, S1.[name] TriggerName, CASE WHEN S2.deltrig = s1.id THEN 'Delete' WHEN S2.instrig = s1.id THEN 'Insert' WHEN S2.updtrig = s1.id THEN 'Update' END 'TriggerType' , 'S1',s1.*,'S2',s2.* FROM sysobjects Read More... Posted Thursday, August 23, 2007 6:28 PM from SSQA.net | (Comments Off) Filed under: SQL Server, tables, tsql, version, trigger

• How to Analyze the PERFMON (SYSMON) data?

  As you may aware the PERFMON (SYSMON) utility provides much information to analyze on the systems resource usage, I support and suggest to make use of this tool as much as you can with a default templates within our environment. You may be aware the data Read More... Posted Tuesday, August 21, 2007 4:26 AM from SSQA.net | (Comments Off) Filed under: SQL Server, performance, databases, tsql, analysis, tuning, sysmon, perfmon

• Get basic Database Mirroring information- principal or mirror

  SELECT DB_NAME(database_id) AS 'DatabaseName' , mirroring_role_desc , mirroring_safety_level_desc , mirroring_state_desc , mirroring_safety_sequence , mirroring_role_sequence , mirroring_partner_instance , mirroring_witness_name , mirroring_witness_state_desc Read More... Posted Monday, August 20, 2007 8:32 AM from SSQA.net | (Comments Off) Filed under: SQL Server, tsql, database, database mirroring, principal, high availability, witness, mirror

• TSQL to generate GRANT statements from a database

  select p . state_desc + ' ' + p . permission_name + ' OBJECT::' + s . name collate Latin1_general_CI_AS + o . name collate Latin1_general_CI_AS + ' TO ' + u . name collate Latin1_general_CI_AS + 'GO' , p .* from sys.database_permissions p inner join sys.objects Read More... Posted Friday, August 17, 2007 8:45 PM from SSQA.net | (Comments Off) Filed under: SQL Server, performance, tsql, access, system, permission, grant

• Dynamic Crosstab with multiple pivot columns

  Jeff Smith in his weblog showed how to generate Crosstab reports using a stored procedure. I had a requirement to generate crosstab reports with more than one pivot column. So I used the same approach he used and modified his procedure as shown below Read More... Posted Thursday, August 16, 2007 11:54 PM from SSQA.net | (Comments Off) Filed under: community, SQL Server, tsql, dynamic, pivot, crosstab

• TSQL methods to display the owner of schema in a database.

  You can use 2 methods to get information about owner of a particular schema or set of schema(s) within the database. Using INFORMATION_SCHEMA views method (which I prefer most of the times): USE <DBName> GO SELECT * FROM INFORMATION_SCHEMA.SCHEMATA Read More... Posted Wednesday, August 15, 2007 7:23 PM from SSQA.net | (Comments Off) Filed under: tsql, system, information schema, catalog