| ||||||
MSSQLPosts related to Microsoft SQL Server as a relational database management system, Transact-SQL, performance, hands-on development, 3rd party tools. Tips and tricks, configuration and t-sql best practices and performance considerations. Info related to editions like SQL Server Express Edition and future versions - like Katmai.T-SQL: Get Filename from FilepathMany times applications store operating system paths to files in the database. And sometimes need to get only Filename portion out of Filepath column in MSSQL ? Disclaimer: I know - it's much easier to get the filename using C#, however [ ... ] posted @ Friday, June 27, 2008 3:13 AM | Your thoughts MySql: Add a Column at a Specific PositionI'm playing with MySql for the 1st time in my life recently. Rather quickly I'm getting used to it mostly due to my experience with MSSQL as DBA. While changing table structure [ ... ] posted @ Wednesday, May 28, 2008 7:17 PM | Your thoughts MSSQL 2008: Spatial Data Types - Geography and GeometryIn addition to new DATETIME data types introduced in MSSQL 2008 there are geography and geometry data types introduced - useful for storing and manipulating geodetic data. Geometry data type is used to store flat map data like points, lines, and shapes [ ... ] posted @ Thursday, May 15, 2008 2:28 AM | Your thoughts MSSQL 2008: What is DATETIME2MSSQL 2008 introduces several new DATETIME datatypes for T-SQL: TIME, DATE, DATETIME2, DATETIMEOFFSET. It is obvious that TIME and DATE provide the ability to store separately DATE and TIME parts of DATETIME. It was not possible to do in previous versions of MSSQL, however [ ... ] posted @ Tuesday, May 13, 2008 7:13 PM | Your thoughts T-SQL: LIKE Operator and Wildcard CharactersAn interesting issue I came into recently. Suppose you have a search procedure - which consists of some SELECT query which gets a query phrase as a parameter and searches through a table coulumn using LIKE [ ... ] posted @ Thursday, May 8, 2008 2:37 AM | Your thoughts MSSQL: Search and Replace in a TEXT(NTEXT) columnSometimes we need to search and replace a text value in the entire table. The column in question is of TEXT or NTEXT datatype. T-SQL REPLACE function does not work with TEXT/NTEXT datatype. Instead we have to use several other functions [ ... ] posted @ Saturday, May 3, 2008 8:16 PM | Your thoughts T-SQL: Get Age from Date of BirthSometimes it is useful for social oriented networks to calculate on the fly or store user age given his/her date of birth. A small trick [ ... ] posted @ Friday, August 10, 2007 6:15 PM | Your thoughts MSSQL 2005 Tip: Using TOP with SubquerySQL Server 2005 introduced new TOP behavior - like TOP function supports variables and expressions. Some examples [ ... ] posted @ Sunday, August 5, 2007 10:53 PM | Your thoughts T-SQL Query Processing OrderOne of the most important aspects in optimizing MSSQL queries is understanding the steps order the query is processed since the query is processed in the order that is different than the code written. IMHO, it is the main difficulty in understanding complicated queries [ ... ] posted @ Saturday, June 30, 2007 9:29 PM | Your thoughts How to Insert DBCC Output into MSSQL TableDBCC can be used with WITH TABLERESULTS hint that outputs the output in the table rowset format. So - here is a small code sample that demonstrates the technique [ ... ] posted @ Saturday, June 23, 2007 2:54 AM | Your thoughts MSSQL: sp_xml_preparedocument is not supported in fibers modeBlog entry about MSSQL: sp_xml_preparedocument is not supported in fibers mode [ ... ] posted @ Friday, January 12, 2007 5:25 PM | Your thoughts MSSQL: Got arithmetic overflow for INT ?Blog entry about MSSQL: Got arithmetic overflow for INT ? [ ... ] posted @ Thursday, December 7, 2006 11:21 PM | Your thoughts MSSQL 2005 Express Edition - unbeatableBlog entry about MSSQL 2005 Express Edition - unbeatable [ ... ] posted @ Sunday, November 12, 2006 12:53 AM | Your thoughts T-SQL: The Rozenshtein MethodBlog entry about T-SQL: The Rozenshtein Method [ ... ] posted @ Sunday, July 9, 2006 1:38 AM | Your thoughts Always Use Stored Procedures in MSSQLBlog entry about Always Use Stored Procedures in MSSQL [ ... ] posted @ Saturday, June 10, 2006 7:15 AM | Your thoughts MSSQL 2005 Service Pack 1 is availableBlog entry about MSSQL 2005 Service Pack 1 is available [ ... ] posted @ Thursday, April 20, 2006 12:30 AM | Your thoughts Enterprise Library: Bug with decimal in DataAccess blockBlog entry about Enterprise Library: Bug with decimal in DataAccess block [ ... ] posted @ Thursday, March 30, 2006 2:22 AM | Your thoughts Microsoft Query Optimization Team is BloggingQuery Optimization Team for Microsoft's SQL Server product is blogging ... ... help people better understand query plans, physical schema design in databases, making your application perform better, and anything else related to query optimization [ ... ] posted @ Saturday, March 25, 2006 4:15 PM | Your thoughts MSSQL 2005: Paging with ROW_NUMBER is the fastestBlog entry about MSSQL 2005: Paging with ROW_NUMBER is the fastest [ ... ] posted @ Friday, March 24, 2006 10:23 PM | Your thoughts MSSQL: Managing Schema Changes on Large TablesBlog entry about MSSQL: Managing Schema Changes on Large Tables [ ... ] posted @ Friday, March 3, 2006 4:46 AM | Your thoughts The most important thing in SQLBlog entry about The most important thing in SQL [ ... ] posted @ Friday, March 3, 2006 4:17 AM | Your thoughts T-SQL: Get only Date from DateTimeBlog entry about T-SQL: Get only Date from DateTime [ ... ] posted @ Friday, February 17, 2006 12:48 AM | Your thoughts T-SQL: Date RangesMSSQL query - how do you find out how many hours each employee worked per day over the last month ? First of all when we talk about data ranges one can definitely find high performance sql-based solution just because of the fact that MSSQL clustered index [ ... ] posted @ Thursday, February 16, 2006 9:22 PM | Your thoughts Date Processing - T-SQL or SQLCLR ?Blog entry about Date Processing - T-SQL or SQLCLR ? [ ... ] posted @ Tuesday, January 3, 2006 12:57 AM | Your thoughts MSSQL Dynamic Management Views (DMVs) for indexesBlog entry about MSSQL Dynamic Management Views (DMVs) for indexes [ ... ] posted @ Friday, December 30, 2005 3:55 PM | Your thoughts MSSQL 2005 System Tables MapMSSQL 2005 system tables map is available for download. The Microsoft SQL Server 2005 System Views Map shows the key system views included in SQL Server 2005, and the relationships between them [ ... ] posted @ Thursday, December 22, 2005 11:40 PM | Your thoughts MSSQL: How to migrate a database serverBlog entry about MSSQL: How to migrate a database server [ ... ] posted @ Sunday, December 11, 2005 3:35 AM | Your thoughts MSSQL: Partitioning and ParallelismBlog entry about MSSQL: Partitioning and Parallelism [ ... ] posted @ Friday, December 2, 2005 4:38 PM | Your thoughts MSSQL 2005: Troubleshooting Performance methodologyBlog entry about MSSQL 2005: Troubleshooting Performance methodology [ ... ] posted @ Tuesday, November 22, 2005 2:41 AM | Your thoughts T-SQL: Paging with ROW_NUMBER()Blog entry about T-SQL: Paging with ROW_NUMBER() [ ... ] posted @ Friday, November 18, 2005 11:59 PM | Your thoughts Show MSSQL Query Estimated Execution Plan in XML Format (SET showplan_xml ON)Another cool new feature introduced in MSSQL 2005 - SET showplan_xml ON - will show T-SQL query estimated execution plan in XML format. In addition there is a new query hint introduced in MSSQL 2005 called USE PLAN to force using the plan just saved with SET showplan_xml [ ... ] posted @ Monday, November 14, 2005 3:03 AM | Your thoughts T-SQL 2005: One-to-many comma separatedBlog entry about T-SQL 2005: One-to-many comma separated [ ... ] posted @ Sunday, November 13, 2005 11:44 PM | Your thoughts Oracle: development tool neededBlog entry about Oracle: development tool needed [ ... ] posted @ Friday, October 7, 2005 9:01 PM | Your thoughts MSSql 2005: XML indexes and XML queriesBlog entry about MSSql 2005: XML indexes and XML queries [ ... ] posted @ Monday, October 3, 2005 11:56 PM | Your thoughts MSSQL 2005: XQuery Implementation in RDBMSBlog entry about MSSQL 2005: XQuery Implementation in RDBMS [ ... ] posted @ Saturday, September 24, 2005 11:55 PM | Your thoughts Primary Key best practices: INT identity vs. UniqueIdentifier (GUID)Blog entry about Primary Key best practices: INT identity vs. UniqueIdentifier (GUID) [ ... ] posted @ Friday, September 2, 2005 1:57 AM | Your thoughts How to change column datatype in Oracle table (ALTER TABLE).Blog entry about How to change column datatype in Oracle table (ALTER TABLE). [ ... ] posted @ Tuesday, August 30, 2005 1:28 AM | Your thoughts The DBA Guide to the SQL Server Database Engine .NET CLR (whitepaper)MSDN whitepaper to bookmark - The DBA Guide to the SQL Server Database Engine .NET CLR (Kimberly L. Tripp) [ ... ] posted @ Sunday, August 28, 2005 12:52 AM | Your thoughts MSSQL 2000: Service Pack 4Blog entry about MSSQL 2000: Service Pack 4 [ ... ] posted @ Friday, May 6, 2005 8:52 PM | Your thoughts MSSQL 2005: BACKUP LOG changesBlog entry about MSSQL 2005: BACKUP LOG changes [ ... ] posted @ Wednesday, May 4, 2005 9:33 PM | Your thoughts MSSQL: The value of Default ConstraintBlog entry about MSSQL: The value of Default Constraint [ ... ] posted @ Friday, April 1, 2005 10:40 PM | Your thoughts MSSQL 2000: Row Count Using sysindexes System TableBlog entry about MSSQL 2000: Row Count Using sysindexes System Table [ ... ] posted @ Thursday, March 31, 2005 4:22 AM | Your thoughts MCP Hall of FameBlog entry about MCP Hall of Fame [ ... ] posted @ Tuesday, March 22, 2005 9:46 PM | Your thoughts MSSQL: Paul McBride on ISNULL vs. COALESCEBlog entry about MSSQL: Paul McBride on ISNULL vs. COALESCE [ ... ] posted @ Thursday, March 17, 2005 10:54 PM | Your thoughts MSSQL: Quest Software to compete Red-GateBlog entry about MSSQL: Quest Software to compete Red-Gate [ ... ] posted @ Saturday, March 5, 2005 1:21 AM | Your thoughts SQL Server 2005 Product Line publishedBlog entry about SQL Server 2005 Product Line published [ ... ] posted @ Friday, February 25, 2005 12:30 AM | Your thoughts Advanced MSSQL 2005: Partitioning, memory management, etcBlog entry about Advanced MSSQL 2005: Partitioning, memory management, etc [ ... ] posted @ Sunday, February 6, 2005 1:33 AM | Your thoughts More MSSQLBlog entry about More MSSQL [ ... ] posted @ Friday, January 14, 2005 1:54 AM | Your thoughts How to Use XMLHTTP from within T-SQL (sp_oacreate)Adam Machanic points out how to use MSXML2.ServerXMLHTTP.3.0 from within T-SQL using OLE objects. This is to perform different tasks. However need to take into account that only members of the sysadmin [ ... ] posted @ Friday, January 14, 2005 1:16 AM | Your thoughts T-SQL Tip: How to Use GETDATE() in User Defined FunctionSeems you it is impossible to use GETDATE() to get the current date in MSSQL user defined function because of determinism. However it is possible to get the desired functionality with the help of the following little trick [ ... ] posted @ Thursday, December 16, 2004 10:32 PM | Your thoughts T-SQL: Search LogicA link to the piece of T-SQL code wich demonstrates how to implement search, similar to Google's, if you don't want to (or can't) use full text indexing [ ... ] posted @ Sunday, November 7, 2004 9:28 AM | Your thoughts T-SQL DATETIME : Last Day of the MonthBlog entry about T-SQL DATETIME : Last Day of the Month [ ... ] posted @ Sunday, September 26, 2004 1:22 AM | Your thoughts T-SQL trick: How to Know Record Row Number By Id ColumnBlog entry about Small T-SQL trick [ ... ] posted @ Friday, September 17, 2004 6:05 AM | Your thoughts How to Insert Millions Records into Database TableSuppose you need to insert millions of rows to database table. There are many ways that can accomplish this task fast, very fast or whatever. But the question is to perform the insert and not to produce locks on selects while the system is online and running which is more complicated [ ... ] posted @ Wednesday, September 15, 2004 3:05 AM | Your thoughts T-SQL: Update and Output in a Single QuerySuppose one have to update table data and after that query the same table. It seems like a common task and it is possible to perform this using a single T-SQL statement [ ... ] posted @ Saturday, August 21, 2004 5:34 AM | Your thoughts MSSQL 2005 beta 2 DE first impressionsBlog entry about MSSQL 2005 beta 2 DE first impressions [ ... ] posted @ Saturday, July 31, 2004 8:03 AM | Your thoughts MSSQL OverusabilityBlog entry about MSSQL Overusability [ ... ] posted @ Friday, July 30, 2004 4:36 PM | Your thoughts SQL Server 2005 Developer Edition Beta 2 (English)Blog entry about SQL Server 2005 Developer Edition Beta 2 (English) [ ... ] posted @ Tuesday, July 27, 2004 3:33 AM | Your thoughts MSSQL 6.5 Service Pack 5 is AvailableService Pack 5 for MSSQL 6.5 is now available for MSDN universal subscribers. Sometimes it is good to update old products. [ ... ] posted @ Tuesday, July 27, 2004 3:23 AM | Your thoughts SQL Server 2005 Documentation and SamplesBlog entry about SQL Server 2005 Documentation and Samples [ ... ] posted @ Friday, July 23, 2004 2:59 AM | Your thoughts SQL Express is not ready yetBlog entry about SQL Express is not ready yet [ ... ] posted @ Saturday, July 10, 2004 7:12 AM | Your thoughts Scheduling algorithm using MSSQL sysjobschedulesBlog entry about Scheduling algorithm using MSSQL sysjobschedules [ ... ] posted @ Friday, July 9, 2004 5:18 PM | Your thoughts Sql Server 2005 EE issuesBlog entry about Sql Server 2005 EE issues [ ... ] posted @ Sunday, July 4, 2004 12:50 AM | Your thoughts Moving SQLBlog entry about Moving SQL [ ... ] posted @ Monday, June 28, 2004 3:08 AM | Your thoughts Stored Procedures Code SearchBlog entry about Stored Procedures Code Search [ ... ] posted @ Tuesday, June 22, 2004 2:47 AM | Your thoughts SQLBPA and order by constantBlog entry about SQLBPA and order by constant [ ... ] posted @ Wednesday, June 16, 2004 1:58 AM | Your thoughts T-SQL Paging Series. RowCountBlog entry about T-SQL Paging Series. RowCount [ ... ] posted @ Saturday, May 29, 2004 10:12 PM | Your thoughts Check Constraint vs. RuleBlog entry about Check Constraint vs. Rule [ ... ] posted @ Tuesday, May 25, 2004 3:37 AM | Your thoughts Rendering depends on HTML source formattingBlog entry about Rendering depends on HTML source formatting [ ... ] posted @ Monday, May 17, 2004 6:18 AM | Your thoughts sp_sproc_columns and ProfilerBlog entry about sp_sproc_columns and Profiler [ ... ] posted @ Friday, April 23, 2004 8:30 PM | Your thoughts SQL Function for Date Sequences (Version 2.1)Another version of the function that demonstrates how to generate date sequences in SQL using loop queries. To create a loop see how I use union all operator [ ... ] posted @ Thursday, April 15, 2004 2:43 AM | Your thoughts MSSQL: Some Undocumented FeaturesBlog entry about MSSQL: Some Undocumented Features [ ... ] posted @ Sunday, April 11, 2004 10:13 PM | Your thoughts Generate Calendar Dates. Version 2.0Blog entry about Generate Calendar Dates. Version 2.0 [ ... ] posted @ Saturday, April 10, 2004 4:14 AM | Your thoughts My howto on sqljunkiesBlog entry about My howto on sqljunkies [ ... ] posted @ Wednesday, April 7, 2004 6:52 AM | Your thoughts How to PIVOT Tables in MSSQL 2000Yes, you can pivot tables easily in MSSQL 2005 (aka yukon) easily but how can we do it in MSSQL 2000. The SQL query code is provided in the post [ ... ] posted @ Friday, April 2, 2004 11:50 PM | Your thoughts QA vs VS2003. Debugging stored procedure.Blog entry about QA vs VS2003. Debugging stored procedure. [ ... ] posted @ Friday, April 2, 2004 7:18 PM | Your thoughts Year calendar 2004Blog entry about Year calendar 2004 [ ... ] posted @ Saturday, March 13, 2004 8:24 AM | Your thoughts Yukon 2005 and Whidbey 2005Blog entry about Yukon 2005 and Whidbey 2005 [ ... ] posted @ Thursday, March 11, 2004 5:45 AM | Your thoughts Optional parameters in T-SQL: COALESCE vs. ISNULL vs. IS NULL ORBlog entry about Optional parameters in T-SQL: COALESCE vs. ISNULL vs. IS NULL OR [ ... ] posted @ Monday, March 1, 2004 3:59 AM | Your thoughts MSSQL Reset Identity: TRUNCATE TABLE vs. DBCC CHECKIDENT RESEEDBlog entry about MSSQL Reset Identity: TRUNCATE TABLE vs. DBCC CHECKIDENT RESEED [ ... ] posted @ Saturday, December 13, 2003 4:13 AM | Your thoughts Why app roles are bad in MSSQLBlog entry about Why app roles are bad in MSSQL [ ... ] posted @ Saturday, December 13, 2003 3:35 AM | Your thoughts
|
|
|||||
| ||||||
