texts: Insert/Update SQL Server Stored Procedure

texts:
I have to insert or update into a table depending on a record. I am using a stored procedure to do this. I am very new to SQL Server and stored procedures. I tried to follow various articles and managed to create a stored procedure using MERGE.  The table looks likes this,

bl   em  number  title email fl name opt

null 101 00659   ABC   null  01 John null

null 102 00660   DEF   null  02 Rosy null

And the procedure:

CREATE PROCEDURE [dbo].[uspInsUpd]
    @bl         char(8),
    @em         char(35),
    @number     char(12),
    @title      varchar(64),
    @email      varchar(50),
    @fl         char(4),
    @name       char(32),
    @opt        char(16),

AS

MERGE [dbo].[employee] AS [Target]
USING (SELECT @bl, @em , @number, @title, @email, @fl, @name, @opt)
   AS [Source] ( [bl], [em], [number], [title], [email], [fl], [name], [opt])
ON [Target].[em] = [Source].[em]

WHEN MATCHED THEN
     UPDATE SET [bl]      = [Source].[bl],
                [em]      = [Source].[em],
                [number]  = [Source].[number],
                [title]   = [Source].[title],
                [email]   = [Source].[email],
                [fl]      = [Source].[fl],
                [name]    = [Source].[name],
                [opt]     = [Source].[opt],

WHEN NOT MATCHED THEN
     INSERT ( [bl], [number], [email], [fl], [name], [opt], [em],[title])
     VALUES ( [Source].[bl], [Source].[number], [Source].[email], [Source].[fl], [Source].[name], [Source].[opt], [Source].[em], [Source].[title]);
GO

Now I am not sure how can I test this stored procedure. Can anybody tell me if my above code is right?


texts: Cleaning up MSrepl_errors, SQL Server Replication

texts:
Our MSrepl_errors table is around 15GB and got records from the beginning of time.
What is the correct way of cleaning up this table.  Do we simply delete from it?

Edit
The distribution agent clean up is already running.

Cheers


texts: SQL 2005 Full Recovery Mode Point in Time Restore w/out Backup

texts:
We have a database that is in full recovery mode.  The database is not in production yet so does not have a backup plan in place and is thus not being backed up.  In other words, there is no existing backup. Recently, during an update, about 400 rows of data were wiped out.

Is there a way to recover those rows given that we have full transaction logging?   I understand that if we had a full backup w/ transaction logs we could do a point in time recovery.  Will this work if we now take a full backup of transaction logs and the database, then do a recover?

I feel like there is one path to recovery, I'm just unclear of the order in which to proceed.

Thanks for your assistance!

UPDATE:  We ended up using this tool http://www.apexsql.com/sql_tools_recover.aspx I would still be interested if anyone comes across this and knows a better way.  For now, we paid the price, but we got the results.  Thanks for the responses!


texts: Insert multiple values in a text field using a "delimiter"?

texts:
Let's say I have a Text field called "description" inside "products" table.

Inside this field I will store 3 values. I will separate them with two pipes ( || ):

requirements:
- bla bla.. (a text)
||
info:
- bla bla.. (a text)
||
specifications:
- bla bla.. (a text)

I am doing this because it looks better than having 3 different Text fields to do the job (which will increase the table's size).

Is this a bad idea? Should I use 3 different fields? Does 3 text fields really affect database performance?


texts:
I have a problem, I have two servers  where I have to save my data. For persistent data I run mySql inno DB. I have a lot of entries, so I need fast (CRUD operations).

For indexing I was thinking to use UUID() or UUID_SHORT(). Auto increment is out of the options, because the data will be replicated.

The problems are that, at UUID() the DB will become very slow. At UUID_SHORT() the problem arise that it's not true unique, if I won't often restart mySql server. According to documentation, mySql DOC I can create only 16000000 entries.

What should I use for indexing?

Any help would be appreciate.


texts: Using ddl triggers with in-memory tables

texts:
I've created server scope ddl trigger to audit user activities on the server.
Now if someone tries to create in-memory table the statement fails with the following error:

Error SQL72014: .Net SqlClient Data Provider: Msg 12332, Level 16, State 107,
Line 1 Database and server triggers on DDL statements DROP and CREATE are not
supported with memory optimized tables.

Can I create ddl trigger that will ignore actions on in-memory tables?


texts: Possible issues dumping a legacy MySQL 5.0 database to a local MariaDB 10.2 for develpment?

texts:
I've inherited a site that uses MySQL 5.0 and want to get a development version up and running with the existing data on my local (OS X) machine where I have MariaDB 10.2 but the compatibility tables are a bit hard to fathom, and don't include MySQL 5.0.

All I need is a copy of the MySQL data on the sever running locally so I can work with the "real" data, but it's unclear whether mysqldump will work between the two.

I don't want to install MySQL on my local machine and have dev sites that use it now, so I don't want to downgrade my local MariaDB. I could update MySQL on the server, but I'd rather leave it for now (it's a live service).

How would you approach this?


texts: How can I check the integrity of disabled foreign key relationships for each individual table? (SQL Server 2008)

texts:
I'm porting a huge legacy database that many different legacy frontends depend on. When I'm finished, a new frontend will be running alongside all the old legacy frontends minus the frontend I replaced against the ported database.

Anyway, the legacy database doesn't have any foreign key relationships defined. My plan is to start putting them in, but leave them disabled to support the old frontends. However I want to use the disabled foreign key relationships in some kind of stored procedure to check the integrity of the database (I'll execute this as part of my integrity test suite every so often). Would that involve attempting to turn each one on, and then recording the error message, then turning it off again? Is there a special command that can do this all in one go?

Any scripts you have to help would be nice, I'm not good at T-SQL. Ideally I'd like the script to return a table consisting of the table name, a record's primary key value, and the  name of the foreign key for that record that failed.

Thanks!


texts: PGPool II + Django w/ psycopg2 load balancing

texts:
I've a pool of applications running django 1.6 with psycopg2 on top of a PGPool II with two backends PostgreSQL servers. But all queries (read and/or write) are going to master PostgreSQL even SELECT.

As we can see on PGPool II documentation, if we have read queries into transactions we need to hit some conditions to load balancing os send to master. I know that we have some points of the code that we have SELECT into a transaction but we also have simple SELECT queries which is going to master too.

I'm not sure if, and what, conditions we are hitting to send all queries to master.

Here the table with conditions for load balancing:


texts:
Context:

PostgreSQL 10, with 3667438 records in users table, the users table has a JSONB called social, we usually use a strategy of indexing computed function outputs, so we can aggregate information into a single index.
The output of the engagement(social) function it a double precision numeric type.

Problem:

The problematic clause is ORDER BY engagement(social) DESC NULLS LAST, there is also a btree index idx_in_social_engagement with DESC NULLS LAST attached to this data.

Fast query:

EXPLAIN ANALYZE
SELECT  "users".* FROM "users"
WHERE (follower_count(social) < 500000)
AND (engagement(social) > 0.03)
AND (engagement(social) < 0.25)
AND (peemv(social) < 533)
ORDER BY "users"."created_at" ASC
LIMIT 12 OFFSET 0;

Limit  (cost=0.43..52.25 rows=12 width=1333) (actual time=0.113..1.625
rows=12 loops=1)
   ->  Index Scan using created_at_idx on users  (cost=0.43..7027711.55 rows=1627352 width=1333) (actual time=0.112..1.623 rows=12 loops=1)
         Filter: ((follower_count(social) < 500000) AND (engagement(social) > '0.03'::double precision) AND (engagement(social) <  '0.25'::double precision) AND (peemv(social) > '0'::double precision) AND (peemv(social) < '533'::double precision))
         Rows Removed by Filter: 8
 Planning time: 0.324 ms
 Execution time: 1.639 ms

Slow query:

EXPLAIN ANALYZE
SELECT  "users".* FROM "users"
WHERE (follower_count(social) < 500000)
AND (engagement(social) > 0.03)
AND (engagement(social) < 0.25)
AND (peemv(social) > 0.0)
AND (peemv(social) < 533)
ORDER BY engagement(social) DESC NULLS LAST, "users"."created_at" ASC
LIMIT 12 OFFSET 0;

Limit  (cost=2884438.00..2884438.03 rows=12 width=1341) (actual time=68011.728..68011.730 rows=12 loops=1)
->  Sort  (cost=2884438.00..2888506.38 rows=1627352 width=1341) (actual time=68011.727..68011.728 rows=12 loops=1)
        Sort Key: (engagement(social)) DESC NULLS LAST, created_at
        Sort Method: top-N heapsort  Memory: 45kB
        ->  Index Scan using idx_in_social_engagement on users  (cost=0.43..2847131.26 rows=1627352 width=1341) (actual time=0.082..67019.102 rows=1360633 loops=1)
            Index Cond: ((engagement(social) > '0.03'::double precision) AND (engagement(social) < '0.25'::double precision))
            Filter: ((follower_count(social) < 500000) AND (peemv(social) > '0'::double precision) AND (peemv(social) < '533'::double precision))
            Rows Removed by Filter: 85580
Planning time: 0.312 ms
Execution time: 68011.752 ms

The select goes with * because I need all the data stored in each row.

Update:

CREATE INDEX idx_in_social_engagement on influencers USING BTREE ( engagement(social) DESC NULLS LAST)

Exact index definition


texts: Can MS SQL 2008 R2 backup database to a file on different network location...I've been unable to set network path for backup to other machine on local network via tasks-backup-destination.add location...


texts: How to set up login/guest and checkout/add to cart

texts:
I want to create an online store database. I want to give users the ability to 'add to cart' and 'check out' without login.

My idea is that if the user logs in, then I can save the information in tables ex(bag, order), but if the user wants to check out as guest than I can create a guest_unique_id and set up bag that way. Guest bag will be mannually deleted after 10 days.

The only issue I see is that sometimes account_id will be empty. I guess I can check to see if guest_unique_id is NULL then user has an account, if not then it is a guest.
What you guys think about this structure, is there a better way to do this?


texts: Error while importing flat file (CSV) in SSMS 17.5

texts:
I'm not sure if this is a bug or not, but when trying to import a CSV file (with no headers and text qualified (")) using the new "Import Flat File" in SSMS, I get this error:
"A column named foo already belongs to this datatable (System.Data)".

It fails right after I enter the tablename and it starts trying to preview the data. I've loaded other similar files just the same and I don't think the format is wrong. Am I doing something wrong and is there a workaround?

SSMS 17.5 importing into SQL Server 2016


texts: SQL Server performance: PREEMPTIVE_OS_DELETESECURITYCONTEXT dominant wait type

texts:
I received a call yesterday from a customer who was complaining about high CPU usage on their SQL Server. We're using SQL Server 2012 64 bit SE. Server is running Windows Server 2008 R2 Standard, 2.20 GHz Intel Xeon (4 Cores), 16 GB RAM.

After making sure that the culprit was in fact SQL Server, I took a look at the top waits for the instance using the DMV query here. The top two waits were: (1) PREEMPTIVE_OS_DELETESECURITYCONTEXT and (2) SOS_SCHEDULER_YIELD.

EDIT: Here are the result of the "top waits query" (although someone restarted the server this morning against my wishes):

