tsql

Finding gaps (missing records) in database records using SQL

I have a table with records for every consecutive hour. Each hour has some value. I want a T-SQL query to retrieve the missing records (missing hours, the gaps). So for the DDL below, I should get a record for missing hour 04/01/2010 02:00 AM (assuming date range is between the first and last record). Using SQL Server 2005. Prefer a set ...

TSQL: Global Script Variable?

Hello all, I am making use of variables in my TSQL queries. As my script has grown, I have separated each part by GO, now the problem is I need access to variables at the top of my script. How can I still access these variables? Hopefully, this is something simple and straightforward. Thanks all ...

Same query has nested loops when used with INSERT, but Hash Match without.

I have two tables, one has about 1500 records and the other has about 300000 child records. About a 1:200 ratio. I stage the parent table to a staging table, SomeParentTable_Staging, and then I stage all of it's child records, but I only want the ones that are related to the records I staged in the parent table. So I use the below que...

sort items based on their appears count

Hello Experts, I have data like this d b c a d c b a b c a c a d c if you analyse, you will find the appearance of each element as follows a: 4 b: 3 c: 5 d: 2 According to appearance my sorted elements would be c,a,b,d and final output should be c b d a d c b a b c a c a d c Any clue, how we can achieve th...

T-SQL Specific Syntax problem (Simple no doubt)

Hi guys, I have an issue with a query I'm trying to run on some data, I guess the place to start is to describe the data. Ok so I have a list of email addresses, each email address has a unique ID and an account ID Also in my tables I have a set number which auto incrememnts, this will allow me to target duplicate email addresses Wha...

Usage of SP_OACreate, SP_OAMethod etc.. is a security risk ?

I am told that usage of SP_OACreate , SP_OAMethod in SQL Server 2000 is of a security risk. I am using Strong Name in the assembly and is stored in GAC on the SQL Server Machine. What are the security implications/compromise ? ...

Query to work out possible destinations on a set of routes using SQL Server 2008

I'm writing an application that models train routes, which are stored in the database table [TrainStop] as follows: TrainServiceId StationCode StopIndex IsEnabled So a given train follows a route and consists of several rows with the StopIndex indicating the order. The problem I am trying to solve is to say which stations a user can ge...

T-SQL Syntax Issue Else if style logic

Hi guys, two questions today, I'm a busy bee and luckily I have an awesome community at my disposal! My issue here is this: I have a field i need to update based on existing field data. If Gender = F then foo = 1 If Gender = M then foo = 2 If Gender = Male then foo = 2 If Gender = Female then foo = 1 If Gender is not above then fo...

Combine First, Middle Initial, Last name and Suffix in T-SQL (No extra spaces)

I'm trying not to reinvent the wheel here...I have these four fields [tbl_Contacts].[FirstName], [tbl_Contacts].[MiddleInitial], [tbl_Contacts].[LastName], [tbl_Contacts].[Suffix] And I want to create a FullName field in a view, but I can't have extra spaces if fields are blank...So I can't do FirstName + ' ' + MiddleInitial + ' ' + L...

SQL Server - Schema/Code Analysis Rules - What would your rules include?

We're using Visual Studio Database Edition (DBPro) to manage our schema. This is a great tool that, among the many things it can do, can analyse our schema and T-SQL code based on rules (much like what FxCop does with C# code), and flag certain things as warnings and errors. Some example rules might be that every table must have a prima...

Getting maximum value of float in SQL programatically

Is there an method for programatically (in T-SQL) retrieving the maximum (and minimum) value of a datatype? That it would act like float.MaxValue in C#. I would like to use it in some selection when the parameter does not equal any actual values in the database, so I would use something like declare @min float declare @max float --fill...

T-sql COLLATE and Varchar(max)

Hi When u use Varchar(max), it is 8000 chars for a variable, and around 2^32 for a column, what is COLLATE and how it affects that? Thanks ...

SQL Get Latest Unique Rows

I have a log table, each row representing an object logging its state. Each object has a unique, unchanging GUID. There are multiple objects logging their states, so there will be thousands of entries, with objects continually inserting new logs. Everytime an object checks in, it is via an INSERT. I have the PrimaryKey, GUID, ObjectStat...

SQL Server "Group By" for an interesting case

Hello, I have a table like that ID ORDER TEAM TIME IL-1 1 A_Team 11 IL-1 2 A_Team 3 IL-1 3 B_Team 2 IL-1 4 A_Team 1 IL-1 5 A_Team 1 IL-2 1 A_Team 5 IL-2 2 C_Team 3 What I want is grouping the same named teams which are also sequential teams (Which is according t...

Sorting tree with other column in SQL Server 2008

Hi, I have a table which implements a tree using hierarchyid column Sample data: People \ Girls \1\ Zoey \1\1\ Kate \1\2\ Monica \1\3\ Boys \2\ Mark \2\1\ David \2\2\ This is the order using hierarchyid column as sort column I ...

Get all or part result from sql using one TSQL commnd

Hi All Here is my condition. There is a Text box in a form, if you don't input any thing, it would return all rows in this table. If you input something, it would return rows whose Col1 matches the input. I try to use the sql below to do it. But there is one problem, these columns allows Null value. It wouldn't return the row with NULL ...

SQL Server - What coding/development rules does your team have in place?

I asked a similar question last week and didn't get a very good response, so perhaps I didn't phrase the question in the right way. I'd like to know what processes/policies/rules your team has in place for writing T-SQL code and database schema. Here are a couple examples: 1) All foreign key columns should be indexed 2) All primary ke...

Search by nvarchar

Hi all. I have this problem. In table I have column which is nvarcar type. and row in this column is row1= 1;6 row2 = 12 row3 =6;5;67 etc... I try to search this column. for example when i send 1 i try to get only row1. I use LIKE but in result set I get row1 and row2. How can I achieved this, any help is appreciated. Tnx... ...

Using IF in T-SQL weakens or breaks execution plan caching?

It has been suggest to me that the use of IF statements in t-SQL batches is detrimental to performance. I'm trying to find some confirmation of this assertion. I'm using SQL Server 2005 and 2008. The assertion is that with the following batch:- IF @parameter = 0 BEGIN SELECT ... something END ELSE BEGIN SELECT ... something e...

On Update: Auto Update Date/Time Field

Hello all, Is there an equivalent of MySQLs on update field for SQL server? With both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses, the column has the current timestamp for its default value, and is automatically updated. What I am looking for is to have a date/time field and every time I update a reco...