User-Defined Functions SQL Server 2005 flagged incorrectly as non-deterministic?

Related to this question, I decided to check the UDFs in my data warehouse (which should largely have been deterministic), and I found several which aren’t which should be. For instance: CREATE FUNCTION [udf_YearFromDataDtID] ( @DATA_DT_ID int ) RETURNS int AS BEGIN RETURN @DATA_DT_ID / 10000 END Shows up in this query: SELECT ROUTINE_NAME FROM […]

SQL Query to get a row, and the count of associated rows

I have two tables, like this: #Articles: ID | Title 1 “Article title” 2 “2nd article title” #Comments: ID | ParentID | Comment 1 1 “This is my comment” 2 1 “This is my other comment” I’ve always wanted to know, what is the most elegant way to get the following result: ID | Title […]

SQL: Using Top 1 in UNION query with Order By

I have a table as below Rate Effective_Date —- ————– 5.6 02/02/2009 5.8 05/01/2009 5.4 06/01/2009 5.8 12/01/2009 6.0 03/15/2009 I am supposed to find the all rates that are effective for current date and after it. So to get the current effective rate, i use SELECT TOP 1 * from table where effective_date < […]

How do I control the format in which MS-SQL Server returns datetime values?

Specifically, I wish to get the date format in a pure (ISO) format: YYYY-MM-DD HH:mm:ss I’m looking for a SET command or something that I can use. I do not wish to rely on the culture setting of the server. Note: I’m interested in the string format in which dates are returned, not entered.

What tools are out there for profiling stored procedures in SQL server other than the MS profiler?

It seems like there should be better tools out there for profiling sql than SQL Server Profiler. It takes forever to set up a profile session. I’m looking for a tool which is more like VS profiler. Just choose a stored procedure, and run it with a set of parameters, and what I should get […]

“Favorites” or “macros” in SQL Server Management studio?

I have a few databases that I always use SQL Server Management Studio with. I’d like to be able to create a toolbar button or keyboard shortcut that automatically opens a new query window (in the current SSMS instance) and connects to a given (registered, perhaps) database. That’s it. That’s all I need. And this […]

subselect vs outer join

Consider the following 2 queries: select tblA.a,tblA.b,tblA.c,tblA.d from tblA where tblA.a not in (select tblB.a from tblB) select tblA.a,tblA.b,tblA.c,tblA.d from tblA left outer join tblB on tblA.a = tblB.a where tblB.a is null Which will perform better? My assumption is that in general the join will be better except in cases where the subselect returns […]

Lucene and SQL Server – best practice

I am pretty new to Lucene, so would like to get some help from you guys 🙂 BACKGROUND: Currently I have documents stored in SQL Server and want to use Lucene for full-text/tag searches on those documents in SQL Server. Q1) In this case, in order to do the keyword search on the documents, should […]

How to encrypt and decrypt highly sensitive information in SQL Server database with ASP Classic?

I have been checking various questions on stackoverflow and of course google, but I can’t really find any specific solution to this question: How do I create a function in ASP Classic to encrypt and decrypt highly sensitive information in a SQL Server database? Like fx. a social security number or anything similar? (Or is […]

SELECT COUNT(DISTINCT… ) error on multiple columns?

I have a table, VehicleModelYear, containing columns id, year, make, and model. The following two queries work as expected: SELECT DISTINCT make, model FROM VehicleModelYear SELECT COUNT(DISTINCT make) FROM VehicleModelYear However, this query doesn’t work SELECT COUNT(DISTINCT make, model) FROM VehicleModelYear It’s clear the answer is the number of results returned by the first query, […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.