Tag Info

Hot answers tagged

8

Kendra here (the author of sp_BlitzIndex) First, thanks for being interested in the procedure and trying it out. Azure doesn't expose all the dynamic management views that we get in the boxed product. I do actually reference sys.dm_db_partition_stats, but there's other info I need to get from sys.partitions for other users. (Is it using compression? What ...


6

It seems you don't really need the "frequency" for the sorting, only the "maximum frequency" per customer. You can use the COUNT(*) OVER (PARTITION BY customerID): SELECT customerID, eventID, ROW_NUMBER() OVER (PARTITION BY customerID ORDER BY eventID) AS frequency FROM customerEvent ORDER BY COUNT(*) OVER (PARTITION BY customerID) ...


6

The following assumptions have been made (some of them possibly repeating parts of your description): SF/FLEX is always followed either by a number immediately or by a space character and then a number. There is always one space character before SF/FLEX, unless the item is at the beginning of the string. There is always a comma or a space after the number ...


5

Now that I better understand that you are trying to extract data, here is a new answer. This just simply extracts data from a table that I created in tempdb. use tempdb -- drop table mytable create table mytable (id int, customer_name varchar(55), cityname varchar(55), statename varchar(55)) insert into mytable values (1, 'a', 'a city', 'a state') ...


4

The problem mentioned on MSDN has nothing to do with sys.sql_modules or OBJECT_DEFINITION(); they've misinterpreted the problem. What actually happened is they were thwarted by an output limitation in Management Studio, which by default will only show 255 characters and at most 8192 in any output tuple in Results to Text. So first, make sure you change this ...


3

A query use the index in the column order (UserId first then RoleId). Without an index on RoleId, it will scan the clustered index. Unless there is a where clause with a userId, the engine does not know how to get inside the index whitout scanning all the userId. Because roleId is a FK, best practice suggest to have an index on it. You definetly need it if ...


3

In simple cases, it will be the same. However, I have seen very complex queries with several joins have significantly different plans. A recent one I was working on started with a table that has close to 6 million rows joined to about 20 different tables. Only the first join to this table was an inner join, all others were left outer joins. The filter in the ...


3

Once again, thank you to Vladimir for providing the formula! The correct way to accomplish what I need is this: DECLARE @step_size INT = 7, @max_iteration_range INT = 40, @max_iterations INT = 2 UPDATE #mytable SET iteration = ((id*@step_size)/@max_iteration_range) % (@max_iterations+1), step = ...


3

You could try Pivot: I think you'll need to order on some key that ties these records together (how do you know that the starttime of "TESR" is related to the EndTime of "TESR". That probably needs to be in the ORDER BY clause below. SELECT Title ,[1] ,[2] ,[3] FROM ( SELECT Title, Value, ...


2

Define the outer join as a separate, non-recursive, CTE: WITH FactorColumns AS ( SELECT a.FactorTitle, a.FactorCode, a.parentId, c.FactorColumnCode, c.FactorColumnTitle, c.FactorColumnValue, c.isFactorValue, c.FieldType FROM FactorSetup a LEFT JOIN FactorColumnSetup c ON ...


2

Another option, at least just to have it stated, is to use BCP Format Files. Format Files allow for specify column properties, including the delimiter. Delimiters are typically just whatever character, but you can add double-quotes into the appropriate field delimiters to produce text-qualified values. You can do a simple test by having BCP generate a ...


2

I realize this has been answered before on Stack Overflow. It got me going in the right direction. I now realize I should add the actual time stamp of the leave to my Start and End Dates and do a simple compare. I wish I had figured this out months ago; it would have saved me much pain and suffering. For anyone in the future who has a similar issue: My ...


2

There should be no real performance problems related to using the system objects to build your T-SQL. Consider also that building your dynamic T-SQL is probably a much lighter load on your server than actually running the T-SQL statements that you generate. There are also INFORMATION_SCHEMA views, but these only partially cover the system objects. I ...


2

I think SSIS is really what you want to use here. To just do a very basic import of Excel into SQL Server this is a great link to use - Simple Talk 10 Simple Steps From there, you will then need to use a combination of Conditional Splits as well as MultiCast in order to move the data to different fields/locations depending on your various rules. ...


2

I would use the Object Explorer in SQL Server Management Studio and go from the top, one procedure at a time. Right click on the procedure. Select Modify. Edit the procedure code. List item Press F5 to execute the modification of the procedure. Close the tab. Go to 1. Makes it easy to divide the work between you and your friend that does the same going ...


2

Is it possible to redesign your schema? It feels like you are making life harder for yourself by basically trying to pivot the data you're importing from the excel spreadsheets. CREATE TABLE dbo.Hardware -- hw? ( [Event_Num] INT NOT NULL PRIMARY KEY, [Name] NVARCHAR(100) NOT NULL, [Install_Date] DATE NULL, -- Install after pulling? ...


2

One method is with a common table expression like the example below. Note that the preceding statement must be terminated with a semicolon. WITH cte AS ( SELECT a.database_name , a.collection_time , ROW_NUMBER() OVER ( PARTITION BY CONVERT (DATE, SWITCHOFFSET(CAST (a.collection_time AS DATETIMEOFFSET), ...


2

You should create a derived table or a CTE , to be able to access the ROW_NUMBER() SELECT a.database_name , a.collection_time , a.row_ID FROM ( SELECT a.database_name , a.collection_time , ROW_NUMBER() OVER ( PARTITION BY CONVERT (DATE, SWITCHOFFSET(CAST (a.collection_time AS DATETIMEOFFSET), ...


2

I was wondering whether you want to group by well_name for all different pull date or not. Right now, a well with X dates gets X rows. If you want the X date/rows to become 1, I will update the queries. Query 1 uses left join Query 2 uses pivot SQL Fiddle (Left Join): ;with data as( Select Pull_Date, Well_Name, Part, PartPN, Part_SN , id = ...


2

This should work. I took the original CTE, partitioned by part and date and numbered those. This gave every part on each date it's own number starting with 1. Then in the case statement, adding WHERE Number = N allows it so that each part is unique. Finally, the column names were modified to have their respective numbers. Since this is unwieldy, ...


1

The list of events that can be used for DDL Triggers can be found on the following MSDN page: DDL Event Groups. If you look through that list, you will notice that they do not offer a level of granularity below the base CREATE / ALTER / DROP {ObjectType} ... So trapping ALTER_TABLE will get all ALTER TABLE... statements. Once a DDL Trigger is fired, you ...


1

I just read this article adressing this topic and I suppose you might be interested, because it uses the lag function to solve exactly your Task: https://www.mssqltips.com/sqlservertip/3690/identify-sql-server-database-growth-rates/ The Author created the following Script using the LAG Window function: SELECT DISTINCT A.[database_name] , AVG( ...


1

Put apostrophes around the string, and use the proper column name: Where part.num = 'A00089'


1

I agree with NReiligh but if you still want to extract values use combination of CHARINDEX and SUBSTRING functions of SQL SERVER SELECT [PEvent] ,[PDate] , [PDescription] ,CASE ------------------------------------------For SF Values WHEN [PDescription] LIKE '%SF%' THEN CASE substring([PDescription], ...


1

I think this is a situation where you just need a cross apply. If you are not familiar with it, look at this link. With your data I created a table and inserted the data with this scrip CREATE TABLE school ( schoolcode VARCHAR(20) ,SchoolYear INT ,Semester INT ,Term INT ,TermEnd DATETIME2 ); INSERT INTO dbo.school ( ...


1

I got a response through their help email. The full text is below: Hi! sp_blitz doesn't work in Azure. The value of Azure is letting someone else be your DBA. ;-) I guess the answer to my question is be bold or find another tool... NOTE: They mentioned "sp_blitz" instead of "sp_blitzindex" in their email response. That might be a simple typo, but I ...


1

I would write a procedure to generate the table for me: CREATE PROCEDURE [dbo].[p_create_table_from_procedure] @TABLE_NAME AS NVARCHAR(MAX), @PROCEDURE_NAME AS NVARCHAR(MAX) As DECLARE @CREATE_TABLE_QUERY NVARCHAR(MAX) = N''; SELECT @CREATE_TABLE_QUERY += ', ' + name + ' ' + UPPER(system_type_name) + CHAR(13) + CHAR(10) + CHAR(9) ...


1

In general, where you put the filters makes a difference. While Tom V says the Optimizer will recognize that the queries are the same and come up with the same plan, that is not always true. It depends on what version of SQL you are on, how complex your query is, and how important to the overall batch the Optimizer determines the query is. The Optimizer ...


1

For SQL Server, using a similar example with a clustered index on the Id, the plans both use a "Clustered Index Seek" which is a broad explanation for what may happen internally. select ObjectId from oav.Object where ObjectId in (3105680,3105681,3105682,3105683,3105684,3105685,3105686,3105687,3105688,3105689) select ObjectId from oav.Object where ...



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