New answers tagged

1

try this - you might get the best of both select manyfield from ( select manyfield , row_number() over (partition by t1.id order by t1.timestamp desc) as rownum from inserted left join sometable t1 on inserted.id = t1.id ) tt where rownum = 1 or t1.id is null


0

Assuming you missed a column call LastLoginTime in User table or some other Log table, here goes the query. USE ABC; IF NOT EXISTS (SELECT * FROM sys.objects WHERE name = 'Not_loggedinUsers' AND type ='U') CREATE TABLE Not_loggedinUsers ( Email NVARCHAR(100), NoticeCount INT ); SELECT UserEmail Email INTO #oldUsers FROM User GROUP BY UserEmail ...


6

Without using REVERSE... It's always fun to use a recursive solution ;) (I did mine in SQL Server 2012, earlier versions might have limitations on recursion) create function dbo.IsPalindrome (@s varchar(max)) returns bit as begin return case when left(@s,1) = right(@s,1) then case when len(@s) < 3 then 1 else ...


2

select author from article where author IS NOT NULL and author <> '' except select name from author since any comparison the null is false I think below works select author from article where author <> '' except select name from author


-1

I assume that you have the following tables: CREATE TABLE Author(AutorID INT, Name VARCHAR(50)); CREATE TABLE Article(ArticleID INT, Article VARCHAR(MAX),Author VARCHAR(50)); Now to achieve your result just run the following query: SELECT DISTINCT Article.Author WHERE Article.Author NOT IN (SELECT Author.Name FROM Author) AND Article.Author IS NOT ...


0

You need only one column in the final output from Article table. So need to join Article and Author table. This can be done using subquery. Please Try this: select distinct name from article where name not in (select name from Author) and name is not null and name != ''


1