We do a lot of intense calculations/conversion, so I can understand SOS_SCHEDULER_YIELD. However, I am very curious about the PREEMPTIVE_OS_DELETESECURITYCONTEXT wait type and why it might be the highest.

The best description/discussion that I can find on this wait type can be found here. It mentions:

  The PREEMPTIVE_OS_ wait types are calls that left the database engine, typically to a Win32 API, and are performing code outside of SQL Server for various task.  In this case, it is deleting a security context previously used for remote resource access.  The related API is actually named DeleteSecurityContext()

To my knowledge, we don't have any external resources like linked servers or filetables. And we don't do any impersonation, etc. Could a backup have caused this to spike or maybe a faulty domain controller?

What the heck could cause this to be the dominant wait type? How can I track this wait type further?

Edit 2: I checked the contents of the Windows Security Log. I see a few entries that may be of interest, but I'm not sure if these are normal:

Special privileges assigned to new logon.

Subject:
    Security ID:        NT SERVICE\MSSQLServerOLAPService
    Account Name:       MSSQLServerOLAPService
    Account Domain:     NT Service
    Logon ID:       0x3143c

Privileges:     SeImpersonatePrivilege

Special privileges assigned to new logon.

Subject:
    Security ID:        NT SERVICE\MSSQLSERVER
    Account Name:       MSSQLSERVER
    Account Domain:     NT Service
    Logon ID:       0x2f872

Privileges:     SeAssignPrimaryTokenPrivilege
            SeImpersonatePrivilege

Edit 3: @Jon Seigel, as you requested, here are the results of your query. A little different than Paul's:

Edit 4: I admit, I'm a first time Extended Events user. I added this wait type to the wait_info_external event and saw hundreds of entries. There is no sql text or plan handle, only a call stack. How can I further track down the source?


texts:
I have made the following query to select a record that isn't 0.

SELECT * FROM Table WHERE Bit != 1 OR Bit IS NULL;

Why is the check Bit != 1 not enough for a nullable BIT?


texts: Is there any way to check whether a row is locked before updating it?

texts: The bottleneck of my app is concurrent updates to the same row in MySQL. Losing some updates wouldn't be nearly as bad as having them all to wait for each other to finish. Is there any way in MySQL to only update a row if there's no other concurrent update?


texts:
I'm creating a VC# application using Visual Studio.
On startup, it opens a MDF file (SQL Server Express database).

When I execute

BACKUP DATABASE db.mdf TO DISK=backup.mdf

I get the backup.mdf created.

Then I do the following:

Close the application
Delete the db.mdf
Rename backup.mdf to db.mdf
Run the application

I get the error "Login failed for user 'PC\User'"

How can I solve this?


texts: Avoiding Functions() in the Where clause

texts:
Okay, while analyzing queries against a SQL Server 2008 R2 database, I happened upon the following query:

(@p__linq__0 nvarchar(4000))
SELECT TOP (1) [Extent1].[OrderSOId] AS [OrderSOId], [Extent1]
   .[WarehouseId] AS [WarehouseId], [Extent1]
   .[MemberId] AS [MemberId], [Extent1]
   .[ShipmentMethod] AS [ShipmentMethod], [Extent1]
   .[PaymentMethod] AS [PaymentMethod], [Extent1]
   .[CustomerName] AS [CustomerName], [Extent1]
   .[Status] AS [Status], [Extent1]
   .[OrderSONo] AS [OrderSONo], [Extent1]
   .[OrderSODate] AS [OrderSODate], [Extent1]
   .[OrderType] AS [OrderType], [Extent1]
   .[DeliveryTo] AS [DeliveryTo], [Extent1]
   .[DeliveryAddress] AS [DeliveryAddress], [Extent1]
   .[DeliveryAddress2] AS [DeliveryAddress2], [Extent1]
   .[DeliveryPostal] AS [DeliveryPostal], [Extent1]
   .[DeliveryCity] AS [DeliveryCity], [Extent1]
   .[DeliveryState] AS [DeliveryState], [Extent1]
   .[DeliveryCountry] AS [DeliveryCountry], [Extent1]
   .[Remarks] AS [Remarks], [Extent1]
   .[IsDeleted] AS [IsDeleted], [Extent1]
   .[CreatedDate] AS [CreatedDate], [Extent1]
   .[CreatedBy] AS [CreatedBy], [Extent1]
   .[ModifiedDate] AS [ModifiedDate], [Extent1]
   .[ModifiedBy] AS [ModifiedBy], [Extent1]
   .[Total] AS [Total], [Extent1]
   .[AppId] AS [AppId], [Extent1]
   .[DiscountType] AS [DiscountType], [Extent1]
   .[DiscountAmount] AS [DiscountAmount], [Extent1]
   .[isPrepared] AS [isPrepared], [Extent1]
   .[ExternalRefId] AS [ExternalRefId], [Extent1]
   .[DeliveryEmail] AS [DeliveryEmail]
FROM [dbo].[OrderSO] AS [Extent1]
WHERE ([Extent1].[IsDeleted] <> cast(1 as bit))
AND ((LTRIM(RTRIM(LOWER([Extent1].[OrderSONo])))) = (LTRIM(RTRIM(LOWER(@p__linq__0)))))

Ignoring the strange way the SELECT and FROM clauses are generated, I am more concerned with the WHERE clause, because it uses functions to provide whitespace trimming and case insensitivity. According to this answer on SO, using such functions in the WHERE clause will "...will generally preclude the use of an index..."

I know that the LOWER() function could be removed by ensuring that collation is _CI_ (which it is, for the DB in question). How should I remove the LTRIM(RTRIM()) functions?


texts: FTS - Index rebuild causing CPU issues (SQL Server 2008 R2)

texts:
I am encountering some critical issues on SQL Server 2008 in a production environment; We have a database that contains a FTS index; we also have a maintenance job that rebuilds the FTS index and updates statistics like this:

alter fulltext catalog [ftCatalogName] rebuild
update statistics [tableNameWithFtIndex]

The job runs weekly, Monday at 9AM; it finishes in around 1minute and always succeeds;

Now the problem: everytime after the job finishes, the CPU of sqlserver.exe process goes up to 99%; regularly the CPU stays at around 70-80% (i know it is very high and we are trying to improve this); and the only way to make it go back to it's regular state (70-80%) is only if we stop the IIS on all front-end servers (we have a webfarm) and then start them up again - this way the CPU for sqlserver.exe goes to 0% when the IISs are stopped, and back to 70-80% when the IISs are restarted back up;

Also i've analyzed the behavior of CPU during the rebuild process:
- start FTS rebuild job (above statements)
- sqlserver.exe CPU=75% (normal); fdhost.exe=20-25% (normal during rebuild)
- job finishes in 51 secs and fdhost CPU goes to 0%, and sqlserver.exe goes to 99% and stays like this until i apply the flow described above (start/stop IIS on front-end servers)

Does anyone have any idea about why this happens? At least an explication... or some ways to optimize this index and make it less resource hungry at rebuild...

Thank you in advance!


texts: How to fix SQL Server instances with different builds on same server?

texts:
I have a Windows 2008 box and there are 5 instances of SQL Server 2008 Standard installed.

After that, SQL Server service pack patched.

Weird thing is: the builds for those instance are different. For example,

One instance build No is 10.0.5512.0
Another instance build No is 10.0.5500.0

Confused. When applied SP on the server, this patch should be for all instances on this server.

How could this happen? How to fix it?


texts: If Exists - Select Prevent Repeating Code

texts:
We receive an order list, and validate it starts with certain prefixes, example if OrderNumber does not start with ABC, than display order list to user as error. We use exists, to search for first existence, don't want to consume much performance time. If none exist, then we can start conducting other task in code.

Is there anyway in sql to get rid of the repetitive code? We have Many checks like this on multiple tables, trying to make code more efficient.

if exists
(
    select *
    from dbo.OrdersImport
    where left(OrderNumber,3) <> 'ABC'
)
begin
    select OrderNumber as OrderErrorList
    from dbo.OrdersImport
    where left(OrderNumber,3) <> 'ABC'
end
else


texts:
I need to import a spreadsheet that was being viewed with the expand/collapse feature into mysql. As shown in the screenshot, when an item (WMIMCART1 in this case) is expanded, it shows more info related to that item. Naturally, when this spreadsheet was exported as a csv file and then imported into mysql with

LOAD DATA INFILE 'carts.csv'
INTO TABLE Carts
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

it of course simply imports row for row. I would like to only load the main items (all of which start with either the substring "WM" or "VM") into a table called Carts, and then the rows that follow each main items should be imported in to a table Maintenance.

Each cart row would have the columns (Name,Location,Serial).
Each maintenance entry would have the columns (Serial,Action,Date).

Basically when the substring "WM" or "VM" is found, load that row into Carts, and the following rows into Maintenance (combining the action and date into one row) until the substring "WM" or "VM" is found again.

I have a pretty good idea of how to do this in PHP, but would like to know if there is a good way to do this just in sql with string functions, etc.


texts: Is there an Express version of Azure SQL DB?

texts:
With on-premise SQL Server, there are editions such as Express, Web, Standard, and Enterprise. Do those editions (or an analog) exist in Azure SQL DB, especially Express?

My Google-Fu is failing here, possibly because it doesn't exist.


texts: How to reduce the required frequency of ANALYSE on a postgres table?

texts:
I have a Postgres database into which about 20 million rows are inserted every day.

The records are inserted via a powershell script that calculates the average time taken to insert each record. It typically takes about 1.5ms per record (including some record parsing and a trigger).

Every now and again (typically after about 3 million rows are added) the processing time jumps up to about 12ms/record (it's a very noticeable step change that happens very quickly)

I've already found the cure. I stop the process that adds the records, do an ANALYSE on the table, and restart the process. The insert time goes back to 1.5ms/record. Although this only take 5-10 minutes, I do end up loosing several thousand records during this downtime.

The database is already shutdown for a brief period at night and all the tables get ANALYSEd regularly, but is there any way I avoid doing additional ANALYSEs during the day?

(note - I've tried simply stopping and restart the INSERT process - it makes no difference. It's only stopping, doing an ANALYSE and then restarting that makes a difference)


texts: How do I create a PRIMARY KEY/UNIQUE constraint that considers trailing space significant?

texts:
I would like to be able to store records about things that are identified by strings where space is significant. I know that T-SQL follows rules about string comparison to implicitly pad up to the length of either string when performing comparisons and that I can work around that in WHERE clauses by also comparing DATALENGTH. But I don’t know how to make a PRIMARY KEY behave similarly. How do I store data so that

tools like SSMS display it as text without me needing to CAST it
it is mostly just treated like a string type in a normal programming language (with case/space being significant in comparisons)
it is stored in a column with UNIQUE/PRIMARY KEY constraints?

I would expect the following to work if T-SQL didn’t do the weird padding-during-comparison thing:

CREATE TABLE #x (
  Id NVARCHAR(16) COLLATE Latin1_General_BIN2 PRIMARY KEY
)
INSERT INTO #x VALUES ('asdf');
INSERT INTO #x VALUES ('Asdf');
INSERT INTO #x VALUES ('asdf '); -- error here
DROP TABLE #x

But on the third insert, I get:

Msg 2627, Level 14, State 1, Line 6
Violation of PRIMARY KEY constraint 'PK__#x________3214EC0761C21167'. Cannot insert duplicate key in object 'dbo.#x'. The duplicate key value is (asdf ).


texts:
I'm having one AG with two nodes (active-passive) in one data center and another AG with two nodes (active-passive) in a different data center.

I need to have log shipping enabled between these two setups, so that a DR situation can be handled.

Most articles are advising to have 1 node in DR. however, for HA, i need to have a AG in DR.

So, with this config, can log shipping be achieved? can you please guide on how to achieve that as well?

Thanks


texts:
I am migrating a database that uses CDC from SQL Server 2008 R2 to SQL Server 2012.

There are CDC-related tables under the "System Tables" branch in Object Explorer:

cdc.ddl_history
cdc.lsn_time_mapping
cdc.captured_columns
cdc.index_columns
cdc.change_tables

After backing up the database and restoring it on a different server, the CDC-related tables are not there.

I tried to copy them across using SSIS, but they are not visible in the "Export data" wizard GUI. It appears the only way of actually copying them across would be to export them manually or by outputting SELECT * FROM... results to a file.

How can I ensure the Change Data Capture related tables are restored properly?


texts: mongodb - mongos - query a mix of sharded and unsharded collections / databases?

texts:
I am using mongodb (3.4). I have six databases. I am in the process of sharding a particularly large collection (call it 'user_posts') in one of them. I like the idea of sharding all collections in all six databases, because it improves redundancy and allows for horizontal scaling. But because I am currently terrified of shard keys, I do not want to rush this process. So, I want to shard the collection that I must, and deal with the other collections later.

So, I will have two mongo servers, where one is just a shard of the 'user_posts' collection, and the other is a shard of the 'user_posts' collection but also contains some standalone databases.

Will mongos be able to query all six databases in this scenario?

Thanks


texts: Index isn't used when value is a result of a subquery or from a wrapper query

texts:
I have a simple one-to-many relationship: accounts and events where an account may have many events.
accounts

id: uuid
...more irrelevant fields

events

id: serial
type: enum ['activated', 'deactivated', 'removed', ...more]
account_id: uuid
created_at: creation date of the row

events table index:
create index "IDX_d82e2f903c778153e7781f455e" on bank_account_events (type, bank_account_id);

I have a simple query which determines when the last event of a type occurred for a specific account:
explain select created_at
from bank_account_events
where bank_account_id = 'b12edcab-9ac5-4a09-a84c-475c8a73c964'
  and type in ('activated', 'deactivated', 'removed')
order by id desc
limit 1;

The query's result shows that we use the correct index is being used:
Limit  (cost=339.51..339.51 rows=1 width=16)
  ->  Sort  (cost=339.51..339.74 rows=94 width=16)
        Sort Key: id DESC
"        ->  Index Scan using ""IDX_d82e2f903c778153e7781f455e"" on bank_account_events  (cost=0.56..339.04 rows=94 width=16)"
"              Index Cond: ((type = ANY ('{activated,deactivated,removed}'::bank_account_events_type_enum[])) AND (bank_account_id = 'b12edcab-9ac5-4a09-a84c-475c8a73c964'::uuid))

However, when I "wrap" this query and use it as a subquery the index isn't being used:
explain select ba.id,
  (
    select created_at
    from bank_account_events
    where bank_account_id = ba.id
      and type in ('activated', 'deactivated', 'removed')
    order by id desc
    limit 1
  ) status_date
from bank_accounts ba
where ba.id = 'b12edcab-9ac5-4a09-a84c-475c8a73c964';

The result of the query above is:
Index Only Scan using ""PK_5a7a02c20412299d198e097a8fe"" on bank_accounts ba  (cost=0.27..8.43 rows=1 width=24)"
  Index Cond: (id = 'b12edcab-9ac5-4a09-a84c-475c8a73c964'::uuid)
  SubPlan 1
    ->  Limit  (cost=0.43..4.14 rows=1 width=16)
"          ->  Index Scan Backward using ""PK_dddc8f2295ddc2561044644a05a"" on bank_account_events  (cost=0.43..264940.10 rows=71513 width=16)"
"                Filter: ((bank_account_id = ba.id) AND (type = ANY ('{activated,deactivated,removed}'::bank_account_events_type_enum[])))

As you can see, it's using now the PK of the table (PK_dddc8f2295ddc2561044644a05a) which is just the serial id. Then it filters the results. Since the table has a few million rows, this is painfully slow.
Why isn't Postgres using the index in this scenario? The only change was that instead of referencing the id directly, we refer to it as ba.id.


texts: SELECT...FOR UPDATE - Will only an UPDATE release the lock?

texts:
I'm using a SELECT ...FOR UPDATE in a Java PreparedStatement.  I may or may not need to execute the update depending on the results of the SELECT.

If I execute a rs.close(); or pStmt.close(); without doing an update, will MySQL release the lock? Is there another way to release it, or do I have to execute the update?

Same question if I used Postgres, too.

Not sure if it matters, but I plan to use rs.updateRow(); for the update.
Also, does MySQL support column-level locking?

Basically, I want to increment a counter in the selected row, but only if the row's status column is ACTIVE.  If it is not ACTIVE, then I just want to release the lock without an update so other threads/processes can read/write that row.


texts: Why do innodb tables get corrupted so easily?

texts:
I'm upgrading a legacy system running MySQL 5.1 all the way to MariaDB 10.  We're on a brand new Windows server, running XAMPP. I've done plenty of DB migrations in my time but this one has just been a week of MySQL / InnoDB hell.

Tables are a mix of MyISAM and InnoDB.  Every time MySQL crashes for any reason -- ANY REASON -- one or more of the InnoDB tables gets corrupted so badly I have to manually drop them and re-import them.  By "manually drop them" I mean:

1) mysqld crashes on startup unless I do innodb_force_recovery=6, and when I do that it puts the DB in read-only mode, so DROP TABLE just gives me a table is read-only error.

2) So instead I have to stop mysqld, delete the .ibd file for the table in question, restart mysqld with innodb_force_recovery=0 (which will now work), and then finally DROP TABLE works.

3) Then I have to re-import 3-4gb tables from SQL dumps, which takes forever.

Interestingly it's never the MyISAM tables, just the InnoDB tables.

If I check the mysql_error.log, what I see is almost always something like this:

2015-12-19  1:39:41 6580 [Warning] InnoDB: Allocated tablespace 234, old maximum was 0
InnoDB: Error: trying to access page number 2601 in space 234,
InnoDB: space name example/foobar,
InnoDB: which is outside the tablespace bounds.
InnoDB: Byte offset 0, len 16384, i/o type 10.
InnoDB: If you get this error at mysqld startup, please check that
InnoDB: your my.cnf matches the ibdata files that you have in the
InnoDB: MySQL server.
2015-12-19 01:39:41 19b4  InnoDB: Assertion failure in thread 6580 in file fil0fil.cc line 5821
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
151219  1:39:41 [ERROR] mysqld got exception 0x80000003 ;

After re-creating and importing the tables everything works great, as long as mysqld doesn't crash.

