New answers tagged

1

What you need is a "student" table a "major" table a linking table (also known variously as an "intersection" or "association" or "joining" table). The fancy name for this is "Associative Entity" (if you read nothing else, read this!). 1 row per "student" in the student table - this is critical for good design - otherwise you are going to have to keep ...


0

I'm sure there are lots of ways to solve this. The first thing that comes to my mind is to create a simple scalar value function that accepts the Student-Id and 'type' of column you are trying to populate and returns a delimited string of values for that student and type of column. The scalar value function (GetInfo) uses the student id and the 'type' of ...


2

Likely your database (the one your are attempting to restore/replace) currently has open connections. Try this logic: ALTER DATABASE <yourdatabase> SET SINGLE_USER WITH ROLLBACK IMMEDIATE; RESTORE DATABASE <yourdatabase> FROM DISK = "D:\Backups\BackupFile.Bak" WITH REPLACE, RECOVERY; ALTER DATABASE <yourdatabase> SET MULTI_USER; ...


0

You could use INNER LOOP JOIN to cause a nested loop, but it may not improve the performance. Or use an OPTION (LOOP JOIN) hint to still allow the optimiser to decide join order (which is forced by join hints).


-5

To repair a corrupt .mdb database of MS Access, one can try Compact and Repair feature of MS Access. It repairs corrupt database and makes it accessible for database users. Other than this one can also use import feature of MS Access that helps database users import their corrupt database to a new database file.


2

In short, you want to use this type of condition for the query to be efficient: SELECT COUNT(*) FROM my_table WHERE my_datetime >= '2015-07-28' AND my_datetime < '2015-07-29' ; Now the only problem is to find the date values of the "same" day, last year. Since the weekday and week of a day of the year is a rather complicated calculation, I would ...


1

I like Kenneth Fisher's response. If you don't have the option of creating the calendar table, I think you should looks for the exact date first, set that date as a variable, then use the variable in your WHERE clause. Building the date takes less than a second, then you have a direct filter. Took a little while to get the calculations right, but I think ...


2

Why not try using a calendar table? Using the code to create one (took about 1 second) from here. SELECT COUNT(1) FROM My_Table JOIN #Dim ON My_Table.my_datetime = #Dim.date WHERE DATEPART(year,CAST(getdate() AS DATE))-1 = [year] AND DATEPART(week,CAST(getdate() AS DATE)) = [week] AND DATEPART(weekday,CAST(getdate() AS DATE)) = [DayOfWeek] In ...


0

This might be a little more of a satisfactory answer. The problem was solved by initialising a Spatial function that calls a CLR assembly. After that the query works. I know that that was the issue because when I restore the database in question the problem comes back and the solution is as described above. I can call any spatial function that calls a CLR ...


3

The "memory" being referred to in the term "memory pressure" is physical RAM, not hard-drive space. What exactly are you attempting to do when you get this error? It seems that it could be due to attempting to invoke CLR code from within an sp_OA* OLE-Automation stored procedure call. That is documented in the following Microsoft forum thread: CLR ...


0

Think you can, of course stop all jobs making sure that all logs have been applied (restored) on the secondary to make sure it stays in sync. It sounds like the jobs you will be changing are the backup job on the primary and the copy job on the secondary.


0

Having been pointed in the right direction by @Phil, it looks like the following merge will work: MERGE userabilitytest USING (VALUES (1, 'EDITACCOUNT'), (1, 'EDITORDER'), (1, 'BOOHOO'), (1, 'FooBar') ) AS foo(account_id, ability) ON userabilitytest.account_id = foo.account_id and userabilitytest.ability ...


0

Have you checked the CPU / Memory stats across servers where one SP is significantly different from another? (I assume your I/O are of the same performance more or less)? I would also check various wait statistics, using Paul Randal's query as good starting point http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/


3

It's almost certainly going to be full backups or indexing. The queries won't show up as a bunch of memory allocated but rather as intensive disk IO. If you are tracking those counters then have a look. Some other easy ways to confirm that these are the cause of that disk IO: Looking at agent job schedules. If it wasn't checkdb, or indexing, then apps ...


0

Before you say PLE is too low, you should know threshold calculation for PLE. You can visit the URL to know that http://sqlperformance.com/2014/10/sql-performance/knee-jerk-page-life-expectancy. PLE alone don't impact performance of SQL server. There are various performance counter we should consider like Buffer cache hit ratio, disk queue length, ...


