New answers tagged

7

New Answer (based on new details provided in updates to the Question) One thing to consider is that a Primary Key and a Clustered Index are not the same thing. A Primary Key is a constraint and deals with the rules by which the data lives (i.e. data integrity); it has nothing to do with efficiency / performance. A Primary Key requires that the key column(s) ...


4

SQL Server will automatically add a 4 byte integer uniqueifier to all duplicate (ie second and subsequent for a key value) clustered index keys to make it unique. This is not exposed to the user though. The advantage of adding your own unique identifier as a secondary key column is that you can then still seek by companyid but also seek to individual rows ...


8

One way would be to pivot WITH CTE AS ( SELECT [Document No_], 'SL' AS Source FROM [Sales Line] UNION ALL SELECT [Document No_], 'SLA' AS Source FROM [Sales Line Archive] ) SELECT P.[Document No_], P.SL, P.SLA FROM CTE PIVOT (COUNT(Source) FOR Source IN ([SL], [SLA])) P WHERE P.[Document No_] IN (1,2,3); I'd hope that the predicate on ...


9

The UCS-2 encoding is always 2 bytes per character and has a range of 0 - 65535 (0x0000 - 0xFFFF). UTF-16 (regardless of Big Endian or Little Endian) has a range of 0 - 1114111 (0x0000 - 0x10FFFF). The 0 - 65535 / 0x0000 - 0xFFFF range of UTF-16 is 2 bytes per character while the range above 65536 / 0xFFFF is 4 bytes per character. Windows and SQL Server ...


3

Looks like a GROUP_CONCAT to me, unless I'm missing something, eg CREATE TABLE #heredity ( parent INT NOT NULL, child INT NOT NULL, depth INT NOT NULL, CONSTRAINT _pk_heredity PRIMARY KEY ( parent, child ) ); GO INSERT INTO #heredity ( parent, child, depth ) VALUES ( 1, 1, 0 ), ( 2, 2, 0 ), ( 3, 3, 0 ), ( 1, 2, 1 ), ( ...


2

It seems that this behavior / ability is both known and deprecated. I was looking through the sys.dm_os_performance_counters DMV the other day and noticed the following two entries: object_name counter_name instance_name ----------------------------- ------------ ------------------------------- SQLServer:Deprecated Features ...


2

No, dropping a synonym requires a Sch-M lock, and also IX locks on sysschobjs/sysobjvalues, at least if it is on the same server (I haven't tested what happens when the synonym uses a 4-part name). What exactly would it mean if you could change a synonym in the middle of a query or transaction? If you have a query plan operator or a cursor which needs to ...


3

Well, I did something similar to Steve, but with no need for a #temp table or recursive CTE. DECLARE @BigString nvarchar(max) = N' /* CREADO POR : Wxxwww wwwwww */ /* FECHA CREACIÓN : 10/12/2015 */ /* DESCRIPCIÓN : ...


1

I didn't spend a lot of time on performance with this one, but assuming you are ready in some kind of header info, this code should work for you. Parse the string out lines based on a line feed (dump to temp table) Find the largest line with the pattern you are looking for. Read the string from the previous end line position. Code: DECLARE ...


2

I've used Openrowset a number of times for this task. This code will create a table in SQL. SELECT * INTO EXCEL_IMPORT FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0; Database=C:\Excel\Spreadsheet.xls; HDR=YES; IMEX=1', 'SELECT * FROM [Sheet1$]'); Ideally you want to create the table first and then use INSERT INTO instead of the SELECT INTO. ...


0

You could try : select distinct parentId, substring(( select ','+cast(childId as varchar) from MyTable t1 with(nolock) where t1.parentId = t2.parentId for xml path ('') ),2, 1000) as childId from MyTable t2


4