Sadly I can reproduce the steps to cause this pretty darn easily.  All I have to do is run a really slow query -- for example, there is a particular report our system generates and the query behind it easily runs for 30 seconds (that's another story).  If I happen to stop the mysqld service before it's finished, the very next time I start mysqld it crashes immediately and I'm back to dealing with corrupted InnoDB tables and repeating all the above steps.

I've tried innodb_fast_shutdown = 0, but it didn't help.

You might say, "just don't stop mysqld while that query is running"... and you'd be right, except live users in our system can be running that report at any time, which means any time I need to restart mysqld for any reason I'm taking a chance that it won't restart and I'll have to re-create tables all over again.

What am I doing wrong here?

Update 1
The long running query is just this (changed actual name of table, otherwise this is verbatim):

SELECT * FROM `foobar` ORDER BY `daterun` DESC

The table has approx 130,000 rows, and is about 4GB in size.


texts: How do I get MS Access 2016 to work with SQL Server 2016 where DATETIME columns are involved?

texts:
I'm working in an environment that has a lot of apps that are a MS Access 2016 front-end with a SQL Server 2016 back-end.

Symptoms:

When viewing data from a table where a DATETIME column is part of the unique identifier that Access is using, some rows show up with #Deleted# in every column.
When updating a table with a DATETIME column, but the DATETIME is not being updated, the update fails with the error that someone else has modified the row, even though nothing has changed.

Root cause:

SQL Server 2016 has a new "interpretation" of DATETIME values. Here's
a link to a discussion of the same problem happening with Entity
Framework: Change in datetime2 implementation in SQL Server 2016

Temporary workaround:

set database compatibility level to 120 (SQL Server 2014) - problem
disappears, but can't use new features, and query performance is
affected.

Possible solutions:

Changing all DATETIME columns to DATETIME2(3)
Add a timestamp column to all tables.

Both possible solutions have solved the issue in testing, but they aren't real practical. That would be a lot of work. I would like to solve this without modifying every table in every database.

I have tried updating ODBC drivers. I have tried rolling back to older drivers. I have also tried older versions of Access.

Code generated by Access for symptom 1:

declare @p1 int
set @p1=11
exec sp_prepexec @p1 output,N'@P1 datetime2,@P2 varchar(50)',N'SELECT "TimeStampKey","UserID"  FROM "dbo"."TurboStaging"  WHERE "TimeStampKey" = @P1 AND "UserID" = @P2','2013-12-13 08:04:02.4200000','dasmith'
select @p1
GO
declare @p1 int
set @p1=12
exec sp_prepexec @p1 output,N'@P1 datetime2,@P2 varchar(50),@P3 datetime2,@P4 varchar(50),@P5 datetime2,@P6 varchar(50),@P7 datetime2,@P8 varchar(50),@P9 datetime2,@P10 varchar(50),@P11 datetime2,@P12 varchar(50),@P13 datetime2,@P14 varchar(50),@P15 datetime2,@P16 varchar(50),@P17 datetime2,@P18 varchar(50),@P19 datetime2,@P20 varchar(50)',N'SELECT "TimeStampKey","UserID"  FROM "dbo"."TurboStaging"  WHERE "TimeStampKey" = @P1 AND "UserID" = @P2 OR "TimeStampKey" = @P3 AND "UserID" = @P4 OR "TimeStampKey" = @P5 AND "UserID" = @P6 OR "TimeStampKey" = @P7 AND "UserID" = @P8 OR "TimeStampKey" = @P9 AND "UserID" = @P10 OR "TimeStampKey" = @P11 AND "UserID" = @P12 OR "TimeStampKey" = @P13 AND "UserID" = @P14 OR "TimeStampKey" = @P15 AND "UserID" = @P16 OR "TimeStampKey" = @P17 AND "UserID" = @P18 OR "TimeStampKey" = @P19 AND "UserID" = @P20','2013-12-13 08:04:02.4200000','dasmith','2013-12-13 08:05:54.7700000','dasmith','2013-12-13 08:20:26.8400000','dasmith2','2013-12-13 08:47:57.5830000','dasmith2','2013-12-13 08:58:41.6230000','dasmith2','2013-12-13 09:11:53.9830000','dasmith2','2013-12-13 09:15:24.4300000','dasmith','2013-12-13 09:16:58.6000000','dasmith2','2013-12-13 09:26:20.1600000','dasmith2','2013-12-13 09:48:19.9300000','dasmith'
select @p1
GO
-- Repeat the following pattern
exec sp_execute 12,'2013-12-13 09:49:48.2830000','dasmith2','2013-12-13 10:04:35.8370000','dasmith2','2013-12-13 10:19:46.5100000','dasmith2','2013-12-13 10:21:26.4570000','dasmith','2013-12-13 10:22:16.1170000','dasmith','2013-12-13 10:26:01.4470000','dasmith','2013-12-13 10:27:08.5300000','dasmith','2013-12-13 10:29:18.4730000','dasmith','2013-12-13 10:31:26.5200000','dasmith2','2013-12-13 10:31:35.9200000','dasmith'

Code generated by Access for symptom 2 (please note only the POSTED column was to be updated):

exec sp_executesql N'UPDATE "dbo"."BATCH" SET "POSTED"=@P1  WHERE "BATCH_NO" = @P2 AND "BATCH_DATE" = @P3 AND "USER_ID" = @P4 AND "DEPOSIT" = @P5 AND "POSTED" = @P6 AND "TYPE" = @P7 AND "MasterTag" IS NULL AND "AutoBanked" = @P8',N'@P1 bit,@P2 int,@P3 datetime2,@P4 varchar(25),@P5 bit,@P6 bit,@P7 varchar(4),@P8 bit',0,91657,'2010-11-10 08:50:28.6570000','jadoe',0,1,'W',0


texts: I am looking for a query to determine when the snapshot has been finished applying on the subscriber

texts:
Is there any way to determine with a query when the replication has finished applying the snapshot on the subscriber?
I have a transnational pull replication set up for reporting purposes and it also includes the non-clustered indexes. Both servers are SQL Server 2016. When I set it up manually I can see in the replication monitor when the snapshot is getting created, delivered and applied (all data copied and the non-clustered indexes are created). After it is getting applied and I have no more undistributed commands, I start some SQL Agent jobs that will make some adjustments to the non-clustered indexes. I've automated the start of the replication and I would like to automate also the start of the SQL Agent jobs, but I cannot find any way to query this.
Normally, when SQL Server will show something in a GUI, there is an underlying system table or system stored procedure that will contain or returns that same information. For instance for the number of undistributed commands I can use this: sp_replmonitorsubscriptionpendingcmds when scripting, but for whether the snapshot is still getting applied on the subscriber side or it is completed, I cannot find anything and it is driving me crazy, I've been searching for days, so at this point any kind of helped is welcomed.
I've already looked into scripting out the actions of the SQL Agent jobs and including them as a post-script in the replication with sp_addscriptexec, but due to internal rules and way of working I cannot change them.


texts: What is a valid use case for using TIME WITH TIME ZONE?

texts:
Along the lines of this related question:

What is a valid use case for using TIMESTAMP WITHOUT TIME ZONE

Are there any valid use cases for actually using  TIME WITH TIME ZONE or should it be considered an anti-pattern?

To be clear: I am asking about TIME…, not TIMESTAMP….


texts: Alter column opreation failed due to disk space in SQL Server

texts:
I am attempting to alter a column on a specific table; however the process is failing because the log file runs out of space once the disk runs out of space.

The size of the table is less than the amount of free disk space.

Can anyone tell me what I'm doing wrong?

Scenario :

Alter column size from numeric(9,10) to numeric(18,6)

Statistics :

Table size - 40 GB
Table rows - 40 million
Disk free space where this table located - 50 GB
Database Recovery model : Full
Mirroring is configured
SQL Server 2012 Enterprise

The error I'm receiving is:

  Alter column operation is failed with an error "The statement has been terminated. The transaction log for database 'x' is full due to 'LOG_BACKUP'


texts:
I have a table, lets call it mytable:

CREATE TABLE mytable {
    id INTEGER,
    ts TIMESTAMP WITHOUT TIME ZONE,
    ..
)

and I have following query in multiple client applications:

SELECT ... FROM mytable ... ORDER BY (ts, id) DESC;

The query is very slow, because of (ts, id) DESC ordering, which results in:

...
Sort Key: (ROW(t.ts, t.id))
Sort Method: top-N heapsort  Memory: 51kB
...

in EXPLAIN ANALYZE VERBOSE ..

When I remove the parentheses in ORDER BY and use .. ORDER BY ts DESC, id DESC; in the query, and add CREATE INDEX idx_mytable ON mytable(ts, id); it uses the index and it's super fast.

However - as I've said, the original query is in multiple clients and I would rather add one index than change client application and update all clients.

So I would like to create ROW VALUE index - something like:

CREATE INDEX idx_mytable ON mytable(ROW(ts, id));

but this is not a valid syntax.

Is there a way to create ROW VALUE index which would be used in query with ..ORDER BY (ts, id) DESC ?


texts: How can I take backup of particular tables in SQL Server 2008 using T-SQL Script

texts: I want to take a backup of particular tables available in my database in a .bak file, and all these should be done using a T-SQL script.


texts: Joining two tables based on two Aggregate columns

texts:
I have a following problem as in the example. I have to join two tables based on the aggregated columns. These are the only way to combine these two tables. Is it possible to do this? I have tried Having by - procedures, without any success.
Select a.customerID,
sum(a.Items) as Amount a
sum(b.Items) as Amount b
From Orders a

Join Table B
   on SUM(a.Items) = SUM(b.Items)
Group By a.CustomerID


texts: Really poor performance when using subquery to get one value

texts:
Hi I have a table defined like below (Its actually lots of partition tables)
CREATE TABLE sensor_values
(
  ts             TIMESTAMPTZ(35, 6)                    NOT NULL,
  value          FLOAT8(17, 17) DEFAULT 'NaN' :: REAL  NOT NULL,
  sensor_id      INT4(10)                              NOT NULL,
  status         TRIDIUM_STATUS(max) DEFAULT 'unknown' NOT NULL,
  original_value FLOAT8(17, 17)
);
CREATE UNIQUE INDEX sensor_values_sensor_id_timestamp_index
  ON sensor_values (sensor_id, ts);
CREATE INDEX sensor_values_sensor_id_timestamp_value_index
  ON sensor_values (sensor_id, ts, value);

I am try to get the last days values grouped by sensor_id and day
SELECT sensor_id, ts::date, MIN(ts), MAX(ts), MIN(value), MAX(value) FROM sensor_values
  WHERE ts >= '2021-03-22 00:00:00+00:00' GROUP BY 1, 2;

Works acceptable. I was hoping for better . I am guessing I should have a partial index on the day to speed things up. The explain analyze is shown at
https://explain.depesz.com/s/jYsM
If I add a sub query to get the day to start selecting the time is much worst
SELECT sensor_id, ts::date, MIN(ts), MAX(ts), MIN(value), MAX(value) FROM sensor_values
  WHERE ts >= (SELECT MAX(day) + INTERVAL '1 days' FROM sensor_values_days_private LIMIT 1) GROUP BY 1, 2;

https://explain.depesz.com/s/1o4I
Is the way I can make postgres understand that the subquery returns an unchanging constant and
make it equal in time taken to the first query ?
Am I right in thinking I need a partial index for the datetime column ?
Thanks


texts: How to troubleshoot "Encryption not supported on the client OLE DB provider "SQLNCLI11" for linked server" error

texts:
We are using LocalDb for integration/unit testing of a .NET Core application. I have been using this solution for months and recently started getting sporadic errors. It seems to happen the first time the tests try to execute a linked server query. The linked server query is hitting another database on the same LocalDb instance on my local machine, i.e. there is not really a remote connection happening.  I am at a loss of how else to troubleshoot.
To set up the linked server connection, we are doing a simple SP call, nothing has changed with this from when it was working consistently.
EXEC sp_addlinkedserver @server='{ServerName}'

Encryption not supported on the client

.
    Test{redacted} Aborted: Exit code is -532462766 (Unhandled exception. System.Data.SqlClient.SqlException (0x80131904): Encryption not supported on the client.
OLE DB provider "SQLNCLI11" for linked server "(localdb)\MSSQLLocalDB" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
OLE DB provider "SQLNCLI11" for linked server "(localdb)\MSSQLLocalDB" returned message "Client unable to establish connection".
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite, String methodName)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
{redacted}

A similar stack track with an SSL credentials error

Exception Info: System.Data.SqlClient.SqlException (0x80131904): SSL Provider: No credentials are available in the security package


texts:
I have a script that loops over records of people (~4 million) and executes multiple updates (~100) and a single delete statement (all of these updates and delete are on different tables).  The problem I am facing is that the one delete statement takes about half the run time by itself.  I understand that when you execute a delete statement, it needs to update the index, but I find it rather ridiculous. I am currently testing this script with one thread using dbms_parallel_execute but I plan to multithread this script.

I am executing a query similar to the following:

DELETE FROM table1 t1
WHERE (t1.key1, t1.key2) IN (SELECT t2.key1, t2.key2
                               FROM table2 t2
                              WHERE t2.parm1 = 1234
                                AND t2.parm2 = 5678).

Following facts:

Table2 (~30 million records) is ~10 times larger than table1 (~3 million records).
There is a primary key on table1(key1, key2)
There is a primary key on table2(key1, key2)
There is an index on table2(parm1, parm2)
I have disabled the foreign key constraint on table1(key1, key2) that references table2(key1, key2)
There are no other constraints on table1, but many more constraints on table2.
All triggers on table1 have been disabled
The explain plan for this query comes up with a cost lower than that of many of my update statements (but I know this doesn't account for much).

Explain plan output:

DELETE STATEMENT                    6
DELETE
NESTED LOOPS                        6
NESTED LOOPS                        6
TABLE ACCESS        BY INDEX ROWID  4
INDEX               RANGE SCAN      3
INDEX               UNIQUE SCAN     1
TABLE ACCESS        BY INDEX ROWID  2

I was wondering if there were any way to make this delete go faster.  I tried to do a bulk delete but it didn't seem to improve the run time.  If there were any way to execute all the deletes and then update the index after, I suspect it would run faster.  Obviously doing a create table from a select is out of the picture since I am looping over records (and running through multiple conditions) from another table to do the delete.


texts: What is search space when it comes to query optimization?

texts:
I am quite new to learning about databases and I have come across the term "search space" in the context of query optimization. I am a little unsure of what the term means and why it is important? I have tried to search the web but haven't found any easy to understand answers.
Thanks in advance!


texts: Open .mdf and .ldf files in Microsoft SQL Server without attach

texts:
I want to open .mdf and .ldf files in Microsoft SQL Server without attach.

Are the following steps correct?

Add new database with MYDBNAME name.
Copy the files MYDBNAME.mdf / MYDBNAME_log.ldf to this path C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA (make sure to stop SQL services (SQL Server & SQL Server Agent))
Start SQL services again.

I tried these steps put after creating a new database, MYDBNAME.mdf and MYDBNAME_log.ldf created in DATA folder automatically. (I want to copy these file to DATA folder manually)

How can I add new database MYDBNAME without creating MYDBNAME.mdf and MYDBNAME_log.ldf files in DATA folder?


texts: How to apply ORDER BY and LIMIT in combination with an aggregate function?

texts:
A fiddle for my question can be found on https://dbfiddle.uk/?rdbms=postgres_10&fiddle=3cd9335fa07565960c1837aa65143685.

I have a simple table layout:

class
person: belongs to a class

I want to select all classes, and for each class, I want the first two person identifiers of the belonging persons sorted by descending name.

I solved this with the following query:

select     c.identifier, array_agg(p.identifier order by p.name desc) as persons
from       class as c
left join lateral (
             select   p.identifier, p.name
             from     person as p
             where    p.class_identifier = c.identifier
             order by p.name desc
             limit    2
           ) as p
on         true
group by   c.identifier
order by   c.identifier

Note: I could have used a correlation subquery in the SELECT clause, but I am trying to avoid that as part of a learning process.

As you can see, I am applying order by p.name desc in two places:

in the subquery
in the aggregate function

Is there a way to avoid that? My train of tought:

First, obviously I cannot remove the order by in the subquery, as that would give a query which does not meet my requirement as stated above.
Second, I think that the order by in the aggregate function cannot be left out, as row order of the subquery is not necessarily preserved in the aggregate function?

Should I rewrite the query?


texts: Converting a large SQL Server database to MySQL. Database corruption? Can't access data after transferring

texts:
I have gigabytes of tabular data stored in a SQL Server database. I've been trying to migrate it to MySQL using the Migration Toolkit from Oracle. I didn't have any mapping errors and fixed all the syntax mismatches, so now the rows are being transferred. But when I go to PHPMyAdmin and try to browse, it keeps saying MySQL returned an empty result set (i.e. zero rows). ( Query took 17.0116 sec ) although it says there are 44,546,089 rows in that table, and I can see the hard drive space filling up too. I just can't access any of the data.

I researched a bit and increased the buffer size from 16M to 160M in my.conf. But I can't fix this problem.

Some things I noticed (red marked in the status page):

Innodb_buffer_pool_pages_dirty: 376

Innodb_buffer_pool_reads = 92 k

Handler_read_rnd = 41

Handler_read_rnd_next = 59 k

Any advice? This database will be read-only. Also the server specs:  dedicated, 8GB RAM, Debian 6, 500GB HDD, ~2.8GHz Quadcore Intel


texts: What is the difference between integrity and consistency?

texts:
Whether I read about CAP or ACID, I see that consistency is referred to ensure the DB integrity constraints. So, I do not understand, why two terms are used to refer the same thing or there is a difference between the integrity and consistency?

I read that

  Anyhow, Atomic, Consistent, Isolated, Durable are properties of the transactions. It is true that atomicity + isolation is enough for you to roll-your-own consistency. But we can also roll our own atomicity, roll our own isolation, and roll our own persistence (durability). When we roll our own, we must pay for features with our own blood, sweat, and parentheses. We don't say the properties were given to us by a transaction system.

which suggests that consistency is what user application can provide himself on top of database integrity constraints. This is not property that is provided by database, as AID properties are. Why to give C the title as you do to the other, system-provided AID properties?


texts: SQL Server 2008 R2 Express Silent Install - FullText Service Not working

texts:
I'm trying to build an installer for an app that installs SQL Server 2008 R2 ADV with fulltext already enabled, but couldn't sort it out.

It must support Windows XP, so that's the first OS I'm testing. It already has all the prerequisites installed.

The command I'm using to install is the following:

SQLEXPRADV_x86_ENU.exe /QUIETSIMPLE /SkipRules=RebootRequiredCheck
    /ACTION=install /IAcceptSQLServerLicenseTerms=True
    /FEATURES=SQL,BC,SSMS,FULLTEXT /INSTANCENAME=NAMED
    /SQLSVCACCOUNT="NT AUTHORITY\NetworkService"
    /AGTSVCACCOUNT="NT AUTHORITY\NetworkService"
    /FTSVCACCOUNT="NT AUTHORITY\NetworkService"
    /SECURITYMODE=SQL /SAPWD="SomePass"

The result of the installation is that the service for the instance (MSSQLFDLauncher$NAMED) is DISABLED and the account used is NT AUTHORITY\LocalService, which is strange because that's not the account I passed, and the service is disabled.

Is there a way to call the installer so it just RUNS with the fulltext? If I need to change the accounts for the services (I only need SQL and FULLTEXT), that doesn't require me to create accounts, I can do so, but I really need it to work.

The documentation is poor, and I've tested all the combinations I found on the web.

Really appreciate the help!


texts: My transaction's max time is going beyond a minute

texts: I have a transaction called "updatePolicyStatus" whose response time is usually under a 3 seconds for many instances during a peak load. This transaction processes bunch of business rules and fires big set of SQLs to database. But  during a peak load, 10% of the total transactions response time is going beyond a 60 seconds. When I look at individual queries they are fine. I am not quiet sure what else is causing the transaction to become slow once in a while? My database I/O, network, CPU and memory everything is fine tuned. Could you please let me know what else could be the bottleneck? My database is Oracle 11g and it has 100 Gigs of data.


texts:
I am in need of getting data from multiple select queries. So I thought of creating two views and to inner join them to get my data.

My questions is: which option is better

creating a view,
creating temporary table


texts: Simple recursive CTE bombs out when changing operator in the WHERE clause

texts:
As the title states, I have a recursive CTE that bombs out when I change the operators in the WHERE clause, even if there are only two rows of data.

CREATE TABLE #Recursion
(Parent varchar(10), Child varchar(10), TopDate datetime)

INSERT INTO #Recursion (Parent, Child, TopDate)
VALUES
('00003137', '00003137', '2018-08-31'),
('04536347', '00003137', '2017-02-28'),
('05458040', '05458040', '9999-12-31'),
('00269705',' 05458040',' 9999-12-31')

;WITH
Parent AS
  ( SELECT parent
     , child
     , 1 as sort
     , TopDate
 FROM #Recursion
WHERE parent = child
 UNION ALL
   SELECT s.parent
        , d.child
        , d.sort + 1
        , s.TopDate
 FROM Parent as d
 JOIN #Recursion s
   ON s.child= d.parent
WHERE s.TopDate < d.TopDate
)

  SELECT parent
       , child
       , sort
    FROM Parent
ORDER BY parent, sort desc
OPTION (maxrecursion 0);

DROP TABLE #Recursion

If I leave the where clause as such, it works

WHERE s.TopDate < d.TopDate

As soon as I change it to this, it bombs and will not complete

WHERE s.TopDate <= d.TopDate

I do need to run some testing if the dates are the less than or equal. How come this wont work, even with only two rows of data?

Here is some example data. Note how the date field can be identical

parent      child       sort    TopDate
00003137    04536347    2       2017-02-28
00003137    00003137    1       2018-08-31
00269705    00269705    2       9999-12-31
00269705    05458040    1       9999-12-31

I can get this to work on all data except data that has identical TopDates


texts: can't connect to pgbouncer administration database

texts:
I installed pgbouncer and I setup the pgbouncer.ini and my application can connect to pgbouncer and make queries.

But when I try to connect to the virtual administration database of pgbouncer by using the following command

psql -p 6543 -U postgres pgbouncer

I get the following message

psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.6543"?


texts:
I have a view in my database. problem is below

Error
SQL query:

SELECT *
FROM `lumiin_crm_prod`.`v_contact`
LIMIT 1 ;

MySQL said:

  1449 - The user specified as a definer ('lumicrm'@'%') does not exist

i Google for a solution

User is created for Host & not for Global.

How to create the User for Global ????


texts: Is the Memory engine adequate for a read intensive/lookup table?

texts:
I have an InnoDB table with just two columns, a VARCHAR(20) and a DATETIME, and around 4M rows. This table serves as a blacklist for other tables, and is truncated and recreated from a csv file from time to time. There are no writes involved besides that, it's only used for a SELECT checking if a key exists, which always hits the index for the VARCHAR column.

The problem is, this table has to be consulted for every single operation in all my systems, all the time, because if there's a match, which is very rare, the operation must be aborted immediately. From my application profiling we spend around 10% of the database time reading from it.

I'm considering using the Memory engine for this table. The idea is to create a base table with the CSV engine that just loads the csv file instead of the whole data importing operation, and a init script to populate the Memory table. I'm assuming the HASH index on the VARCHAR column would be faster for simple lookups, but I'm not sure if it performs well with the almost 100% miss ratio I have.

Is this a good idea to improve my lookup speed?


texts:
When using sp_add_jobstep, are there any limits on @command when I choose @subsystem to 'TSQL', say, maybe I can only set @command to some simple, single tsql like:

'select * from o2o.dbo.sth'

Or could it be complicated like this?:

use hxo2o
go
begin
  declare @last_sync_date_updater nvarchar(2000)
  declare @tb_name nvarchar(200)
  declare tb_name_cur cursor for
    select table_name from dbo.o2o_tables

  open tb_name_cur
  fetch next from tb_name_cur into @tb_name

  while @@fetch_status = 0
  begin
    set @last_sync_date_updater =
        'update dbo.o2o_tables set table_last_sync_date = (select '
        + case
        when @tb_name = 'trace_user_actions' then 'max(operation_date_time)'
        else ' dbo.InlineMax(max(create_time), max(update_time)) ' end
        + 'from dbo.' + @tb_name + ')'

    exec (@last_sync_date_updater)

    fetch next from tb_name_cur into
    @tb_name
  end
close tb_name_cur
deallocate tb_name_cur
end

If not, should I use another @subsystem instead? Or some other methods? I tried to google and stack for the issue but labor in vain.


texts:
I know how to JOIN two tables as create a new table out of the results, using CREATE TABLE result_table AS ( SELECT * FROM tableA JOIN tableB ).

I forgot to include some columns from the JOIN in the result_table. Is it possible to merge some of columns from tableA into result_table? Is there a performance improvement over simply dropping and re-creating result_table again?


texts: Finding the names and the year of the movies that have at least 5 actors

texts:
Given database of the website "eMovies" that manage data about cinema movies.

  Write in SQL query that finds the names and the year of the movies that have at least 5 actors.

My attempt:

The idea: to do a natural joint between "Movies" and "ActorsMovies" tables, and then to count the rows with the same MovieID, and then to check if count>5

I succeded to count the rows thank's to @Phil's advice, but still I need the name of the movies and the year,

Here is my start:

SELECT DISTINCT Year, MovieName
FROM Movies, ActrorsMovies
SELECT Count(ActorsMovies.MovieID)
FROM ActorsMovies
GROUP BY ActorsMovies.MovieID
HAVING (((Count(ActorsMovies.ActorID))>5));

This code does not work

EDIT: another attempt

 SELECT DISTINCT Movies.Year, Movies.MovieName
 FROM Movies, ActorsMovies
 GROUP BY ActorsMovies.MovieID
 HAVING Count(ActorsMovies.ActorID)>5;


texts: Recovering MySQL 5.7 tables in MariaDB 10

texts:
I did a very foolish thing... I did not realize installing mariaDB would uninstall MYSQL 5.7 using apt-get. (Using Ubuntu 16.04).

So now I am trying to recover my data.

So far, I have read many guides and tried several things.  I think this has gotten me into bad position, because the advice seems to be very version specific.

My most successful attempt is 1) Fresh install of MaraiDB. 2) copy * files from old sql directory to new. 3) Copy database directory myDatabase/ from old sql directory to new. 3) Run mysql_secure_installation 4) Configure my users, etc. etc.