0

Unfortunately PLE alone isn't going to tell you much. What's your baseline PLE? Does it dip then steadily rise again, or does it stay low for an extended amount of time? Are you getting any performance hits? My advice would be to build up a bigger picture of the issue by looking at other memory related performance measures. Page Life Expectancy is just ...


0

In my case, truncating and re-populating data in the concerned tables was the solution. Most probably the data inside tables was corrupted.


3

I have to assume some things here because I'm not sure what your table looks like or what your columns are, but let's assume you have a table called StudentHistory. Let's say StudentHistory contains the columns StudentID, StudentName, SchoolName, LeaveDate. Each student may have multiple entries depending on how many schools they have frequented. These ...


1

There is a bug in the SQL Server 2008 Upgrade system where even after upgrade, the system still shows the evaluation expiration message. This is resolved in SQL Server 2012 onwards.


2

There are two ways to encrypt the SSL connection. Configure server for all the incoming client request for SSL. Selective client configuration. In the first case if you installed certificate on the server and make "Force encryption to yes" then no matter which client is querying for connection, it will always create SSL connection. In the second case ...


0

Knowing by default newer SQL Server builds keep the TCP communications off whereas JDBC and jTDS require it I went ahead and enabled TCP in Configuration Tools -> Configuration Manager. In SQL Server Network Configurations -> Protocols I enabled the TCP protocol and then was able to connect.


1

(emphasis added) Is there any way to definitively prove it was/wasn't No, there is no way to prove in any definite way that it has been executed, outside of coding the stored procedure to do something like insert a record into a log table showing that it had been executed. In your particular case, this would also require a time machine in order to put ...


2

Starting from SQL Server 2008,use can sys.dm_exec_procedure_stats which gives you the status on when was the stored procedure last executed select db_name(database_id) as dbname,last_execution_time from sys.dm_exec_procedure_stats


5

You can use a ROWVERSION column for this. The documentation states that Each database has a counter that is incremented for each insert or update operation that is performed on a table that contains a rowversion column within the database. The values are BINARY(8) and you should consider them as BINARY rather than BIGINT as after ...


-2

Have you tried with AUTOINCREMENT option? An example: [RowUpdateCounter] [bigint] NOT NULL AUTOINCREMENT(1,2), where, 1 --> Starting value 2 --> Incremented by This is similar to SEQUENCE in Oracle. Sorry my fault. SQL server supports IDENTITY option [RowUpdateCounter] [bigint] NOT NULL IDENTITY(1,2) where, 1 --> Starting value ...


0

I resolved the issue by changing Use the below code to resolv the issue. DECLARE @ReturnVal varchar(100) IF (@Num_Credit_Limit 0) ---- CURRENT CREDIT IS GREATER THAN OR EQUAL TO THE CREDIT LIMIT. SELECT @ReturnVal = 'Credit Limit Exceeded.'; ELSE SELECT @ReturnVal = '0'; RETURN @ReturnVal


4

From the documentation: --Transact-SQL Scalar Function Syntax CREATE FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type [ = default ] [ READONLY ] } [ ,...n ] ] ) RETURNS return_data_type [ WITH <function_option> [ ,...n ] ] [ AS ] BEGIN ...


4

Realizing that this is a complete redesign of your intent but why not script out the change for each table? Ie create a new table, copy the data over, re-create all indexes, security etc. The easiest way to do this would be to use the designer, make your changes, don't hit save, but instead right click on the background of the designer and request "...


2

Here is how to find the "illegal" character: There are two places in your query that cast to XML, so start with confirming which one is throwing the error. I doubt it is the outer SELECT since that is just taking a subset of the data that is already valid XML. And, if it was the outer CAST, that would mean that an individual deadlock graph would have at ...


0

As wBob noted in the comments, there have been a few issues with the extended events XML output. You could update your servers and hope the issue is fixed in a more recent build. However, you say that you are testing the query on another system and it's working fine, but you are trying to parse the extended events in the ring_buffer, which means it's from ...


0

