Tag Info

New answers tagged

1

Your idea is right but you need HAVING COUNT(DISTINCT design) > 1 ;with multi_design_perfs as ( select b.perf_id from perf_ticket_type b group by b.perf_id having COUNT(distinct b.design) > 1 ) select m.perf_id ,STUFF(( select ', ' + CAST(b.design as varchar(10)) from perf_ticket_type b where ...


0

I use your code and added some checks, regarding the COST_CHANGE = 0 For Sql Server 2012, you can use the LAG (this is not available in 2008,2008R2) --my source table DECLARE @tPROD_COST TABLE (ID INT /*IDENTITY(1,1)*/, Duration INT, Cost FLOAT, COST_NEXT FLOAT, COST_CHANGE FLOAT) INSERT INTO @tPROD_COST(ID,Duration,Cost,COST_NEXT,COST_CHANGE) ...


2

Supplementing to @MaxVernon's answer, there are some optimizations that you can do : Use different filegroup for staging tables and make sure the filegroup is on a different physical drive (or LUN). Use Instant File Initialization as well as have a proper autogrowth setting. I have a handy script to analyze autogrowth events. Since you will be emptying the ...


2

Putting those staging tables on their own filegroup allows you to do piece-meal restores. This helps get the database up-and-running more quickly in the event of a disaster recovery effort. Having those tables in their own filegroup will allow you to place them on their own set of disks which may increase performance.


4

This site, is a question and answer site, not a mentoring site. You have a Database Design class, which is much closer to the mentoring that you are seeking. Also, you must have instruction materials that will explain at least the basics. Questions are welcome, but you will get better results by: Working on the problem yourself first. Describe ...


1

Applications do their testing against the new principal which is ServerB but don't want to take those changes or sync to ServerA (Mirror). Is there any way to do this without breaking the mirror? No this is not possible to prevent changes to ServerA. You have to resetup mirroring from original primary ServerA to ServerB What you can do is : ...


1

For these kind of reports I prefer to use a table of numbers or calendar table. Table of numbers is simply a table that has one column with integer numbers from 1 to some large enough number, say, 100,000. I personally use table with 100K numbers. Aaron Bertrand wrote a good article explaining how to generate such table. Calendar table is simply a table that ...


1

Summarizing other approaches originally offered in comments to the question. Use NOWAIT if the desired behaviour is to fail the whole chunk as soon as an incompatible lock is encountered. From the NOWAIT documentation: Instructs the Database Engine to return a message as soon as a lock is encountered on the table. NOWAIT is equivalent to specifying ...


6

Is the TCP port SQL Server is listening on open globally? If so, yes I'd be concerned. If there is a password that can be brute-forced or guessed, or an exploit that allows someone to bypass authentication, eventually your database could be compromised. You'll also be vulnerable to attacks that don't require access, such as someone filling up the drive that ...


2

Move the condition from the WHERE clause to a conditional count: SELECT COUNT(CASE WHEN CD_TRACKING = 14 THEN CD_BarCode ELSE NULL END), CD_Carrier, SUBSTRING(CONVERT(VARCHAR,DT_Arriving,103),1,11) AS [Date] FROM TB_AGIL WHERE DT_Arriving >= @date AND DT_Arriving < DATEADD(MONTH,+1,@date) GROUP BY CD_Carrier, ...


2

There are any number of ways to express this in T-SQL. One that works (without using APPLY - which you should learn to love, by the way) is below, with comments inline: WITH SampleData (PERSON,TRANSACTDATE, STARTDATE, END_DATE, IN_PUNCH,OUT_PUNCH,HOURS, PAYCODE) AS ( SELECT 1234,'08/03/2015','08/03/2015','08/03/2015', '06:00','09:00','3', 'REG1' ...


1

Refer to the link Aaron Bertrand provided. There are a limited set of operations that are minimally logged, but reorg/rebuild aka defragmenting are not included. If you wish to minimize the effect, you'd have to do incremental reorgs as mentioned by Cibi or just allocate the needed space if this is expected to occur regularly. I've had to do this before in ...


1

If the dates aren't in your resultset then you need to get them from somewhere right? You could create a join with a derived table full of dates.


1

Below will require a bit of more planning and testing. Blue-Green Concept : The gist of Blue-Green Concept is to divide your production into 2 environments and they are identical all times (data synchronization) wherein The Blue (Current) will have the current version of the schema/build or product and will be your "LIVE" environment. At the same ...


1

Since you mentioned about Availability Groups, this means that you are using SQL Server Enterprise edition (and possibly SQL Server 2012 or 2014). You can use database snapshots, but in our case there were lot of dependencies on different components and so using database snapshot was ruled out. Below will require a bit of more planning and testing. ...


0

Assuming SQL Server 2008 or better (please always include version information with your questions). I'll let you deal with holidays, but here's a much more efficient, set-based way to generate your 30 years of date dimensions. A lot of your CASE expressions, aggregates, updates and looping are simply not necessary, because they were constructed with a ...


4

Housekeeping: I am using SQL Server Microsoft SQL Server 2012 - 11.0.5343.0 (X64) Solution: You can use WITH RECOMPILE in your stored procedure. Start by creating a test database, table, some indexes, and finally test data. USE [master]; GO IF DATABASEPROPERTYEX (N'test', N'Version') > 0 BEGIN ALTER DATABASE [test] SET SINGLE_USER WITH ...


0

You can use a range of integers to generate a range of dates using the DateDiff and DateAdd functions. Let's pretend you have a TVF that generates a range of integers already: select [Date] = DateAdd(day, n, @date1) from dbo.RangeSmallInt(0, Abs(DateDiff(day, @date1, @date2))) Wrapping it up in a TVF: -- Generate a range of up to 65,536 contiguous DATES ...


5

You can add the query hint OPTION (RECOMPILE). This effectively tells SQL Server, "Give me a plan to execute this query one time, but don't save it in the plan cache". Take a peek at Kendra Little's article on RECOMPILE hints and execution plan caching. She covers all the uses and potential effects. RECOMPILE Hints and Execution Plan Caching Also see: ...


-1

For SQL 2008, we can use OUTER APPLY. Question about the order in which the LEAVE1 and LEAVE2 appear, if they have a particulare order ? Any how , here a sample of it. WITH SampleData (PERSON,TRANSACTDATE, STARTDATE, END_DATE, IN_PUNCH,OUT_PUNCH,HOURS, PAYCODE) AS ( SELECT 1234,'08/03/2015','08/03/2015','08/03/2015', '06:00','09:00','3', 'REG1' UNION ...


2

I suggest you go down a different path with this. It looks like a pattern of select top() .... order by should be sufficient. select top(@NumberOfItems) P.PhotoId, P.[Key], P.Created from Content.Photo as P inner join Content.UserPhotoAssociation as UPA on P.PhotoId = UPA.PhotoId where -- Older than CutoffTime P.Created < @CutoffTime and ...


2

It might be related with leaking connections exhausting the connection pool. If the connections are not released properly they will remain active and will not be returned to the connection pool until the next Garbage Collection executes. If a lot of connections are opened in a short time the connection pool might fill up before the GC runs, and the following ...


1

Based on the BOL article you referenced to, the date is not going to change unless a Service Pack or CU creates new system objects. However the date returned may not necessarily coincide with the date you install that CU or SP. The Resource database makes upgrading to a new version of SQL Server an easier and faster procedure. In earlier versions of ...


-1

DECLARE @SQL varchar(8000) SET @SQL = '' SELECT @SQL = @SQL + ' CAST(' + COLUMN_NAME + ' as ' + ISNULL(DATA_TYPE + '(' + CAST(CHARACTER_OCTET_LENGTH as varchar(20)) + ') )','') + ' as ' + COLUMN_NAME + ',' FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'YourPermTable' SET @SQL = SUBSTRING(@SQL,1,LEN(@SQL)-1) SET @SQL = 'SELECT ' + @SQL + ' FROM ...


2

It rather depends on what the scripts are intended to do... If you can cope with the occasional dirty read or unrepeatable read issue (or they are truly simple enough that these are unlikely/impossible), also set your transaction handling to the most lenient mode possible with SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED. This is actually equivalent to ...


0

The only way you'll be able to do this consistently and correctly is by looking at what each job does, and cross-referencing that with the msdn articles for each command on your given version. For example, DBCC SHOW_STATISTICS has a section: Permissions In order to view the statistics object, the user must own the table or the user must be a ...


4

It is very much normal for there to be gaps in identity values (caused by rolled-back transactions, over estimated pre-allocations, and so forth) as you are already aware, though that sort of sparse use of values is rather unusual and indicates that there is something going on which you need to be aware of (either a design issue that may be unavoidable and ...


1

Same solution as the one provided by Aaron Bertrand when it comes to building the comma separated values but a bit different in connecting CourseMaster.CourseId with the values in StudentMaster.Course. SQL Fiddle MS SQL Server 2014 Schema Setup: create table dbo.CourseMaster ( CourseId char(2), CourseName char(3) ); create table dbo.StudentMaster ( ...


0

If you will always have SchoolId then you could benefit from making the clustered index a composite key of SchoolId, Id as you won't have to have an additional index on SchoolId to avoid a table scan. Not having the additional index will let Inserts/Updates/Deletes complete faster as the transaction only has to update one index. You may find as you create ...


1

Post the DDL for players (same for users). This is the CREATE TABLE My_Table (fieldname... fieldlist); statement for both tables. You've no table player1 (or player2) - and then you're trying to use aliases for field names as table names? @RobFarley has given you a clue here (maybe). I really don't know where to start, except to advise you to read some ...


1

Probably: SELECT p.gameId, p.player1id, p.player2id, u1.username as player1, u2.username as player2 FROM dbo.players AS p INNER JOIN dbo.users AS u1 ON p.player1id = u1.id INNER JOIN dbo.users AS u2 ON p.player2id = u2.id WHERE p.gameId = 2; This is 2015. You should not be using old-style join syntax from the 70s; join us in this ...


0

The quick answer is: FROM players as player1, players as player2, users The better answer is for you to learn how to use the ON clause.


0

This should work as it did for me: Set the -failoverpartner using the mirror name in the sql agents for replication. Fail mirror over to secondary Make sure replication continues to send commands to subscriber. Remove mirroring so primary is no longer connected. Go to distributor, add an alias (sql server alias) The alias should be the old principal name ...


2

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


3

There are a couple of concepts which need to be distinguished. One is about structure and the other about schema. Structured data is one where the application knows in advance the meaning of each byte it receives. A good example is measurements from a sensor. In contrast a Twitter stream is unstructured. Schema is about how much of the structure is ...


5

Despite the mainstream of noSQL databases IMHO the decision about adopting such technology should be made according to the achievements needed according to the information stored, not only attending to the performance you currently have. This means that maybe your best option is to stick to the SQL database and improve your HW. But additionally I read ...


0

I Installed SQL Server 2014 Service Pack 1 (SP1) and that resolved the issue.


0

To remove database mirroring Connect to the Database Engine of either mirroring partner. From the Standard bar, click New Query. Issue the following Transact-SQL statement: ALTER DATABASE database_name SET PARTNER OFF where database_name is the mirrored database whose session you want to remove. The following example removes database mirroring from the ...


5

Assuming you want randomish ordering of your output you can just ORDER BY NEWID(). That would jumble up the ordering of the results reasonably well. If you only need to randomize after a certain order is established then you can still use the NEWID() trick. Below are some quick examples: -- Fully random ordering SELECT * FROM dbo.Foo F INNER JOIN ...


5

You really should have a junction table for the courses a student is taking, rather than jamming comma-separated values into a single tuple. If you think this is the last problem you'll have because of this sub-optimal design, you're in for a big surprise. You really should have the owners of this project go read up on normalization - yes it's painful to ...


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

If you're sure the user in question has View Server State (and it looks like in your screenshot he does). Then there are a number of reasons previously put into an msdn blog. Ranging from: Performance Objects and counters set-up during the SQL Server installation failed. A mixture of 64 and 32 bit platforms. Registry permissions have been skewed To ...


5

Have you looked under Advanced Security Settings? The arrows point to the modifications I've often had to specifically apply when installing SQL Server on Windows 2012 R2. For the SQL Server Root Directory, I'll give full control to the SQL Server Process account. Related official documentation: Configure File System Permissions for Database Engine ...


0

If you stop it (disable it), the index won't be available for queries using it. However you can set bchange tracking to manual: ALTER FULLTEXT INDEX ON schema.table SET CHANGE_TRACKING MANUAL GO Update... ALTER FULLTEXT INDEX schema.table START UPDATE POPULATION GO ALTER FULLTEXT INDEX ON schema.table SET CHANGE_TRACKING AUTO GO You can check the ...


3

RESTORE DATABASE Tfs_CoE FROM DISK = 'D:\Backupfolder\Tfs_CoE.bak' WITH MOVE 'Tfs_CoE' TO 'D:\MSSQL12.SQLSERVERSECOND\MSSQL\DATA\Tfs_CoE.mdf', MOVE 'Tfs_CoE_Log' TO 'D:\MSSQL12.SQLSERVERSECOND\MSSQL\DATA\Tfs_CoE.ldf', RECOVERY, REPLACE; See RESTORE (Transact-SQL) for the full documentation.


0

Simple way to import .trc file without any errors by opening file into SQL Profiler tool -> File -> Save as -> Trace Table -> SQL Table import option will pop up. Good Luck


0

You can script this whole process out with TSQL and SQL Agent jobs depending on the your cleanup process -- I assume you can or already have it scripted but here are the basics how I've dealt with similar issues in environments I maintain and support. Switch recovery model to simple on primary DB (TSQL with SQL Agent job step) -- this will break the log ...


1

Just a quick thought on something to look into, in a domain type environment, some operating systems allow you to logon to the server before full network connectivity is established. You may want to check for either local or domain level group policy settings to not allow logon or OS startup until full network connectivity is established. Just in case ...


0

Not sure of all your environment specifics or too familiar with cluster configurations with SQL Server 2014 Enterprise, but you may want to look into Synchronous Database Mirroring (High-Safety Mode) and the High-Safety Mode with Automatic Failover type configuration. I think this requires the two mirrored instance DBs in conjunction with a separate ...



Top 50 recent answers are included