Hot answers tagged

15

There are are 2 elements to your field Year An auto incrementing number They do not need to be stored as one field Example: A year column which has a default of YEAR(GETDATE()) A number column based on a sequence. Then create a computed column concatenating them (with appropriate formatting). The sequence can be reset on change of year. Sample code ...


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] ...


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 ...


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, ...


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 ...


8

Did you consider to create an identity field with seed = 2016000000? create table Table1 ( id bigint identity(2016000000,1), field1 varchar(20)... ) This seed should be autoincremented each year, for example at the night of 2017/1/1 you need to schedule DBCC CHECKIDENT (Table1, RESEED, 2017000000) But I already see problems with the design, for ...


8

update ch1380.ComplexMiscLogic set logic = 'UNIDDisposition IN (''1'',''2'',''5'')' where misclogic = 11 You need to escape your single quotation marks.


8

Using a single statement and ROW_NUMBER(). With proper indexes, it will probably be more efficient than cursors. Note that I moved the p.[BucketRef] = @stgBucketNo from the WHERE to the ON clause. The way you had it, made the LEFT JOIN work as an INNER JOIN. WITH starting (Id) AS ( SELECT ISNULL( ( SELECT TOP (1) v.Id FROM ...


6

You can identify database snapshots in sys.databases by the column source_database_id being not null. Join it to sys.master_files and you're done: SELECT mf.database_id, dbs.source_database_id, mf.type_desc, mf.name, mf.physical_name FROM sys.master_files AS mf INNER JOIN sys.databases AS dbs ON mf.database_id = dbs.database_id WHERE ...


6

According to this MSDN article for using FOR XML in SQL Server 2000, "ELEMENTS Specifies that the columns are returned as subelements. Otherwise, they are mapped to XML attributes. This option is supported in AUTO mode only." So, your usage of ELEMENTS with the RAW option isn't supported in SQL Server 2000, but it does work with AUTO. Edit: If you were to ...


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

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;


5

When you use this query hint OPTION(FAST n) the optimizer picks an execution plan that can return that amount of records in the fastest way possible. Normally, the optimizer tries to decide on a path that can efficiently return the complete resultset. So, if you want a set of rows to come back quickly, you could possibly use this to retrieve them, but in ...


4

You can use an identity column in a temporary table to avoid a cursor. This is especially convenient with older SQL Server versions that don't support WITH or window functions. CREATE TABLE #Variable ([VariableNo] int IDENTITY NOT NULL, [Code] varchar(4000) NOT NULL) INSERT INTO #Variable ([Code]) SELECT DISTINCT p.[Variable] FROM ...


4

You could do this with sp_executesql and assigning the variable to an output parameter DECLARE @numreturned INT; DECLARE @sql NVARCHAR(MAX); DECLARE @fulllocation NVARCHAR(MAX) = 'nagadishi.mirama.dbo.Humizan'; SET @sql = N'SELECT @numreturned = COUNT(*) FROM '+@fulllocation+' ' +N'WHERE ([Nishan] IS NULL)'; EXEC sys.sp_executesql ...


4

You like need to wrap that code in CREATE PROCEDURE ... syntax, and remove the GO statements after BEGIN TRANSACTION and before COMMIT TRANSACTION. GO CREATE PROCEDURE dbo.AssignUserToTicket ( @updateAuthor varchar(100) , @assignedUser varchar(100) , @ticketID bigint ) AS BEGIN BEGIN TRANSACTION; SAVE TRANSACTION MySavePoint; SET ...


4

I would suggest to use Ola's backup solution (and Index maintenance solution as well). This is a well applauded solution that is smart enough to work with mirroring (in your case). Maintenance plans are not bad, but when your environment grows, the limited flexibility and functionality that maintenance plans provide wont be sufficient. For e.g Maintenance ...


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 = ...


4

This is untested. Let me know if it works or not. The grammar for exec accepts a @module_name_var which allows the module name to be in a variable. I'm not sure if it accepts three part names or not. If it does then hopefully the below will do the trick. DECLARE @DbName sysname = 'AdventureWorks2012'; DECLARE @module_name_var nvarchar(500) = ...


3

I'm not really sure what you are asking but you can tell if a database is online by looking in sys.databases at the state_desc column. Possible values are: ONLINE RESTORING RECOVERING (SQL Server 2008 through SQL Server 2016) RECOVERY_PENDING (SQL Server 2008 through SQL Server 2016) SUSPECT EMERGENCY (SQL Server 2008 through SQL Server 2016) OFFLINE ...


3

If you want to properly handle nested Stored Procedures that can handle Transactions (whether started from T-SQL or app code) then you should follow the template that I described in the following answer: Are we required to handle Transaction in C# Code as well as in Store procedure You will notice two differences there from what you are attempting here: ...


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 ...


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, ...


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 ...


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

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';


2

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


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 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 ...


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 ...



Only top voted, non community-wiki answers of a minimum length are eligible