You can use a pattern like this: SELECT files.database_id, db.name AS DatabaseName, STUFF((SELECT ', ' + names.name FROM sys.master_files names WHERE names.database_id = files.database_id FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)') , 1, 2, '') AS NameList FROM sys.master_files files ...


0

The very crude method is to pair up largest database with smallest, then 2nd largest with 2nd smallest and so on. This should work assuming that your database sizes are evenly distributed. Implementation has been hacked toghether in 5 min, so can probably be improved a lot: ;WITH DBSizes AS ( SELECT db.name, DbSize.TotalDataSize * 8 / 1024 as ...


2

This solution would replace the need for having 100 + procs doing the same thing. you have a proc and a function. The function splits all your medical codes from a string to a table which can be use in a CROSS APPLY in the new proc. This way you only have to call the one proc. Of course you'd have to update all the code calling the other procs to use ...


10

This should work for you: CREATE VIEW MyView AS SELECT <colList> FROM <TableOne> INNER JOIN <TableTwo> ON ... AND ..... AND ..... LEFT JOIN <TableThree> ON ... AND ..... AND ..... WHERE ..... AND ..... AND ..... Then replace in Procs with: ... FROM MyView WHERE MedicalPlanCode IN ('abc', 'def', 'ghi')


10

The biggest difficulty in coming to a precise solution is in defining exactly what characters are to be included (or excluded, whichever direction makes more sense for the operation). Meaning: Are we talking about VARCHAR / ASCII data or NVARCHAR / Unicode data? The list of punctuation characters for ASCII data depends on the Code Page which in turn ...


5

I may be over-simplifying this a bit but, if we say that punctuation is all that is left when alphanumeric values are removed, then the following will search for strings that have non-alphanumeric characters in them. Create Table #Test ( Value VarChar(10) ) Insert Into #Test Values ('123a'), ('456b'), ('12ABC'),('AB!23'),('C?D789') -- Original Select ...


0

May be you can try this: CREATE TABLE #tempFileTable (FName VARCHAR(8000),Depth INTEGER, Files INTEGER) INSERT INTO #tempFileTable EXEC xp_dirtree 'D:\Test1', 1, 1 SELECT TOP 1 Fname FROM #tempFileTable; DECLARE @DBName varchar(255) = 'Test' select 'RESTORE DATABASE ' + @DBName + ' FROM DISK = D:\Test1\' + fname + ' WITH FILE = 1' from ...


0

Maybe you could try something like : declare @file varchar(50) declare @DBName varchar(50) declare @sql varchar(200) CREATE TABLE #tempFileTable (FName VARCHAR(8000),Depth INTEGER, Files INTEGER) INSERT INTO #tempFileTable EXEC xp_dirtree 'D:\', 1, 1 select @file = FName from #tempFileTable set @DBName = 'MyDB' set @sql = 'RESTORE DATABASE '+@DBName+' ...


1

I had to run this on a SQL 2005 Instance since my newer instances do not allow xp_cmdshell execution, which is why the parameters do not have their initial values set with the declare statements. I added a second parameter and then executed that. Remove the comments from the --EXEC (@MappingCommand) statement to execute it. DECLARE @UNCSourcePath ...


2

It looks like it only accepts one argument. So try doing this... DECLARE @UNCSourcePath nvarchar(255) = 'NET USE S: "\\OTHER_SERVER\C$\Program Files"' EXEC xp_cmdshell @UNCSourcePath


9

This recursive CTE (SQL Fiddle) should work with your sample: WITH cte(ParentID) AS( SELECT ParentID FROM @Instances WHERE [Part] = 'Rivet' UNION ALL SELECT i.ParentID FROM cte c INNER JOIN @Instances i ON c.ParentID = i.InstanceID WHERE i.ParentID > 0 ) SELECT ParentID, count(*) FROM cte GROUP BY ParentID ORDER BY ParentID ; ...


0

I'm not sure I understand what you mean by "amount" and where table (?) PartInstances and columns id and count come from in your sample but I calculated what I guess from your sample data. ;with ins as ( select [InstanceID], [Part],[ParentID],0 lvl from instances where ParentID=0 union all select i.[InstanceID], i.[Part],i.[ParentID], lvl+1 from instances i ...


1

YES, it is incorrect. The inner FETCH NEXT should go at the END of the loop. The code above would result in the following: Hello, I am # 2 Hello, I am # 3 Hello, I am # 4 Hello, I am # 5 Hello, I am # 5 Correcting the problem, and putting the FETCH NEXT at the end of the loop, results in the correct & expected output: Hello, I am # 1 Hello, I am # ...


2

There are quite a few ways to achieve your desired results. Undeterministic methods (in the event that many rows in table 2 match one in table 1) UPDATE T1 SET address = T2.address, phone2 = T2.phone FROM #Table1 T1 JOIN #Table2 T2 ON T1.gender = T2.gender AND T1.birthdate = T2.birthdate Or a slightly more concise ...


0

Making the correct join is key. UPDATE table1 INNER JOIN table2 ON table1.birthdate = table2.birthdate AND table1.gender = table2.gender SET table1.address = table2.address, table1.phone = table2.phone ; As ypercubeᵀᴹ has mentioned, if you have duplicates in the second table then an arbitrary record will be picked for the update..


2

Removing permissions is not generally going to work because you can't be CERTAIN that someone doesn't have permissions. Possibly through a group, role or even because they are sysadmin (although let's hope not). For tables you can disable them. And that is a quick process. However to enable them requires you to rebuild them and for a large table that ...


6

Is there any other way to achieve the same(tables cannot be used anymore) without dropping them? A schema change is a very fast operation - just metadata change is required. The original idea I got was from Aaron Bertrand's blog - Schema Switch-A-Roo. You can follow the steps from my answer here Obviously there are other methods like sp_rename N'old ...


2

Remove the permissions as Phil W. suggests. Also remove the permissions from any stored procedures that use the tables. In SQL Server, (I don't know about others) permissions are chained from a calling object (e.g. the sproc) to the called object (e.g. a table).


5

Remove the permissions on the table from the Role(s)/ Group(s)/ Account(s) that [might] be using it. If anything blows up, put them back [quickly]. Hint: Using a script to do make these changes would be a really, really Good Idea.


11

A couple of other options are to just rename the tables, or if they have clustered indexes, you can disable the clustered index.


1

You can create a new filegroup, make it DEFUALT, create all schema objects and switch DEFAULT filegroup back. If at any later point you would need to add objects to that schema, you can explicitly specify filegroup through ON filegroup clause for each object in create statement or temporarily switch DEFUALT filegroup. Alternatively you can explicitly ...


5

Ranges in the pattern syntax use the sorting rules of your collation. Use a binary collate clause so the range is ordered by character code. (I also changed it to LIKE as I find that more obvious than PATINDEX > 0) SELECT * FROM mbrnotes WHERE LINE_TEXT LIKE '%[' + CHAR(127)+ '-' +CHAR(255)+']%' COLLATE Latin1_General_100_BIN2


2

Your 2nd query seems correct and does what you want. Another option would be to use the TOP .. WITH TIES but this works only because NULL values are treated as the lowest in SQL Server. SELECT TOP (1) WITH TIES t.* FROM cte AS t ORDER BY t.NodeId DESC ; If you had MIN() instead of MAX() and you tried the TIES with ASC instead of DESC, the above would ...


-1

Assuming your NodeId contains INT values, use ISNULL to replace all nulls with the least of int values. This way you'll include NULL values as your least probability to occur. Following is an example: DECLARE @MinNodeId INT; SET @MinNodeId = MIN(NodeId) - 1 FROM CTE; SELECT * FROM CTE WHERE ISNULL(NodeId, @MinNodeId) = (SELECT MAX(ISNULL(NodeId, ...


1

Have you considered using a trace for that? https://technet.microsoft.com/en-us/library/ms191006%28v=sql.105%29.aspx You would be able to configure it for specific tables and store output in a table. I also have to point out that, with the method you are trying to use, you will only be able to capture the query used to change the table. In some situations ...


1

Import-Module SQLPS $ErrorActionPreference = "Stop" Set-StrictMode -Version Latest $serverName = "VM-WIN81" $instanceName = "SQL2014" # or DEFAULT $jobName = "syspolicy_purge_history" $jobSuffix = "_Old" $script = Get-ChildItem SQLSERVER:\SQL\$serverName\$instanceName\JobServer\Jobs | Where { $_.Name -eq $jobName } | %{ $_.Script() } $script = ...


1

It would be a large script as a Job consists of data entries in multiple tables/views: msdb.dbo.sysjobs_view msdb.dbo.sysjobsteps msdb.dbo.sysjobschedules -- you probably dont need these as you are copying to the same server msdb.dbo.sysschedules msdb.dbo.syscategories sys.server_principals msdb.dbo.sysOperators Create Jobs using following SPs: ...


3

The output from the CASE can only be of one data type. The first branch is decimal and the second is nvarchar(255). According to the Data Type Precedence (Transact-SQL) rules in SQL Server the resulting data type for the CASE is decimal. Add one more cast to nvarchar(255) in the first branch of the CASE. SELECT CASE WHEN [Parcel Number 1] like '%E+%' ...


4

Just for fun, here's a SQL Server 2016 Scalar User-Defined function with the In-Memory OLTP feature: ALTER FUNCTION dbo.IsPalindrome2 ( @inputString NVARCHAR(500) ) RETURNS BIT WITH NATIVE_COMPILATION, SCHEMABINDING AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English') DECLARE @i INT = 1, @j INT = LEN(@inputString) ...


0

The UPDLOCK will prevent a subsequent call from reading an order that has already been picked up, but won't allow that subsequent call from reading others because it will wait to read all the rows that fit the criteria and you have explicitly told the engine that you intend to update them in this transaction. If you add a READPAST hint as well, it will ...


6

This is an inline TVF-friendly version of Martin Smith's set-based solution, additionally decorated with a couple of superfluous enhancements: WITH Nums AS ( SELECT N = number FROM dbo.Numbers WITH(FORCESEEK) /*Requires a suitably indexed numbers table*/ ) SELECT IsPalindrome = CASE WHEN EXISTS ( SELECT * FROM ...


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


3

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


17

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



Top 50 recent answers are included