Tag Info

Hot answers tagged

13

I know of no hacks to get SQL Server 2000 to install on an existing Windows 10 installation. There are probably forces against you from both within the setup program (which looks for certain things on the host system) and within the operating system (which prevents many flavors of older software from getting installed, mostly for security reasons but also ...


9

So, we are changing the approach to delete small batch of rows at at time. This is a really good idea to delete in small careful batches or chunks. I would add a small waitfor delay '00:00:05' and depending on the recovery model of the database - if FULL, then do a log backup and if SIMPLE then do a manual CHECKPOINT to avoid bloating of transaction ...


8

This is most easily implemented in SQL Server using a Recursive Common Table Expression. Table definition DECLARE @binaryUser AS TABLE ( id integer NOT NULL, joiningDate date NOT NULL, placement char(1) NOT NULL, pId integer NOT NULL, cId integer NOT NULL, referBy integer NOT NULL ); Data INSERT ...


8

Microsoft exams (and preparation material) tends to be stupid in some cases. It works best with excluding answers. You have to keep records? So truncate is not an option for you (and creating a new table is typically not the right way to solve it). Delete does free up the space. Of course not the harddisk space but (again typically) the space inside the ...


6

The semantics of the two queries are not the same - UNION removes duplicates, whereas the FULL OUTER JOIN will not: DECLARE @T1 AS table (id bigint NULL, val integer NULL); DECLARE @T2 AS table (id bigint NULL, val integer NULL); INSERT @T1 (id, val) VALUES (1, 1); INSERT @T1 (id, val) VALUES (1, 1); INSERT @T2 (id, val) VALUES (1, 1); INSERT @T2 (id, val) ...


6

If I understand the request correctly, the goal is to delete batches of rows, while at the same time, DML operations are occurring on rows throughout the table. The goal is to delete a batch; however, if any underlying rows contained within the range defined by said batch are locked, then we must skip that batch and move to the next batch. We must then ...


5

This cannot be done with a computed column. One option is to use a trigger: Sample data CREATE TABLE dbo.Example ( GroupKey integer NOT NULL, [Status] integer NOT NULL, inserted_date datetime NOT NULL DEFAULT GETUTCDATE(), IsLastTimeOccurred bit NOT NULL DEFAULT 'false', CONSTRAINT PK_dbo_Example_GroupKey_Status_inserted_date ...


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


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


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


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


4

You're right: system administrators can gain sysadmin access, but you will find entries in the default trace for that action. Moreover, it requires restarting the service at least twice, which would hardly go unnoticed. Not granting sysadmin privileges to BUILTIN\Administrators is a change introduced in SQL Server 2008. In my opinion it's a good idea: it ...


4

Method 1 : Comes with a cost You can use a third party software which will do everything for you in terms of gathering the data and presenting the reports for database growth and predicts the same depending upon the gathered data as explained here Method 2: Create table, run the stored proc using SQL agent job and scheduled accordingly to gather the data ...


4

First, just to make sure that a collation is available or not, you can test in two ways: Check the list of available collations (i.e. sys.fn_helpcollations()). Running the following query in SQL Server 2012 and 2014 should return 52 rows: SELECT * FROM sys.fn_helpcollations() WHERE [name] LIKE N'ukrain%'; Try using the collation: SELECT N'a' COLLATE ...


4

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


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


3

You have to concentrate on Memory, Disk IO and CPU utilization to balance out your environment. Best is to start collecting and dumping the results in a utility database and to give you a good baseline using sql agent on a scheduled basis. Alternatively, you can use Performance data collector (applies to sql server 2008 and up) MEMORY: You can look ...


3

Another way would be to have a computed column. But the values of this column depend not only on the values of the other columns in the same row but from other rows as well, so this isn't allowed in SQL-Server (and not in any other DBMS I know). You can still have though such a computed column in a view, with: create view vlog as select log.*, ...


3

In SSMS, right-click on the database and go to "Reports", "Standard Reports", "Disk Usage by Table". It will tell you the total size, the data size, the index size, and the unused size for each table (as well as the row count).


3

It's been answered on Stack Overflow: SELECT t.NAME AS TableName, s.Name AS SchemaName, p.rows AS RowCounts, SUM(a.total_pages) * 8 AS TotalSpaceKB, SUM(a.used_pages) * 8 AS UsedSpaceKB, (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB FROM sys.tables t INNER JOIN sys.indexes i ON t.OBJECT_ID = ...


3

Given the additional information that Aaron Bertrand didn't have access to when he posted his answer I would suggest a different tack. Instead of putting logic/business significance in table names I would have general table names and put the logic/business significance in attributes/data in the tables. This should make it easier to expand functionality, ...


3

Something like this (untested) should work for you: select *, case q20 when '8' then 1 when '9' then 1 else case q46 when '8' then 1 when '9' then 1 else 0 end end as promoter from ##csat_resp; You could also write the same logic as: case when q20 in ('8','9') or q46 in ('8','9') then 1 else 0 end or in SQL ...


3

Are there exceptions to the above clustered index rules? The general guidance for selecting a clustered index is good, but there are sometimes additional considerations to be taken into account. Occasionally, these extra factors can be more important than the general 'rules'. Your scenario is somewhat "special" in that you have a very wide table, with a ...


3

Option A Take a command and control approach. Designate your newest SQL Server tier of boxes as the runner of all things SSIS. Your connection manager for SQL Server will be able to speak the appropriate TDS dialect for everything in your enterprise. Knowing nothing else about your organization, I would generally favor this approach. The management ...


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.


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


3

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


2

Not sure of the exact syntax for SQL Server (there's an edit that says that your RDBMS is SQL Server), but I'd do something like this (I'm making the assumption that a TRANSACTION is a - single - command :-). I don't think it's possible in a single line command. (pseudocode) BEGIN TRANSACTION; CREATE TABLE dummy AS SELECT * FROM My_Original_Table WHERE ...


2

Please then modify your database to be in 'SIMPLE' recovery mode instead of 'FULL' or 'BULK LOGGED'. The full recovery mode never truncates the transaction log, unless you do a transaction log backup. Only after it's backed up will SQL Server reuse the empty space in the transaction log. Now setting the DB to simple mode, taking a full backup, and ...


2

I just quickly grabbed your query and re-wrote it without looking at how you rewrote it. SELECT DISTINCT a_stmt.cyc_dte, a_carr.carr_nm, a_stmt.amt_du_ic FROM a_stmt LEFT OUTER JOIN a_icc on a_stmt.ic_cntct_id = a_icc.ic_cntct_id INNER JOIN a_carr on a_carr.acna = a_stmt.acna INNER JOIN s_dataccs on s_dataccs.acna = ...



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