This works except that I cannot run sql queries.

SELECT * FROM myDatabase.tableOne;

I get either:

SQL Error [1932] [42S02]: (conn:56) Table 'myDatabase.tableOne' doesn't exist in engine

OR

SQL Error [1146] [42S02]: (conn:56) Table 'myDatabase.tableTwos'

It seems that all tables give one of these two errors.

So far, I have read that mysqlcheck can fix this.

If I run

/var/lib/mysql$ mysqlcheck myDatabase
myDatabase.tableOne
Error    : Table 'myDatabase.tableOne' doesn't exist in engine
status   : Operation failed

I can't find any suggested solutions for fixing this. Although perhaps I went the wrong way from the beginning. Thanks for your help.


texts:
I have a large amount of data on users (about 8 million users).

I want to mine this data to create a table with statistics about first names. It should also include as many variations of each name as possible. The variations of first names ("John" and "Johny") should be recognized and treated properly.

The purpose of this table is to quickly find variations for a name and select one, in order to use user's non-standard name when contacting him. The name to be searched for can be his standard/ official ("John") or any variation of it ("Jack, Jackie, Jacky").

So far, I came up with following table structure:

table name: first_name_variations
+---------+----------+-------------+-------+
|  name   | group_id | is_standard | count |
+---------+----------+-------------+-------+
| Jack    |        1 |           1 |    53 |
| Jackie  |        1 |           0 |     5 |
| Francis |        2 |           1 |    32 |
| Frank   |        2 |           0 |    52 |
+---------+----------+-------------+-------+