Firstly, be aware of shrink operation via maintenance plan will effect on *.mdf file and *.ldf file. so you need to create a maintenance plan with SQL job task and write the following command to can only shrink *.ldf file to your appropriate target size. use sharepoint_config go alter database sharepoint_config set recovery simple go dbcc shrinkfile('...


3

Dimensions in Microsoft SQL Server Analysis Services support either ROLAP or MOLAP. Open the dimension in BIDS 2008, select the root dimension node and change this in the properties window. For fact data you open the cube and can similarly set the storage mode by selecting a measure group and altering the storage mode to one of HOLAP/ROLAP/MOLAP in ...


0

Not sure if it will improve performance but you are killing a number of left join with the where WHERE ipx.ProviderType IN ('IRF') turns that into a join SELECT ip.EpisodeID, SUM(ipx.LengthOfStay) as LengthOfStay FROM dbo.[IPHDR] ip LEFT JOIN dbo.[IPHDR_EXT] ipx ON ip.IPHDRRecordID = ipx.IPHDRRecordID WHERE ipx.ProviderType IN ('IRF') GROUP BY ip.EpisodeID ...


1

This is where OLAP puts everything by default, but during the install I believe you can choose where you want your olap database (Data Directories tab). Look for a .db folder for your olap database in the below location, as well as supporting files (like .xml) in the OLAP folder for your olap database. ...\Program Files\Microsoft SQL Server\MSAS12....


1

You need to check the actual size of the database files which you can check by running RESTORE FILELIST ONLY FROM <backup_device> This will give you information about the files contained in the backup and their sizes in bytes You need the same space available on the server as the database file size, the backups can be compressed. Now you need ...


2

You're missing a CREATE USER to add them in as a database principal. CREATE LOGIN [TRAINW7X64CA04\Test8] from Windows with default_database = Eclipse; Use Eclipse; CREATE USER [TRAINW7X64CA04\Test8]; EXEC sp_addrolemember N'db_owner', N'TRAINW7X64CA04\Test8';


0

you can have a filter index if filter values are static or if not they you can have a composite index based on your filter columns and include rest of columns in it out of 15 col which are in that view if there is not too much insert,update,delete but as you said there is no other index except clustered then you may probably bear the cost of another index......


0

Add an index for each A, B, C, D, E, F Or you could add one composite index on A, B, C, D, E, F


0

This helped me to crack the interview that's why I'm posting to help someone for the same. Good luck!


-3

What about EXEC master..sp_msloginmappings?


1

Since your day is divided into 5-second buckets, you'll need to do the same to your timestamp in the table. Since you're counting the 5-second buckets and not the actual number of updates, you'll need to use either a CTE or derived table. I'll use the CTE only because I think it's easier to read. WITH UpdateIntervals AS ( SELECT DATEADD(SECOND, ...


0

This sounds like a case where the key_guid in the encrypted data does not match the key_guid for the symmetric key used to encrypt it. When data encrypted using a symmetric key and is then saved in a varbinary column, the first 16 bytes are the Key_guid of the symmetric key used to encrypt the data, which explains how DECRYPTBYKEYAUTOCERT can automatically ...


11

Each server is a quad core virtual machine with 16GB of RAM provisioned on a server rack. We populate each server with the maximum of 50 instances allowed by SQL Server. Each instance will be accessed sporadically by a maximum of 10 clients simultaneously, and each database will in general only be around 100MB to 500MB in size. IMHO, your total RAM ...


2

Set a maximum of 300MB per instance and be done with it. Seriously, you can monitor it with something like this to determine which may be candidates for giving a little more or less to. SELECT DB_NAME(database_id) AS [Database Name], COUNT(*) * 8/1024.0 AS [Cached Size (MB)] FROM sys.dm_os_buffer_descriptors WHERE database_id > 4 -- system databases AND ...


21

This works too: SELECT salesid, SUM(num) FROM #BE GROUP BY salesid, CASE WHEN num >= 0 THEN 0 ELSE id END; Assumptions: Id starts at 1, hence it can use THEN 0. salesid ELSE salesid+id+1 would work as well 0 is considered positive number, hence the >= 0 (Is zero positive or negative?). Although x+0=x seems to make the = sign unnecessary, it helps ...


20

Try this: SELECT salesid, sum(num) as num FROM #BE WHERE num > 0 GROUP BY salesid UNION ALL SELECT salesid, num FROM #BE WHERE num < 0; If you want both the sum values in one row then you must create a maxValue (and minValue) function and use this as sum(maxValue(0, num)) and sum(minValue(0, num)). This is described in: Is there a ...



Top 50 recent answers are included