If a question (in a Microsoft SQL Server specific context) asks for "a T-SQL statement to do X", then they are just asking for any SQL statement that would be valid on a SQL Server instance. It does not have to use syntax that only works on SQL Server (but not MySQL etc). Standard ANSI SQL is fine if it works on SQL Server. (Not all ANSI SQL is valid on ...


13

Since there are a fair number of solutions I'm going to go with the "critique" part of your question. A couple of notes: I've fixed some typos and noted where I did. If I'm wrong about them being a typo mention it in the comments and I'll explain what's going on. I'm going to point out several things that you may already know, so please don't take offense ...


10

The REVERSE() method "improved", i.e. reversing only half of the string: SELECT CASE WHEN RIGHT(@string, LEN(@string)/2) = REVERSE(LEFT(@string, LEN(@string)/2)) THEN 1 ELSE 0 END AS Palindrome;


4

A major issue you're going to run into is that with any value greater than 1, LEFT or RIGHT will return multiple characters, not the character at that position. If you wanted to keep with this method of test, a really simple way to modify it would be RIGHT(LEFT(String,@n),1)=LEFT(RIGHT(String, @StringLength),1) This will always grab the rightmost ...


13

You could also use a Numbers table approach. If you don't already have an auxilliary numbers table you can create one as follows. This is populated with a million rows and so will be good for string lengths up to 2 million characters. CREATE TABLE dbo.Numbers (number int PRIMARY KEY); INSERT INTO dbo.Numbers (number) SELECT TOP 1000000 ...


3

In my other answer, I mentioned that functions in SQL Server can be quite slow. The typical way around that slowness is to create what is known as a "table-valued-function" or TVF. A TVF can be easily applied by SQL Server against a set of rows, thereby making them quite a bit faster than the non-TVF equivalent. The multi-statement TVF that would be ...


2

what do you expect count to count? you need to group by and use having rather than where SELECT VendorName FROM Vendors V join Invoices I on V.VendorId = I.VendorID group by VendorName having Count(*) >= @Number


2

You don't need the WHERE clause because you are doing an inner join, which ensures you will only get vendors that have an invoice: SELECT DISTINCT VendorName FROM Vendors V join Invoices I on V.VendorId=I.VendorID OR SELECT DISTINCT VendorName FROM Vendors V WHERE EXISTS (SELECT 1 FROM Invoices I WHERE V.VendorId=I.VendorID)


8

Without using REVERSE, which is what immediately comes to mind, but still using a function1; I would construct something like the following. This part simply removed the existing function, if it already exists: IF OBJECT_ID('dbo.IsPalindrome') IS NOT NULL DROP FUNCTION dbo.IsPalindrome; GO This is the function itself: CREATE FUNCTION dbo.IsPalindrome ( ...


47

If you are using SQL Server you can use the REVERSE() function to check? SELECT CASE WHEN @string = REVERSE(@String) THEN 1 ELSE 0 END AS Palindrome; Including Martin Smith's comment, if you are on SQL Server 2012+ you can use the IIF() function: SELECT IIF(@string = REVERSE(@String),1,0) AS Palindrome;


0

When you rename an SQL Server (i.e. change the Windows NetBIOS name), you also need to carry out this small manual step inside SQL Server to rename it internally. Details in this MSKB article.


4

I've set up option 3, Master Server, a number of times, but never for scheduling backups. I'm able to schedule jobs for performance data collection, pushing out administrative changes/script updates via SQLCMD, and run tests against multiple versions of SQL Server on multiple instances to test for any unexpected changes made after SP/CU application (among ...


-1

LoginName and Hostname from Sys.sysprocesses you've already got a cross apply to dm_exec_Sql_Text. so you've already got everything else you need.


9

There are several flaws to this approach: The term "preview" can be quite misleading in most cases, depending on the nature of the data being operated on (and that changes from operation to operation). What is to ensure that the current data being operated on will be in that same state between the time the "preview" data is gathered and when the user comes ...


3

The simplest approach is often the best and I don't really have that much of an issue with code duplication in SQL, especially not in the same module. After all the two queries are doing different things. So why not take 'Route 1' or Keep It Simple and just have two sections in the stored proc, one to simulate the work you need to do and one to do it, eg ...


0

select categoryMinMax.category , tableMin.ID as [tableMin.ID] , tableMax.ID as [tableMax.ID] from ( select category , min(order) as orderMin , max(order) as orderMax from table group by category ) categoryMinMax join table as tableMin on tableMin.category = categoryMinMax.category ...


2

Assuming this sample data: USE tempdb; GO CREATE TABLE dbo.o([Order] INT, ID INT, CategoryID INT); INSERT dbo.o([Order],ID,CategoryID) VALUES (1,100,13), (2,103,13), (3,102,13), (4,105,13), (5,104,13), (6,101,13), (1,201,57), (2,200,57), (3,204,57), (4,203,57), (5,206,57), (6,205,57); GO We can use a CTE to determine the first and last row, for each ...


3

You could do this without the new (2012+) window function FIRST_VALUE(), using the ROW_NUMBER(). But you could also do it using OUTER APPLY: SELECT d.CategorieID, IDMinOrder = a.ID, IDMaxOrder = b.ID FROM ( SELECT DISTINCT CategorieID FROM tableX ) AS d OUTER APPLY ( SELECT TOP (1) a.ID FROM tableX AS a WHERE ...


1

if is is just two then select name , min(Contact No) as [cont 1] , max(Contact No) as [cont 2] from table group by name


1

Here's an example which does what you want and will get you started with using PIVOT. Sorry it is a bit rushed, but hopefully it will get you started and show you how it can be done fairly easily. There will be limitations and I would fully advise researching and playing with the pivot functions because they are really powerful. -- Some sample data to work ...


2

My concerns are as follows. The transaction handling doesn't follow the standard pattern of being nested in a Begin Try / Begin Catch block. If this is a template then in a stored procedure with a few more steps you could exit from this transaction in preview mode with data still modified. Following the format increases developer work. If they change the ...


2

The time is not stored as number of milliseconds. It is a numeric representation of the actual time. For instance, 12:06:59 is represented as 120659. 1:00:02 pm is presented as 130102. 1:15:42 am would be 11542. Instead of calculating that all yourself, simply use the dbo.agent_datetime function. Something like: SELECT ...


2

will this script delete all tables with the prefix of Test_ on the database I execute it on? Yes, it will DROP tables. DELETE is normally used with deletion of data. Your script will physically drop the tables. Also, please dont use INFORMATION_SCHEMA and there is no need for a cursor. Your script can easily be written using dynamic sql. EDIT : In ...


1

I stumbled upon the same question a couple of years ago when investigating performance issues in Dynamics AX. Following Microsoft's explanation, I could see a different execution plan triggered by the hint and this corresponds to the other answers given here. With the hint, SQL Server tries to find an execution plan that allows a sort of streaming of ...


11

To reproduce the problem: SELECT *, (CASE WHEN IsGun=1 THEN CEILING(Price1Avg) ELSE Price1 END) FROM ( SELECT UPC, IsGun, Price1, AVG(CAST(Price1 AS numeric(8, 2))) OVER (PARTITION BY UPC) AS Price1Avg FROM ( VALUES ('A', 0, 14.99), ('B', 0, 29.99), ('C', 1, 319.00), ('D', 1, ...


11

Yes, things haven't changed much in this area, you should be using quotename for any SQL server object names that are used in dynamic SQL (especially if they are supplied externally to your code). As well as SQL injection mitigation this also means your code will work correctly for non standard identifier names. The function is only appropriate for object ...


2

Use CET : CREATE PROCEDURE [dbo].[sp_JobSearch] @EnteredKeyword nvarchar(200) = '', @EnteredLocation nvarchar(200) = '', @PageNumber INT = 1, @PageSize INT = 40 AS BEGIN WITH CTE AS ( SELECT ROW_NUMBER() OVER ( ORDER BY [MasterJobId] ) AS RowNum , MasterJob.Title, MasterJob.CompanyName, ...


6

The easiest way would be to add a computed column to the table with your mod calculation, and then create an index or add it to an existing index. For example: -- Test table and values CREATE TABLE #Test (col integer PRIMARY KEY); INSERT #Test (col) VALUES (1), (2), (3), (4), (5); -- Original query (scan) SELECT T.col FROM #Test AS T WHERE col % 5 = 4; ...


5

The grammar for exec accepts a @module_name_var which allows the module name to be in a variable. This does accepts three part names so the below does the trick. DECLARE @DbName SYSNAME = 'AdventureWorks2012'; DECLARE @module_name_var NVARCHAR(500) = QUOTENAME(@DbName) + '.sys.sp_addextendedproperty'; EXEC ...


3

My personal preference is to use COALESCE as it is ANSI standard. CREATE TABLE dbo.Contact ([ID] int, [FirstName] varchar(5), [LastName] varchar(6), [MiddleName] varchar(4)) ; INSERT INTO dbo.Contact ([ID], [FirstName], [LastName], [MiddleName]) VALUES (1, 'Mark', 'Jordan', 'E'), (2, 'James', 'Blunt', NULL) ; --- below both tsql will give ...


0

You can always use CASE to get that reverse logic, but it's easier than that in your case. --With names in separate columns SELECT FirstName, ISNULL((MiddleName + '.'), '') AS MiddleName, LastName FROM Contact --With names merged together, removing any leading/trailing spaces. SELECT LTRIM(RTRIM(ISNULL(FirstName, '') + ISNULL((MiddleName + '. '), ' ') + ...


5

It's great that a NULL plus a value produces a NULL*, means you can do stuff like this... SELECT FirstName + ' ' + ISNULL(MiddleName + '. ', '') + LastName FROM Contact; Hopefully, this works for what you want. * Assuming CONCAT_NULL_YIELDS_NULL is set ON, as it should be;


1

Scalar UDFs always affect performance. What you might have read is that you probably don't want to use them in your WHERE or JOIN ON clauses because that test is happening as part of the filtering stage and thus may force every single row to go through the UDF to determine the output. This is compared to the less worse option of just using it in the part of ...


0

Use this ones : Create Table [Type] ( Id Int ,Name varchar(10) ) Create Table [Product] ( Id Int , Name varchar(10), TypeID Int, ) INSERT INTO [Type](ID,Name) VALUES (1,'Bikes'), (2,'Clothes'), (3,'Electrical') INSERT INTO [Product](ID,Name,TypeID) VALUES (1,'Product 1',1), ...


2

In simple words, it could be put as: If the table has the A1 location, give me every distinct part that is the A1 location. Otherwise give me every distinct part regardless of location. Based on that, the query could be built like this: WITH cte AS ( SELECT *, TableHasA1 = COUNT(DISTINCT CASE Location WHEN 'A1' THEN 1 END) OVER (), ...


2

select distinct t1.part from table t1 where t1.Location = 'A1' or not exists (select 1 from table where Location = 'A1')


2

How about a little 2 in 1 using CTE? ;with cte as ( select top 1 loc = 1 from parts where Location = 'A1' ) select distinct Part --, Type, Location from parts left join cte on 1=1 where loc is null or Location = 'A1';


0

You can use the following SQL command to get the last commands that executed on MyDB (as example). SELECT deqs.last_execution_time AS [Time], dest.text AS [Query], dest.* FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest WHERE dest.dbid = DB_ID('MyDB') --note you need to change MyDB to your database name ORDER BY ...


1

I'm not a fan of triggers, but this seems best I could work out. Pros: No background jobs Can make fast queries on the DisplayId The trigger does not need to scan for the previous NNNNNN part Will restart the NNNNN part every year Will work if there is more than 100000 rows per year Does not require schema updates (e.g., sequence resets) to keep ...


9

In the result, the value column must have a data type, like always. SQL Server determines the type using the rules for data type precedence (more precisely, the VALUES clause is a UNION, so types are matched there). In your first example, the precedence rules give a column of type float. In the second example, it is datetime. Solve the problem by ...


2

Just create 2 separate row numbers for change of Type and SubType. I used Id field since it looks like you don't want it to be sorted by Name: Select case row_number() over (partition by T.Id order by T.Id, S.Id, P.Id) when 1 then T.Name else '' end as Type, case row_number() over (partition by T.Id, S.Id order by T.Id, S.Id, P.Id) when 1 then ...


2

SELECT CASE WHEN rn = 1 THEN TypeName ELSE NULL END AS TypeName , ProductName, rn FROM ( SELECT Type.Name AS [TypeName], Product.Name AS [ProductName] , ROW_NUMBER() OVER(PARTITION BY Type.Name ORDER BY Product.Name) AS rn FROM Product JOIN Type ON Type.Id = Product.TypeId ) tt order by ...


4

Windowing functions can help here. SELECT CASE WHEN RowNum = 1 THEN TypeName ELSE NULL END AS TypeName, ProductName FROM ( SELECT Type.Name AS TypeName,Product.Name AS ProductName, ROW_NUMBER() OVER(PARTITION BY Type.Name ORDER BY Product.Name) AS Rownum, COUNT([whatever you want to count]) AS ItemCount FROM Product INNER JOIN Type ON Type.Id = ...


11

A FAST N will tell SQL Server to generate an execution plan with quickly returning the number of rows defined as N. Note that the estimates will be as per the N since you are telling sql server to retrieve N rows as fast as possible. e.g. running below query with fast 500: -- total rows : 19972 SELECT [BusinessEntityID] ,[TotalPurchaseYTD] ...



Top 50 recent answers are included