The "name" column should be primary index. Standard/ official names are to be marked with "is_standard". The variations of one name must be assigned the same group_id.

Should I include surrogate key (e.g. id)? Will it improve something?

Should I create a separate table for name groups, or maybe just marking names with group_id would be sufficient?

Since my database knowledge is rather shallow, can you please propose improvements to his approach.

The database is MySQL

UPD: I think I'll remove 'is_standard' column; it's an overkill.


texts: Introducing FOREIGN KEY constraint 'FK_X_Y' on table 'X' may cause cycles or multiple cascade paths

texts:
So while publishing my database scripts in Visual Studio, I get this exact error: Introducing FOREIGN KEY constraint 'FK_Users_Manager' on table 'Users' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

This is my simplified script for creating a self referencing Users table:

CREATE TABLE [dbo].[Users]
(
    [UserId] INT NOT NULL IDENTITY,
    [ManagerId] INT NULL,
    [Data] NVARCHAR(1024) NULL,
    CONSTRAINT [PK_Users_UserID] PRIMARY KEY ([UserId] ASC),
    CONSTRAINT [FK_Users_Manager] FOREIGN KEY ([ManagerId]) REFERENCES [Users] ([UserId]) ON DELETE SET NULL
)

Even if somehow I made a cycle on these relationships, I don't see why wouldn't deleted row's direct children have their ManagerId set to NULL?

Should I create a trigger for this scenario to set NULLs for me instead? Or is there something I am clearly missing?


texts:
Coming from a MySQL background, where stored procedure performance (older article) and usability are questionable, I am evaluating PostgreSQL for a new product for my company.

One of the things I would like to do is move some of the application logic into stored procedures, so I'm here asking for DOs and DON'Ts (best practices) on using functions in PostgreSQL (9.0), specifically regarding performance pitfalls.


texts:
I have a table in my database with few simple columns and one binary column. The primary key of that table is an integer type.

The problem I am facing is that auto-incremented primary key column had hit its maximum limit +2147483647. Means that table have around 2 billion records in it. Due to binary column in that table, it have around 255 GB size on disk.

Now all insertion to that table had stopped due to that data type limit. Logical solution is that we covert that primary key column to bigint. But due to huge size of the ALTER TABLE command always get failed and some times even database put into inconsistent state, where we can't access that table at all.

Statement used:

ALTER TABLE table_name ALTER COLUMN id SET DATA TYPE bigint ;

Any one have any suggestions to solve this issue.


texts: How to log into a fresh MariaDB install?

texts:
I am usually using Postgres, now I have had to install MySQL/MariaDB to help a friend with some issues.

The problem is just - I am not able to log into the freshly installed database on Debian 9.

When searching for default user name, I am told to use "root" with a blank password. But no matter what I have tried, I am just greeted with Access denied.

mysql -u root
   mysql -u root -p

and then repeat with root@localhost and root@127.0.0.1. I have tried to use the description in the accepted answer of https://superuser.com/questions/603026/mysql-how-to-fix-access-denied-for-user-rootlocalhost but to no avail.

Where am I messing up? Are there any command line utilities similar to Postgres' createuser to create a new user?

mysqld 10.1.26-MariaDB-0+deb9u1 on debian stretch (9.3)


texts: Is it necessary to archive master WAL files in order to implement streaming replication in postgresql-11?

texts:
The documentation at:

https://www.postgresql.org/docs/11/warm-standby.html#STREAMING-REPLICATION

Starts out by saying that:

"Streaming replication allows a standby server to stay more up-to-date than is possible with file-based log shipping. The standby connects to the primary, which streams WAL records to the standby as they're generated, without waiting for the WAL file to be filled."

Which sounds a lot like streaming replication is a replacement for WAL logfile-shipping. But a little later the instructions at 26.2.5 say:

"To use streaming replication, set up a file-based log-shipping standby server as described in Section 26.2. The step that turns a file-based log-shipping standby into streaming replication standby is setting primary_conninfo setting in the recovery.conf file to point to the primary server."

This sounds like a streaming-replication secondary must also be a logfile-shipping secondary, which seems redundant. I can understand that it's possible for the secondary to fall behind in replication so far that the master may delete WAL segments from it's xlog directory before they are replicated, and that is an argument for a WAL archive. But the documentation says that a "replication slot", apparently an indicator of the last WAL file processed by the secondary, will accomplish the same goal, I guess by telling the master to hold on to that and subsequent segments. At 26.2.5:

"If you use streaming replication without file-based continuous archiving, the server might recycle old WAL segments before the standby has received them. If this occurs, the standby will need to be reinitialized from a new base backup. You can avoid this by setting wal_keep_segments to a value large enough to ensure that WAL segments are not recycled too early, or by configuring a replication slot for the standby."

I'm in the midst of experiments, but any explanation would be appreciated.


texts: What is the size of currently-existing tmp tables in innodb?

texts:
This is yet another "how to add a column to a gigantic table" question. The table on disk takes 2.5GB, and I have around 10GB of ram I can use. I have a feeling I can crank up the tmptablesize option so my add column tmptable resides completely in memory so disk i/o isn't a bottleneck. How can I get a sense of what value I should set tmptablesize to?

I have a second vm provisioned for a dry run. I'm running my alter table command and it is chugging along, writing to a temp table. How can I tell how large this temp table is, exactly?

edit for comments

mysql --version
mysql  Ver 15.1 Distrib 5.5.30-MariaDB, for debian-linux-gnu (x86_64) using readline 5.1

The table has 5.5 million rows: 10 decimal columns, 17 integer columns, and 2 datetime columns. No foreign keys. 12 of the integer columns have indexes.

The command:

alter table anonymized_table_name ADD COLUMN `anonymized_column_name` VARCHAR(100) NULL;


texts: oracle 12c enterprise manager default login

texts:
I am just installed oracle database in my windows machine after installation it i can't able to login i dont have username only i know password . can any body help on this

advance thanks


texts: What are possible ways of handling multiple users accessing the same database via custom web API?

texts:
I am very new to databases and Web design, but I understand enough to know that designing a good database is crucial, so I would like to prepare for that conceptually. However, I was unable to find any answers on my level on the web/in tutorials.

I am working towards a developing a database (MariaDB, to be exact) that will be accessed by my website (written in Django). I want the website to serve as an API for several people to work on the database. Naturally, I want to set some use- or group-specific privileges.

What are the possible ways of doing that, and what are their upsides and downsides?

I can only think of two generals approaches:

Connecting the website to the database as root, and letting the website API limit user capacity to edit certain tables.
Using some sort of server-side user system directly in a web API.

This is important to me both in the context of limited access to certain tables within a database and syncing multiple people editing the same table. Is there an approach generally considered as a good practice?


texts: Optimize a UNIQUE CONSTRAINT to make SELECT faster

texts:
I have a database on SQL Server 2012 SP3 with this table:

CREATE TABLE [dbo].[Code] (
    [CodeId]            INT            IDENTITY (1, 1) NOT NULL,
    [Serial]            NVARCHAR (20)  NOT NULL,
    [AggregationLevelId]  TINYINT      NOT NULL,
    [...],
    CONSTRAINT [PK_CODE] PRIMARY KEY CLUSTERED ([CodeId] ASC),
    CONSTRAINT [UC_CODE_SERIAL] UNIQUE NONCLUSTERED ([Serial] ASC)
)

Using Sql Server Management Studio Activity Monitor I have seen that the following select has a 32ms average duration.

set @maxCode = (select max(Serial) from Code where AggregationLevelId = @codeLevel);

Is there any way to improve it? I asked this because I don't know if I can add a new index to a column that has a Unique Constraint.

UPDATE:

Code table has also this index:

CREATE NONCLUSTERED INDEX [ix255] ON [dbo].[Code]
(
    [AggregationLevelId] ASC
)
INCLUDE (   [Serial],
    [CodeId])
WHERE ([CommissioningFlag]=(255))


texts: When REBUILDing indexes on SQL Server, what bearing does tempdb & LOG disk speed have?

texts:
Say I have the a data disk that is 50x faster than the LOG and tempdb (measured by Random Write speed) disk. (Don't ask why that's something we'll be fixing if needed)

I have a table that's got 19 million rows with about 30 indexes (so basically non trivial).

How much of a factor would the disk speed of the LOG & tempdb than if I was to have the same disk speeds as the data disk.

Edit:
Some more stats.

We have moved the LOG to the RAID data drive as test and unfortunately there was not improvement in the speed of the the REBUILD. It still too 1 hour.

tempdb is on it's own Raid10 array
LOG    is on it's own Raid10 array
Data   is on it's own Raid10 array
RAM > 100 Gb (yes 100Gb) and no limit on SQL using it for data or index creation

Note this is not a transactional system, rather a Data Warehouse.


texts: Find tables and views that are not in use at all?

texts:
I did not find any similar question here, so please direct me if I'm wrong.

I have a large database (inherited from former employee) and I need to clean up that database.

How do I find all tables and views which are not used at all in database?

I found a query at SQL Server script to know when the table accessed last time (CALSQL | SQL Server Community Blog):

with cte_recent as
(
select SCHEMA_NAME(B.schema_id) +'.'+object_name(b.object_id) as tbl_name,
(select MAX(last_user_dt) from (values (last_user_seek),(last_user_scan),(last_user_lookup)) as all_val(last_user_dt)) as access_datetime FROM sys.dm_db_index_usage_stats a
right outer join sys.tables b on a.object_id =  b.object_id
)
select tbl_name,max(access_datetime) as recent_datetime  from cte_recent
group by tbl_name
order by recent_datetime desc , 1


texts:
I am struggling to extract data from an XML file. The file for all intense and purposes looks like this:

<Message>
  Hello
  bye
</Message>

At the moment I am using the Value function.

However the data I get returned is only ever "Hello". How do I return all data in a tabular format, like so:


texts: Missing commands in MySQL command line history

texts:
Why aren't all command that are entered in the MySQL command line saved in the command history?

For example, entering the commands

DROP DATABASE mydb;
CREATE DATABASE mydb DEFAULT CHARACTER SET utf8;
GRANT ALL ON mydb.* TO username@host IDENTIFIED BY 'password';

and then trying to go through the history by pressing   ▲   I only see the first two commands.

I'm running MySQL Ver 14.14 Distrib 5.7.17, for osx10.12 (x86_64) installed with Homebrew


texts: How pass a local variable in Json path during data extraction using Json_value function in sql sever select statement

texts:
My Query is

declare @id INT
set @id = 69
select
   (JSON_VALUE(cast(ad.data_map as varchar(max)),'$.dataMap."69".value')) CorrespondanceNumber,
   (JSON_VALUE(cast(ad.data_map as varchar(max)),'$.dataMap.***"@id"***.value')) Agency,
   (JSON_VALUE(cast(ad.data_map as varchar(max)),'$.dataMap."69".value')) Protocal
from actions_new a
  left join action_data ad on ad.id_ref = a.id
where baid = 12;


texts: SQL Server 2014 CTP 2014 expiration date

texts: I installed SQL Server 2014 CTP2 on a server and I have been testing the new features in SQL Server 2014. On the web site where I down loaded the software I notice the statement "expires on March 31, 2014". Can you explain what this means? Will we still be able to test SQL Server 2014 CTP2 after that date or does the SQL Server engine stop working?


texts: Restart ORACLE OHAS without restarting databases

texts:
looks like ohasd is getting to much memory (memory leak or something) that cause heavy swapping, is it possible to restart OHAS (till patch is applied) without bouncing databases?
"crsctl stop has" puts all resources down :(


texts: Why is our SQL Server instance writing a mystery text file to our C: drive?

texts:
On a dev-test WINTEL server, our C: drive has almost run out of disk space due to a SQL.LOG file that is 133 GB in size. I used SysInternals Handle.exe tool to determine which process was writing this SQL.LOG file and found that the culprit is sqlservr.exe.

As this SQL.LOG file does not appear to be any of the standard SQL Server log/error log files (they are all configured to write elsewhere), my question is: how can I query this SQL Server 2012 Enterprise instance to determine which job, stored procedure, assembly, etc is writing to this mysterious C:\SQL.LOG file..?

Note: the file itself doesn't contain any clues. It is too large to open in Wordpad but if I query for the first few lines in Powershell, nothing helpful appears...

So I need to find a way of asking SQL Server which bit of itself is writing this C:\SQL.Log file. I guess I need a way of searching within SQL Server for either configuration information (somewhere this filename and path must be set) or for details of file output operations (other than IO operations on database data/log files). Can anyone help me with drafting such a T-SQL query?


texts: How do I generate a pivoted CROSS JOIN where the resulting table definition is unknown?

texts:
Given two tables with an undefined row count with a name and value, how would I display a pivoted CROSS JOIN of a function over their values.

CREATE TEMP TABLE foo AS
SELECT x::text AS name, x::int
FROM generate_series(1,10) AS t(x);

CREATE TEMP TABLE bar AS
SELECT x::text AS name, x::int
FROM generate_series(1,5) AS t(x);

For example, if that function were multiplication, how would I generate a (multiplication) table like the one below,

All of those (arg1,arg2,result) rows can be generated with

SELECT foo.name AS arg1, bar.name AS arg2, foo.x*bar.x AS result
FROM foo
CROSS JOIN bar;

So this is only a question of presentation, I would like this to also work with a custom name -- a name that is not simply the argument CASTed to text but set in the table,

CREATE TEMP TABLE foo AS
SELECT chr(x+64) AS name, x::int
FROM generate_series(1,10) AS t(x);

CREATE TEMP TABLE bar AS
SELECT chr(x+72) AS name, x::int
FROM generate_series(1,5) AS t(x);

I think this would be easily do-able with a CROSSTAB capable of a dynamic return-type.

SELECT * FROM crosstab(
  '
    SELECT foo.x AS arg1, bar.x AS arg2, foo.x*bar.x
    FROM foo
    CROSS JOIN bar
  ', 'SELECT DISTINCT name FROM bar'
) AS **MAGIC**

But, without the **MAGIC**, I get

ERROR:  a column definition list is required for functions returning "record"
LINE 1: SELECT * FROM crosstab(

For reference, using the above examples with names this is something more like what tablefunc's crosstab() wants.

SELECT * FROM crosstab(
  '
    SELECT foo.x AS arg1, bar.x AS arg2, foo.x*bar.x
    FROM foo
    CROSS JOIN bar
  '
) AS t(row int, i int, j int, k int, l int, m int);

But, now we're back to making assumptions about the content and size of the bar table in our example. So if,

The tables are of undefined length,
Then the cross-join represents a cube of undefined dimension (because of above),
The catagory-names (cross-tab parlance) are in the table

What's the best we can do in PostgreSQL without a "column definition list" to generate that kind of presentation?


texts:
I am running SQL Server 2012 on windows azure VM Windows server 2012.

This is a live Database, about 250GB, with high usage.

I applied some best practice articles for the temp-db long ago, and also split my main database to its own physical drive for the data file and for the log file.

In the past couple of days i encountered extreme slow feeling in the VM (though the databases response seems fast and good).

The VM is running on an A7 instance (8 cores 56GB Ram) and from performance monitor it seems that the bottle neck is on the Hard drive, especially the one that is running the mdf file of the main database.

I researched the web a bit, and decided to create another file group (had only a primary and only the mdf),
I attached another 7 hard drives to the machine, so that i now have 4 files (hard drive for each..) for the primary group and 4 files (hard drive for each..) for a secondary group that i defined as default.
Afterwards i rebuild the indexes on most of the important tables (which caused me a downtime of about 15 minutes for one big important table...).

I expected that these actions would dramaticly effect the machine's slowness yet no dramatic change is felt, also the drives that are running the ndf's and the mdf's are still having pretty high Avg. disc sec/read write (0.4 avarage for read and 0.01-0.02 write)

Am i missing something ?

EDIT:

Ok,
So after about 24 hours from the changes i implemented it seems to be working well, the disk read/write seems better and the machine is running fast.

I honestly can't explain why it took about 24 hours yet i'm fine with the fact that it worked :)


texts: better format using nanodbc R on Windows

texts:
Using R-3.5 and odbc (which uses nanodbc), I connect via ODBC and the ODBC Driver 13 for SQL Server ODBC driver on windows 10.

DBI::dbGetQuery(odbccon, "select SYSDATETIMEOFFSET() as now")
#                                         now
# 1 2018-09-03 16:36:44 Pacific Standard Time

The same query with a native (non-ODBC) driver:

DBI::dbGetQuery(nativecon, "select SYSDATETIMEOFFSET() as now")
#                                  now
# 1 2018-09-03 19:37:13.9923643 -04:00

(Both are R's character type.)

This demonstrates a few things:

the ODBC driver does not recognize an object of type DATETIMEOFFSET, which an issue I've raised with odbc; this is not of itself a huge issue, but I think indicates why it is returned as a string (and not internally converted correctly, I think)
loss of millisecond precision (the biggest problem)
non-standard time zone indication (where I believe "standard" includes -07:00, -0700, +07, or even America/Los_Angeles)

Is there a way to prevent SQL Server or Windows ODBC from formatting (and losing) data thus? Is there a way to change this via a SQL Server command, an ODBC function, or something else? (The largest problem is omission of milliseconds.)

Edit: for fairness, having it come as a character vice a native POSIXt is not a big problem, that's easy enough to fix with a call to as.POSIXct; similarly, I can compensate for the timezone depiction using a lookup on something built from this "Zone -> Tzid" table. It's the loss-of-data thing I really need to mitigate, since my immediate use-case uses milli-second precision, and altering all processes to explicitly cast(...) is more than I'd like to do right now (and is not a general solution).

DBI::dbGetQuery(con, "select @@VERSION")
#
# 1 Microsoft SQL Server 2016 (RTM-GDR) (KB4019088) - 13.0.1742.0 (X64) \n\tJul  5 2017 23:41:17 \n\tCopyright (c) Microsoft Corporation\n\tStandard Edition (64-bit) on Windows Server 2016 Standard 6.3 <X64> (Build 14393: )\n


texts: Why don't HOST comands appear in the spool?

texts:
spooltest.sql:

SPOOL test.log
CLEAR SCREEN
SET VERIFY OFF
PROMPT Queries will appear in spool
select sys_context('USERENV', 'INSTANCE_NAME') from dual;
PROMPT Output from the HOST command will not
HOST ECHO %COMPUTERNAME%
SPOOL OFF

sqlplus output:

Queries will appear in spool

SYS_CONTEXT('USERENV','INSTANCE_NAME')
---------------------------------------------------

wibble

Output from the HOST command will not
AWESOMESERVERNAME

SQL>

test.log:

Queries will appear in spool

SYS_CONTEXT('USERENV','INSTANCE_NAME')
--------------------------------------------------------------------------------
wibble

Output from the HOST command will not

If it's not possible to capture HOST output, can someone please suggest a workaround.


texts: What kind of object is being checked against the cache when a new query arrives?

texts:
After reading several resources, it's not clear to me what kind of object is being checked against SQL Server Plan Cache when a new query arrives.

I see 3 possible options:

The pure query text.
The parse tree (the resultant object of the parser)
The query plan (the resultant object of the optimizer. Below, I refer to it as a "compiled query").

Now, since the cache is actually called "The Plan Cache", it may indicate that option 3 must be the correct one.

However, it seems to me that the third option must be false. First, because the "Plan Cache" actually caches other object than just compiled plans (see here). Secondly, as I see it, the purpose of accessing the cache is to prevent an unnecessary compilation of the arrived query. Thus, if the access to the cache is done against a compiled query, it requires the compilation of the new query, which misses the whole point.

I read one resource that supports this view, saying that the cache is being checked before the Query Optimizer takes place, i.e., before the generation of the query plan (taken from Professional SQL Server 2012 Internals and Troubleshooting, pg. 113, under the "Query Optimization" section):

  The current model provided by the SQL Server team works something like
  this:

  Is a valid plan cached? If yes, then use the cached plan. If no plan
  exists, then continue [and apply simplification and optimization,
  which results in a query plan].

So it seems option 2 is the right one. However, in two other resources I found a support for option 3. This is the first one (source):

  When a query is submitted to the server, an estimated execution plan
  is created by the optimizer. Once that plan is created, and before it
  gets passed to the storage engine, the optimizer compares this
  estimated plan to actual execution plans that already exist in the
  plan cache . If an actual plan is found that matches the estimated
  one, then the optimizer will reuse the existing plan, since it’s
  already been used before by the query engine.

And The following text (taken from Microsoft SQL Server 2012 Bible, pages 1042 and 1044) claims the same: The query plan is checked against the cache after its generation (Note that it uses slightly different terms and it differs from the above text regarding the component that is responsible for the request to check the cache):

  After the query execution plan generates [By the query optimizer], SQL Server stores it in the
  Plan Cache [...] and
  informs the Query Processor that the plan is ready to be executed.

  [...]

  When the query processor needs to execute a query, it first checks to
  see if the query can reuse an execution plan already in the Plan
  Cache. It’s much faster to find and reuse a Query Execution Plan than
  to generate a new plan, even though that option is not always the best
  solution.

Is there a decisive answer for this?


texts: Is an anomaly possible if a serializable transaction interfers with another isolation level?

texts:
Let's assume transaction A runs in SERIALIZABLE and transaction B runs in a more permissive isolation level, like READ UNCOMMITTED. Can Transaction A now suffer from any anomaly introduced by transaction B?

I found this SO question, but the answer doesn't give that information. I understand the referred manual page only for homogeneous scenarios:

  The guarantee that any set of concurrent serializable transactions will have the same effect as if they were run one at a time means that if you can demonstrate that a single transaction, as written, will do the right thing when run by itself, you can have confidence that it will do the right thing in any mix of serializable transactions

I found these references in the SQL draft indicating that SERIALIZABLE transactions will indeed not suffer from any anomaly induced by other isolation level transactions:

CD 9075-1:200x(E) page 27:

  The highest isolation level SERIALIZABLE, guarantees serializable execution, meaning that the effect of SQL-transactions that overlap
  in time is the same as the effect they would have had, had they not overlapped in time.

CD 9075-2:200x(E) page 124:

  Changes made to SQL-data or schemas by an SQL-transaction in an SQL-session may be perceived by that
  SQL-transaction in that same SQL-session, and by other SQL-transactions, or by that same SQL-transaction
  in other SQL-sessions, at isolation level READ UNCOMMITTED, but cannot be perceived by other SQL-
  transactions at isolation level READ COMMITTED, REPEATABLE READ, or SERIALIZABLE until the
  former SQL-transaction terminates with a .

That's the standard. But how do the implementations do in reality? I'm particularly interested in the defined behaviour of Postgres and MySQL.


texts: Is there a practical limit on the number of PostgreSQL TYPES?

texts:
We are using Postgresql 9.6  in an application where we are considering using the TYPES system to extend new object types

The system would start with 10-20 types and then users would have a UI to create new types, probably composite types.

Question - is there a practical limitation on the number of TYPES?

From a system design alternative perspective we could use MongoDB and JavaScript objects which would give us more expressiveness - it would also require us to support MongoDB which is not our core expertise or we could go in other directions like using F# type system but that is going astray from this question....

and we believe the PG types will give us better mileage....


texts: Migrating data in SQL Server from source to target with schema change at target

texts:
We have a strange scenario.

We have a source database running on SQL Server 2005 where we push data to a target database running on SQL Server 2008 R2.

The source database table has 5 columns.  The target database contains an extra column which is not nullable and has a DEFAULT value defined.

We need to push data for the 5 columns from source to target.  If we use any tool like red-gate or dbcompare, it will compare source with destination and provide a query to insert the new data on to the target, however running the query fails as it is not inserting anything into the newly introduced column which is not nullable.

Any idea on how to handle this?


texts:
I'm trying to get postgresql 9.4 and BDR working. Following this tutorial: https://wiki.postgresql.org/wiki/BDR_Quick_Start

Actually, my sysadmin provided me with a package to test with. He's taken care of the compilation "stuff".

I have two instances running, one on port 5598 and the other on 5599. What I'm noticing is that when I leave the following line in the postgresql.conf file, I can't start the service:

 # Load BDR
 shared_preload_libraries = 'bdr'

the error message i get is:

  FATAL:  could not access file "bdr": No such file or directory

When I remove the above line, it I can start the service on the specific port needed.
According to the BDR docs, if you get that message, you're missing the bdr contrib package:

https://wiki.postgresql.org/wiki/BDR_Reference#Could_not_access_file_.22bdr.22:_No_such_file_or_directory

But I have it installed. I just don't know how to verify it because I don't exactly know what it does.

When i check my package manager to see everything that's been installed, I have the following items listed:

postgresql-bdr
postgresql-bdr-contrib
postgresql-bdr-dbg

My apologies in advance for being such a noob. But i'm not a sysadmin, linux is relatively new to me... and it looks like BDR is pretty new too. I'm just trying to collect as much information as possible for my sysadmins to be able to fix whatever is wrong with this package...

Thanks.


texts: Select Query slow for Non-clustered index

texts:
I am using SQL Server 2008 R2. Select query is slow when non cluster index is used in the table  compared to table without index.
Query Used:
select * from table order by customer_name

It is  too slow (more than 1 minute for 40000 rows)
Error was due to non-clustered index , without index  query executed in 5 sec.
How did indexing caused  problem?
The query plan:
For without index is : https://www.brentozar.com/pastetheplan/?id=rJZtQfltv
For with index is : https://www.brentozar.com/pastetheplan/?id=Bkrg8zgKD
Queries with Different conditions are executed and results updated.
Query plan  for Query  with order by : https://www.brentozar.com/pastetheplan/?id=ByOxkbpYP
Query plan  for Query  without order by : https://www.brentozar.com/pastetheplan/?id=S1WB1bpYD
Summery :

Column [sl_no] is clustered index
Column [Customer_Name] is non-clustered index
For the queries column with large text used
The elapsed time for  :

With Order by Query1 - 12ms
With Order by  Query2- 1sec
Without Order by Query1-0ms
Without Order by Query2- 3sec
Query with order by with non clustered index on column -5 sec


texts:
I have installed SQL Server 2016 Enterprise and applied the license key but

SERVERPROPERTY('LicenseType') is disbaled and
SERVERPROPERTY('NumLicenses') is null

There is no key in the registery such as following for SQL Server 2016:

  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.SQL2016\Setup

Any idea how can I change LicenseType of SQL Server?


texts: InnoDB: does `redo log` contain initial value and final value? or just final value?

texts:
start transaction;
update users set age = 34 where user_id = 3 // update age from 30 to 34 years old.
....
....

when I am updating the age of this user, InnDB will write redo log.

What's the structure of redo log?
Does it contain initial value 30 and final value 34? or just contains new value 34?


texts: What are some important security-related error codes / events to monitor?

texts:
I am setting up alerts in SQL Server and am interested in what events I should be raising alerts for.

In particular, I am looking for any events related to security.

I am already monitoring error 18456.


texts: How to prevent asking for password when creating new database in PostgreSQL 10?

texts:
I use the following batch file to create a new database in PostgreSQL 10.

echo off
cd "C:\Program Files (x86)\PostgreSQL\10\bin"
createdb -h localhost -p 5432 -U postgres myDB

But it asks to enter the password. How to prevent asking for the password? I tried the -w option explained in this link to not ask for password prompt. But this did not work. How to prevent asking for password?


texts: How to use ts_headline with multiple languages

texts:
I am using full-text search against a single table column. The data in the column could be of any language with no way to determine the language ahead of time. Assuming I only care about some subset of possible languages (for example English and Spanish), I can easily generate a list of lexemes for the vector:

to_tsvector('english', my_column) || to_tsvector('spanish', my_column)

and for the query:

to_tsquery('english', 'something') || to_tsquery('spanish', 'something')

This works as expected.

However, I would also like to return the column value with the matches highlighted, but ts_headline accepts a single configuration, the document and the query as parameters. I can write something like:

ts_headline(
  'english',
  my_column,
  to_tsquery('english', 'amor') || to_tsquery('spanish', 'amor')
)

but this will only apply the highlighting based on the configuration passed to ts_headline -- so in the example above, the word "amoroso" will be included in the result, but will only be highlighted if I set the configuration to "spanish".

I could run ts_headline for each configuration and return the results as an array, but then I would still have to combine them after running the query, which is less than ideal.

Is there a way to apply the highlighting based on multiple languages?

EDIT: Alternatively, given a document, its lexemes and the query lexemes, is there a way to return the list of matched words in the document along with their locations in the document?


texts: Should I create a separate database table for temporary users

texts:
I have a user table that controls access to a website. We currently have a few hundred users and this might eventually grow to a few 10,000s. We have a requirement to allow in "temporary" users. These temporary users will have a token that times out, never to be used again. These tokens will likely outnumber the general users by a great degree. The question I have is whether these temporary users should be stored in the general table or in their own table.

My inclination is the same table as the userid is used elsewhere as foreign keys and is still useful for a temporary user. The uniqueness across the ids would be important. However, I'm not delighted that the user table will be filled with many records that never again need to be used and will thus slow down the table.

Another option I've considered is to create a user record, capture the id, delete the record and then use the id in another table. I therefore retain the uniqueness of the id but reduce the bloating of the table. I don't mind if the foreign keys reference different tables.

Anyone had a similar issue and have any thoughts?


texts:
I want to try out Cassandra Cluster. My main question is about scalability.

Every node in a Cassandra cluster has the same copy of data. So when I have in total 1 TB and have 5 nodes -> 5TB.

This at some point will get huge. How do I scale Cassandra so that the storage can be distributed? Do I need to shard manually then again?


texts: Why are my SQL Perfmon counters returning Zero?

texts:
I have set up a Perfmon data collector set on one of our servers with SQL server running on it. I also have a scheduled task which imports this data into SQL (set up by following this open source project: https://github.com/heraflux/BLGtoSQL ). All this runs with one user on the local machine.

After leaving this running for a couple of weeks I have created a report to look at the data gathered so far. All the Perfmon counters EXCEPT the SQL ones work. All the SQL counters return zero - even though I know at least some of these counters CAN'T always be zero (e.g. User Connections, Free Memory KB etc).

What's odd is that when I run and import the exact same setup on our development server the SQL counters work as desired. I've checked and the user has the same local permissions on each server.

I've also granted the user with the following permissions on the SQL server:
Connect Any Database, Connect SQL, View Server State.

When looking at the SQL counters live on Perfmon I've found that they also don't seem to be working. I'm pretty stumped as to why they aren't showing anything.


texts:
I am having trouble with the below, I need to average two columns in a correlated subquery and I cannot get it to work, how do i get both aggregate sub queries to run then go through the outer query? I am not even sure I am on the right path so any guidance appreciated.

Question: Display the ID, Name and average Exam and Coursework weights for all lecturers and make sure all the columns have suitable headings, using a correlated query. Investigate use of the ISNULL function to produce suitable output in the case of a lecturer not teaching any modules.

SELECT l.StaffID, l.FirstName, l.Surname, m.ModuleDescription
FROM dbo.Lecturer as l
RIGHT OUTER JOIN dbo.Module as m
    ON l.StaffID = m.ModuleConvenor
WHERE m.ExamWeight = SELECT AVG(m.ExamWeight) AS 'Avg ExWeight'
                     FROM dbo.Module as m2
                     WHERE m2.ModuleConcenor = l.StaffID OR m2.ModuleConvenor IS NULL)
WHERE (SELECT AVG(m.CWWeight) AS 'Avg CWWeight'
    FROM dbo.Module as m3
    WHERE m3.ModuleConcenor = l.StaffID OR m3.ModuleConvenor IS NULL)