texts: Why can hr create an Oracle DB session though not using PWFILE?

texts:
I have created a database. HR schema has been enabled.

connect / as sysdba is known as operating system authentication.

connect hr/password is not operating system authentication right ? If yes, I guess that authentication is possible only with the oracle password file located at ORACLE_HOME/database for Windows.

I have queried V$PWFILE_USERS but it only showed me sys... Other users (such as hr, system, ...) are not in that view and I guess not in the password file.

Why ? What type of authentication is used by connect hr/password ? Athentication by OS o by Password ?


texts:
I'm trying to connect to a microsoft SQL database but I dont know its name.

I suspect it will be something of the form servername\databasename

It would be good if I could just use SELECT DB_NAME() but since I don't know what its called, I cant run the query in the first place.

I can see that there is a SQL port open in zenmap and when I look at the server I can see that the process is running in the task manager.

EDIT

When just using the server name I get an error message

  A network-related or instance-specific error occurred while
  establishing a connection to SQL Server. The server was not found or
  was not accessible. Verify that the instance name is correct and that
  SQL Server is configured to allow remote connections. (provider: Named
  Pipes Provider, error: 40 - Could not open a connection to SQL Server)
  (.Net SqlClient Data Provider)


texts: SQL Server cannot obtain a LOCK resource at this time

texts:
I'm somewhat familiar with blocking processes and locks in SQL Server, however this one has me pretty confused. I understand this error can come from two issues: 1) if SQL Server cannot allocate more memory for locks or 2) if there is a blocking process.

I do not believe #1 is the issue as the machine has 48GB memory and only ~14GB is used. SQL Server is configured to use a max of 2,147,483,647MB of memory.

With #2, when I run sp_lock, I see a row with an ObjID and a TABLE lock, mode IS. This lock is under the same process I am running SSMS under. Therefore when I try to do a KILL on the process, I get the error "Cannot use KILL to kill your own process."

I have restarted the server a couple of times and closed SSMS and still this lock persists. My program can't do work on the relevant table because of the lock; it keeps getting the error message that SQL Server cannot obtain a lock and to try again at a less-busy time. There is no activity on the server, I am the sole user. Therefore I do not believe this is a resource issue, but firmly related to the lock I can't seem to get rid of.

I have run a modified sp_lock procedure that uses sp_who in conjunction to try and determine who is blocking who. In this view, I see my userid has one IS mode lock, but it is not an exclusive lock.

How can I release the table lock? What is happening with the database? How can I avoid this issue in the future?


texts: Most efficient way to join two tables with MAX value

texts:
I am optimizing a query, where I have to join two tables, let's say Product and TransactionHistory table, and return multiple columns from both table for the last TransactionDate from the TransactionHistory table for each Product in the Product table.

TransactionHistory table has around 13 Million rows and the Product table has almost 2 million rows.

Sounds pretty easy, doesn't it? but for different scenarios different query perform differently.

I am not using my actual table names here, to show the different query syntax I have tried, I am using Adam Machanic's BigProduct and BigSalesHistory Tables.

The query was originally written like....

SELECT p.ProductID
     , p.Name
     , h.Quantity
     , h.TransactionDate
FROM [dbo].[bigProduct] p
INNER JOIN [dbo].[bigTransactionHistory] h ON p.ProductID = h.ProductID
WHERE h.TransactionDate = ( SELECT MAX(TransactionDate)
                            FROM [dbo].[bigTransactionHistory]
                            WHERE ProductID = h.ProductID )
AND p.ProductID > 1317
AND p.ProductID < 1416
GO

Which resulted in some 30 scan counts and almost 300,000 logical reads.

I being a big fan of windowing functions wrote the following queries using ROW_NUMBER(), both backfired badly:

SELECT p.ProductID
     , p.Name
     , c.*
FROM [dbo].[bigProduct] p
INNER JOIN ( SELECT  h.ProductID
                   , h.Quantity
                   , h.TransactionDate
                   , ROW_NUMBER() OVER (PARTITION BY h.ProductID
                                            ORDER BY h.TransactionDate DESC) rn
              FROM [dbo].[bigTransactionHistory] h
            ) c
            ON p.ProductID = c.ProductID AND rn = 1
WHERE p.ProductID > 1317
  AND p.ProductID < 1416
GO

AND

SELECT p.ProductID
     , p.Name
     , c.*
FROM [dbo].[bigProduct] p
CROSS APPLY ( SELECT  h.ProductID
                   , h.Quantity
                   , h.TransactionDate
                   , ROW_NUMBER() OVER (PARTITION BY h.ProductID
                                            ORDER BY h.TransactionDate DESC) rn
              FROM [dbo].[bigTransactionHistory] h
              WHERE p.ProductID = ProductID
            ) c
WHERE p.ProductID > 1317
  AND p.ProductID < 1416
  AND rn = 1
GO

The Scan count went into 100s and the logical reads jumped to 700,000s.

Being pretty disappointed by the windowing function's performance I rewrote the query using CROSS APPLY

SELECT p.ProductID
     , p.Name
     , c.*
FROM [dbo].[bigProduct] p
CROSS APPLY ( SELECT TOP 1  h.ProductID
                          , h.Quantity
                          , h.TransactionDate
              FROM [dbo].[bigTransactionHistory] h
              WHERE p.ProductID = h.ProductID
              ORDER BY h.TransactionDate
             ) c
WHERE p.ProductID > 1317
  AND p.ProductID < 1416
GO

Now this query seems to perform the best out of all the above mentioned but as soon as the OUTER Query's Range for ProductID increases this query goes horribly wrong and starts performing worse than the above two.

Also even though it reduced the logical reads down to 27000 but the CPU time and the elapsed time jumped up by 8 times.

Is there a more efficient way of writing a query like this, keeping in mind that the outer query range for ProductID varies a lot?.

Any suggestion or pointers in the right direction are much appreciated. Thank you.


texts: Storing encrypted and not encrypted data in the same column value?

texts:
I have several concept questions about encrypting data.

The task

My goal is to encrypt NVARCHAR(3800) column but I want to encrypt only small percent of the data (less than 1% of it). As the table is referred in so many SQL objects, ASP files, .rdl files and SSIS projects I do not want to create separate table to hold these values there (at least for now I am looking for alternatives).

The plan

I am thinking for one of the following solution:

Add bit column to the table called isEncrypted with default constraint 0 and with values
Rename the current column to _column_raw_data
Add new column called _column_encrypted_data
Add new computed column with the original column name _column which will return the _column_raw_data if IsEncrypted flag is 0, otherwise the _column_encrypted_data
By default a query will return encrypted data - in order to decrypt it, additional logic will be implemented

or I am wondering is there any considerations to:

Add bit column to the table called isEncrypted with default constraint 0 and with values
When data is going to be encrypted the flag is raised and the output encrypted varbinary value will be set in the existing column

I am going to test the both variants of course but before doing that I am wondering is there another option or any issues in the above ones?


texts:
We have a mysql database running version 5.0. Every month we shutdown the database for maintenance. We run a myisamchk on the tables to fix data fragmentation, and this takes down the system for up to an hour.

Will it be better to run optimize table daily via a cron so as to eliminate the downtime or is it necessary to do the myisamchk periodically ?.


texts: How can I count affected rows by an insert and a merge statement

texts:
I have little experience with DB2. I have been trying to find out how to get the number of affected rows (after insert and merge statements) and/or a success message to give back to my HTTP Post request. Can anyone help me on this?

Using DB2 node in Node-RED.


texts: Can I restore all tables but not the stored procedures?

texts:
I have a testing database that I restore regularly from a production backup file in SQL Server 2008 R2 to get new data. We often edit\change or update the stored procedures in the testing DB to test the data compared to the production's stored procedures!

Is there away to only refresh\restore the table(s) and not the stored procedures? so I don't have to re-edit them back to the changes that I need?

Both of these databases (Production and Testing) are in one server.

My DB size is about 5 GB and we can't afford to have third party tools.


texts: SQL Server not using all NUMA memory with 20 core limit and affinity mask

texts:
I have a physical SQL Server 2016 SP1 machine with this spec:

CPU Sockets:      2
Cores per socket: 44
Multithreading:       Yes
Number of cores:  88
RAM:  256 GB
SQL Server Max Memory:    240 GB
NUMA: CPU1    128 GB
CPU2    128 GB

We are running SQL Server 2016 Enterprise Edtion CAL licensing, not Core, so with licensing limitations we can only utilise 20(40) cores.
By default affinity mask enables the first 40 cores on CPU1. This means that CPU2 and the NUMA node memory are not used.

We have changed the affinity mask to enable CPU1 and CPU2 and therefore make the memory on both NUMA nodes available to SQL Server.

ALTER SERVER CONFIGURATION

SET PROCESS AFFINITY CPU = 1 TO 20, 65 TO 84;

Info and selects from Glenn Berry https://www.sqlskills.com/blogs/glenn/balancing-your-available-sql-server-core-licenses-evenly-across-numa-nodes/

-- SQL Server NUMA node information

SELECT node_id, node_state_desc, memory_node_id, processor_group, online_scheduler_count,
   active_worker_count, avg_load_balance, resource_monitor_state FROM sys.dm_os_nodes WITH (NOLOCK) WHERE node_state_desc <> N'ONLINE DAC' OPTION (RECOMPILE);

-- SQL Server schedulers by NUMA node

SELECT parent_node_id,   SUM(current_tasks_count) AS [current_tasks_count], SUM(runnable_tasks_count) AS [runnable_tasks_count],   SUM(active_workers_count) AS [active_workers_count],   AVG(load_factor) AS avg_load_factor FROM sys.dm_os_schedulers WITH (NOLOCK) WHERE [status] = N'VISIBLE ONLINE' GROUP BY parent_node_id;

Info and selects from David Alcock https://www.sqlshack.com/monitoring-memory-clerk-and-buffer-pool-allocations-in-sql-server/

SELECT TOP 10 [type], SUM(pages_kb) / 1024 AS SizeMb FROM sys.dm_os_memory_clerks GROUP BY [type] ORDER BY SUM(pages_kb) / 1024 DESC

This indicates that around 90 GB of memory is being used.

Databases on this server add up to 285 GB.
sys.dm_os_buffer_descriptors reports that the buffer pool is using 65 GB with 10 GB empty.

select * from sys.dm_os_process_memory

physical_memory_in_use_kb   large_page_allocations_kb   locked_page_allocations_kb  total_virtual_address_space_kb  virtual_address_space_reserved_kb   virtual_address_space_committed_kb  virtual_address_space_available_kb  page_fault_count    memory_utilization_percentage   available_commit_limit_kb   process_physical_memory_low process_virtual_memory_low
249659780   0   0   137438953344    489700832   251863768   136949252512    715604512   100 47434048    0   0

Is there a way to tell for definite if all memory is being used? Or does the fact that 4 nodes are offline mean that is the case?

Many Thanks

1) Look at the results of sys.dm_os_memory_nodes. If you configured SQL Server correctly you should see rows for memory_node_id 0 and 1. That means that SQL Server has access to all of the memory on your server.

I have memory_node_id 0 and 1.

2) Look at the results of sys.dm_os_memory_clerks for your favorite memory clerk that is NUMA aware. For example, you can see if the buffer pool is spread over multiple NUMA nodes:

SELECT memory_node_id, pages_kb FROM sys.dm_os_memory_clerks WHERE [type] = N'MEMORYCLERK_SQLBUFFERPOOL';

You are likely to see pages for both memory node 0 and 1. It's possible that you won't, and that doesn't necessarily represent a problem.

This shows pages for both node 0 and 1.

3) Look at the results of sys.dm_exec_query_resource_semaphores. Target_memory_kb should be around 70-80% of max server memory. If it's closer to 96 GB than 192 GB then you might have a problem.

target_memory_kb is 150-180 GB for resource_semaphore_id 0. The higher value is 70% of max server memory.

This all indicates that all memory is available to SQL Server, even if it isn't all currently being used. And the bufferpool usage will increase over time.

Thanks


texts: Postgresql hot standby what happens when Master is down

texts: Beginner question. When Master is down hot-standby become automatic master? When old master is up what happened to hot-standby and meanwhile data insert in hot-standby how is transfer to the old-master


texts: What happens when one of the filegroup is full within SQL server

texts:
Just a basic question due to my lack of understanding on how below works:

For one of the databases around 1 TB in size we have 8 file groups with multiple files added to them.

Now for one of the FG say FG1 there are 3 files :

FilesFG1a-has max growth restricted to 150 GB and 130 GB is used with 20 free
FilesFG1b-has max growth restricted to 150 GB and 150GB is used with 0 free
FilesFG1c-has max growth restricted to 150 GB and 150GB is used with 0 free

Now the operation will continue to work as we have 20 GB still out there on FilesFG1a but what happens when all 3 files are full within that FG1 file group?

I know we can increase the max growth restr. value as space is there on disk. But if not, will there be failures or operation tends to move data into mdf files instead of going to ndf files ?


texts: How to restore a database to a point in time using a script in Microsoft SQL Server

texts:
I have a database that must always restore to the date in which it was created, after test data have been added on it, I get the create_date from the script below:

WITH LastRestores AS
(
SELECT

    [d].[create_date] ,

    r.*,
    RowNum = ROW_NUMBER() OVER (PARTITION BY d.Name ORDER BY r.[restore_date] DESC)
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.[restorehistory] r ON r.[destination_database_name] = d.Name
WHERE [d].[name] = 'MyDatabase'
)
SELECT create_date
FROM [LastRestores]
WHERE [RowNum] = 1

Now what I need to do is run a script that restores the database to the date in which it was created, assuming the date is static/hardcoded in the script i.e: is always '2019-03-08 11:31:50.237' and the database is not restored but stored in a certain folder i.e C:\Documents\BackUps\MyDatabase.bak , now lets say I normaly restore databases like this:

USE [master]
RESTORE DATABASE MyDatabase
FROM
DISK = N'C:\Documents\BackUps\MyDatabase.bak' WITH  FILE = 1,
MOVE N'MyDatabase' TO N'C:\Documents\BackUps\MyDatabase_rows.mdf',
MOVE N'MyDatabase_log' TO N'C:\Documents\BackUps\MyDatabase_log.ldf',
NOUNLOAD,  STATS = 5
GO

The only difference now is that it must restore to a point in time which is '2019-03-08 11:31:50.237' using a script.


texts: Getting error when trying to use OPENQUERY Msg 7321, Level 16, State 2, Line 1

texts:
When I run this query:

SELECT *
FROM OPENQUERY([XXX], 'SELECT *
                       FROM Database.Table
                       WHERE (MBCONO=650) AND MBCUNO LIKE a%' )

I get the error:

  OLE DB provider "DB2OLEDB" for linked server "XXX" returned message
  "Token %ÂŸFOR SKIP WITH FETCH ORDER UNION EXCEPT OPTIMIZE SQLSTATE:
  42601, SQLCODE: -104".

  Msg 7321, Level 16, State 2, Line 1 An error
  occurred while preparing the query "SELECT * FROM Database.Table WHERE
  (MBCONO=650) AND MBCUNO LIKE a%'" for execution against OLE DB
  provider "DB2OLEDB" for linked server "LAWSON".

I can run the same query without AND MBCUNO LIKE a% successfully.


texts: Should a filtered index theoretically perform faster in this example?

texts:
I have a SuperheroMovies table which has all superhero movies, and has a column called "UniverseName" which has either the values "Marvel", "DC", "Capcom", "Unknown".
It also has columns called "MovieId" (primary key) and "MovieTitle".
(There are also other columsn in SuperheroMovies, which we can call OtherField1...OtherField25.)

The clustered index is on the primary key MovieId.
There's also a nonclustered index IX_MovieTitle on the table for the MovieTitle column.

This is the query that will be ran the most against the SuperheroMovies table:

DECLARE @MovieTitlePrefix VARCHAR(50) = 'F'; -- This can be any single character from A to Z

SELECT MovieId, MovieTitle, UniverseName, OtherField1, OtherField2, OtherField3, ROW_NUMBER() OVER (PARTITION BY UniverseName ORDER BY MovieTitle) AS SortId
FROM SuperheroMovies
WHERE MovieTitle LIKE @MovieTitlePrefix + '%'
    AND UniverseName <> 'Unknown'

If a nonclustered index IX_MovieTitle_FilteredOnUniverseName ON SuperheroMovies (MovieTitle) WHERE UniverseName <> 'Unknown' was created and used in the query instead of IX_MovieTitle, should it perform faster?

I would still need the filter on UniverseName <> 'Unknown' in the query with IX_MovieTitle_FilteredOnUniverseName to achieve the same result set, right?


texts: MySQL profile on query "Creating Sort Index" using 75% of the total time

texts:
We are trying to figure how to optimize a query (taking around 100ms), and running profile we see Creating Sort Index using 75% of the total time. First, what exactly effects creating the sort index? Is it disk/io?

Second, is there any optimization we can make to the query itself?

SELECT r.`id`,
       r.name,
       r.public_uri,
       rv.version,
       rv.interpreter,
       rv.notes,
       rv.content,
       r.added,
       r.added_by,
       r.modified,
       r.modified_by,
       r.public,
       r.public_by
  FROM recipe_heads rh,
       recipes r,
       recipe_versions rv
 WHERE rh.recipe = r.`id`
   AND rh.recipe_version = rv.`id`
   AND r.`id` = rv.recipe
ORDER BY r.added DESC

Explain:


texts: Mapping the oracle database directory object to a network path

texts:
I'm an accidental DBA. We have oracle database running on windows server.

I created an oracle directory object named 'Result' and mapped it one of the local drives (C:\appResult) on the database server. The front end application creates a report and writes the file to 'Result' and a file thus gets written to C:\appResult. Now, we want to change the file path 'C:\appResult' to some other server in the network (another server in the network). What is the best way to achieve this?

Thanks


texts: MYSQL find if a default value has been set for a table (DateTime Specifically)

texts:
I am trying to internationalize a database, but there are places where the timestamp that gets set is using NOW() instead of UTC_TIMESTAMP(), and it's using Amazon's RDS with multiple MySQL servers.  The problem is that each is on their own timezone and this creates a mess of disentangling the various timezones, translating them to UTC, and then re-translating them back to the users timezone.

This database has some 140+ tables and I'd like something more efficient than searching manually by using SHOW CREATE on each table, plus I'd like to be able to check when I'm done that everything was converted correctly .

Any ideas on a query that would do this?

To be clear, I'm looking for a query to SELECT the rows that I need, I can figure out a INSERT... SELECT statement from that.


texts: Restore Log and Differential Backups Simultaneously in MS SQL Server

texts:
I have the full backup of a database, also one differential backup and one log backup. Is it possible to restore this 3 backups together? SSMS lets only full backup and only one of them. Is there a way to restore both? It's okay to show the way in t-sql too.

Thanks for your time!


texts: Error 9001, Severity: 21, State: 1 The log for database 'DB_NAME' is not available

texts:
I have a SQL Server 2008 R2.

I got a mail that the backup had failed, I opened the SQL log viewer and started browsing.
And i found these two errors a bunch of times.

error 1:

Date        31-01-2013 13:20:30
Log     SQL Server (Current - 01-02-2013 05:13:00)

Source      spid20s

Message Error: 9001, Severity: 21, State: 1.

error 2:

Date        31-01-2013 13:20:30
Log     SQL Server (Current - 01-02-2013 05:13:00)

Source      spid20s

Message
The log for database 'DB_NAME' is not available. Check the event log for related error messages. Resolve any errors and restart the database.

After a little googling, I had read a few times that the errors indicated that the DB or log file maybe corrupt.
I read that I could confirm it, by running a DBCC CHECKDB 'DB_NAME', so i did, and here are the last to lines of the output:

CHECKDB found 0 allocation errors and 0 consistency errors in database 'DB_NAME'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

The output shows that the DB isn't corrupt. But can the log file still be corrupt?

I also read that one solution might be run something called "Emergency Mode Repair" to fix the corrupt files. But some users did NOT recommend it.

Could another solution be to delete the log file completely and then create a new?.


texts: Why does Postgresql not write the username to the log file

texts:
I am trying to understand why, my database is logging without username in the queries.

I discovered today that I can't see by looking at the logs who did a query.

I think it is a configuration option, but why a configuration like that can exist in a database!

The log is there, but it is not registering the usernames connected to the database, so the logs aren't good for anything, absolute nothing, for no one in the world in my opinion!  So why it let me doing so stupid thing with myself?

The configuration looks like this:

log_statement = 'mod'                   # none, ddl, mod, all


texts: phpmyadmin/ mysql how to copy a table with all its constraints

texts:
I tried to copy a table which is connected to another table through a foreign key using

$query = "CREATE TABLE table1 AS SELECT * FROM table 2";

but when I run this query it copies every thing but the constraints are not copied.

Is there any way I can run a script through phpmyadmin or write a function that preserve the constraints and the references ?


texts: When refreshing materialised views does the index automatically refresh


texts: Create a trigger that compares a field, with a field in other table

texts:
I have 3 databases, (One principal, one Mirror and a witness) and I'm trying to make this system the most fault tolerant as possible.

So I was thinking in doing some extra codding to protect me against the loss of the witness in some cases.

I want to keep track of mirroring_state_desc in SYS.database_mirroring database, so that in case it changes from SYNCHRONISING to DISCONNECTED I'll know its not safe to promote it to principal, but if it changes from SYNCHRONIZED to DISCONNECTED is ok to promote it (I'm working in high safety mode)

My first approach was to have a custom windows service monitoring this table...but doing that I'm doing a LOT of unnecessary selects to the database.

So I though, maybe a trigger would be what I'm looking for... but i stuck with just

CREATE TRIGGER modify_state ON SYS.database_mirroring FOR INSERT, UPDATE

I need help for the rest of the syntax.

I have a state_db database with 2 columns (last_state and current_state), and I need to be able to check the current state, with the one in my state_db. if they are different I want to modify the last_state with current_state and current_state with the fresh state read from SYS.database_mirroring

My question is, can I use a trigger for insert and modify to keep an auxiliary database with the last 2 states of SYS.database_mirroring?? and if yes, how can I do it?


texts: Do we need index on "order by" if the "where" clause alreday have index ( small data set )

texts:
Assume I have a very large table X and I want to run below SQL.

select * from table X where type='X1234' order by time;

type is not unique and indexed ( with high cardinality), and there are very few rows in one type ( assume 1 - 5 ). Data size of each row is about 50 byte to 50K byte

Do I still need index for time column?

I think although the table is very large, but "where" clause should be executed before "order by", that will result in a small dataset and no index is needed for column time. Am I right?

Thanks!

Update:

I have tested with composite index ( type, time ), and single index ( type ), the report explain shows as below.

Composite index
+----+-------------+---------+------------+------+-----------
| id |type |ref   | rows | filtered | Extra                 |
+----+-------------+---------+------------+------+----------+
|  1 |ref  |const |    3 |   100.00 | Using index condition |
+----+-------------+---------+------------+------+----------+

Single index
+---------+------------+------+---------------+------------+---------+------+
| id |type |ref   | rows | filtered | Extra                                 |
+----+-------------+---------+------------+------+---------------+----------+
|  1 |ref  |const |    3 |   100.00 | Using index condition; Using filesort |
+----+-------------+---------+------------+------+---------------+----------+

There is no noticeable difference in execution time. It is likely the dataset is small and the filesort operation was entirely performed in buffer cache, so it is still very fast.

In case the dataset become larger, then using composite index should be a good idea.


texts: How to calculate running balance for many accounts?

texts:
please help me fix query below to calculate running balance correctly.

What I need to do is:

Calculate running balance (debit- credit) by account_no
Reset the balance when account_no changes
Reset the balance if transaction_date is in a new financial period AND account_type > 5

Query

SELECT
    acc_account_transactions.id,
    account_no,
    account_type,
    transaction_id,
    DATE_FORMAT(acc_transactions.transaction_date,
            '%d/%m/%Y') transaction_date,
    debit_balance,
    credit_balance,
    IFNULL(acc_finacial_periods.id, - 1),
    (@current_period_id:=IF(IFNULL(acc_finacial_periods.id, - 1) <> @current_period_id,
        IFNULL(acc_finacial_periods.id, - 1),
        @current_period_id)) current_period_id,
    (@running_balance:=IF(@curr_account_no < account_no
            OR (@current_period_id <> IFNULL(acc_finacial_periods.id, - 1)
            AND account_type > 5),
        0,
        @running_balance)) prev_runnng_bal,
    (@curr_account_no:=IF(@curr_account_no < account_no,
        account_no,
        @curr_account_no)) curr_account_no,
    (@running_balance:=@running_balance + (acc_account_transactions.debit_balance - acc_account_transactions.credit_balance)) AS running_balance
FROM
    (SELECT
        @running_balance:=0,
            @curr_account_no:=0,
            @current_period_id:=- 1
    ) r,
    acc_account_transactions
        INNER JOIN
    acc_transactions ON acc_transactions.id = acc_account_transactions.transaction_id
        INNER JOIN
    acc_accounts ON acc_accounts.id = acc_account_transactions.account_no
        LEFT JOIN
    acc_finacial_periods ON DATE_FORMAT(transaction_date, '%Y/%m/%d') >= DATE_FORMAT(startdate, '%Y/%m/%d')
        AND DATE_FORMAT(transaction_date, '%Y/%m/%d') <= DATE_FORMAT(enddate, '%Y/%m/%d')
ORDER BY account_no , acc_account_transactions.id

What's wrong with it now?

current_account_no variable has a wrong value, -it should be same as account_no column- hence, the running_balance result is also incorrect

Fiddle: here

Note:
Database server info is below

Please don't close my question, I really need help :/

EDIT

current wrong result as in the fiddle (last query):

what I expect, something like this:


texts:
I adapted this syntax to fit my need from a post from @ypercubeᵀᴹ - but in my instance shown below - the Select statement only returns the top most value for the patientID it does not SUM() the value for each DoctorName listed.  In this instance, I want the SUM() for TotalBill by each DoctorName

What is set-up wrong in the query below?

Declare @TotalBill Table
(
     doctorname varchar(100)
    ,patientid varchar(10)
    ,totalbill decimal(10,2)
    ,vdate date
)

Declare @HospitalInfo Table
(
     doctorname varchar(100)
    ,patientid varchar(10)
    ,vdate date
)
INSERT INTO @TotalBill (doctorname, totalbill, patientid, vdate) VALUES
('Doctor Z',    '11.48',    '4321',         '2017-01-25'),
('Doctor Z',    '11804.00', '3431',         '2017-01-31'),
('Doctor Z',    '879.40',   '1111',         '2017-01-26'),
('Doctor Z',    '11.24',    '0089',         '2017-01-26'),
('Doctor A',    '11.24',    '6654',         '2017-01-26'),
('Doctor A',    '55.11',    '0011',         '2017-01-26'),
('Doctor A',    '66.24',    '5466',         '2017-01-26'),
('Doctor A',    '22.24',    '1098',         '2017-01-26'),
('Doctor A',    '13.24',    '9987',         '2017-01-26')

Insert Into @HospitalInfo (doctorname, patientid, vdate) Values
('Doctor Z',    '1111',         '2017-01-26'),
('Doctor Z',    '0089',         '2017-01-26'),
('Doctor Z',    '3431',         '2017-01-31'),
('Doctor Z',    '4321',         '2017-01-25'),
('Doctor A',    '6654',         '2017-01-26'),
('Doctor A',    '0011',         '2017-01-26'),
('Doctor A',    '5466',         '2017-01-26'),
('Doctor A',    '1098',         '2017-01-26'),
('Doctor A',    '9987',         '2017-01-26')

Declare @Startdate datetime = '01/01/2017', @Enddate datetime = '01/31/2017'
Select
a.[doctorname]
,a.[patientid]
,[Total Bill] = COALESCE(sumTB,0)
FROM
    ( SELECT [doctorname]
             ,patientid
             ,vdate
             ,rn = ROW_NUMBER() OVER (PARTITION BY a.[doctorname]
                                     ORDER BY [patientid])
      FROM @HospitalInfo As a
    ) As a
    OUTER APPLY
    ( SELECT sumTB = SUM(b.[totalbill])
      FROM @TotalBill b
      WHERE a.patientid = b.patientid
      AND a.[doctorname] = b.[doctorname]
        AND a.rn = 1
    ) AS b
WHERE CAST([vdate] As Date) BETWEEN @Startdate AND @Enddate

EDIT
My desired output is like the image below.  SUM the totalbill - and only show it for ONE patientID.  I do not care if it is a random id or the Max() or Min() as longn as it is only one


texts: Separating data to different tables relating to the same object

texts:
I am unsure what is the correct practice here. I have a collection of rows with fixed values in the columns....consider them fixed attributes for a particular object of a given ID. So i guess you could say a read-only table.

Now I am also about to add a sizable number of more columns of which their values change (well over 30 columns most likely as the project progresses). Rows won't be deleted or inserted frequently, as there is one row per object, similar to the read-only table.

This will increase the number of columns by quite a large amount, of which, I read large column counts is not ideal. So i was wondering should i separate these columns to another table so that fixed values are in one table, changing values in another?

Are there any benefits to doing this? Or is there no point for something with less than 50 columns in a table?

I don't know the terminology for a lot database stuff, I am self taught from google. So am looking for advice from people who know how to structure databases to work at their best.


texts:
After a hot backup, where all tablespaces are placed in backup mode seperately; i.e.

alter tablespace user_data start backup;

each datafile will have a different Checkpoint SCN in its header; with the equivalent redo log files which will bring it up to date.

Why doesn't this non-sync of Checkpoint SCN cause a problem when restoring and recovering these datafiles?  The "checkpoint" SCN of each datafile is different from other datafiles and probably different from the control file's recorded SCN as well; doesn't this cause a problem?

Addition

Doesn't the inconsistency of the SCN in the datafiles cause a problem when SMON checks them once we decide to restore and recover the database using the hot backup?


texts:
I know that mysql table comment can be defined at the creation with:

create table (...)comment='table_comment';

And you can display comments by:

show table status where name='table_name';

How do you change (alter?) table comment after it's been created. I mean wihtout dropping and recreating the table again.


texts: How to manage RAM consumption in MongoDB?

texts:
I am using MongoDB. I have a hardware device which continuously submits data to the database every 10 seconds for 4 hours a day. The data includes latitude, longitude, and other basic information of the device. I have indexed latitude and longitude for faster querying. I have used an event based document creation approach. That is, every time the device submits data, I am creating a document in my collection. The average number of documents created for per day will be 1,440. My document size is 300 bytes. Since I have 3 indices (including _id) the RAM consumption per document is going to be 8176*3 = ~24KB. I will have a million devices submitting data in a similar fashion. If this continues I will end up wanting 33,000 GB of RAM.

Am I assuming something wrong? What is the right approach to manage the documents in this case?


texts: Find a specific table and column value across all databases

texts:
This is a question about logic that could help me understand more how to iterate into my DBs.

On my server I have several AdventureWorks* databases:

I have a query that cold help me iterate through all my databases, AdventureWorks* and not:

CREATE TABLE #LIst_DB (name nvarchar(128))

INSERT INTO #LIst_DB

select name from  sys.databases
WHERE database_id > 4
AND state = 0;

select * from #LIst_DB

DROP TABLE #LIst_DB

For each AdventureWorks* database I want to list AWBuildVersion next to each DB:

At this point I'm puzzled because there are 2 problems I don't know how to solve:

How to run a select [Database Version] from AWBuildVersion for each database?
What to do if a database has no AWBuildVersion table?

Here I found this query:

DECLARE @SQL NVARCHAR(max)

SET @SQL = stuff((
            SELECT '
UNION
SELECT ' + quotename(NAME, '''') + ' as Db_Name, Name collate SQL_Latin1_General_CP1_CI_AS as Table_Name
FROM ' + quotename(NAME) + '.sys.tables WHERE NAME =  @TableName '
            FROM sys.databases
            ORDER BY NAME
            FOR XML PATH('')
                ,type
            ).value('.', 'nvarchar(max)'), 1, 8, '')

--PRINT @SQL;

EXECUTE sp_executeSQL @SQL
    ,N'@TableName varchar(30)'
    ,@TableName = 'AWBuildVersion'

That brings me close to my goal but it shows me the table name AWBuildVersion while I need the column Database Version = 11.0.2100.60


texts: What's a good (free) client for Informix on Windows?

texts: I'm looking for a GUI client for Informix on Windows (mainly to view data and execute queries), free would obviously be nice but I know Informix is used mostly in corporate environments and free may not be available. Perhaps the best option is set up ODBC and use any one of many clients available?


texts: Understanding optimisation of complex SELECT queries

texts:
I have a people table with basic contact info in. I have a bunch of criteria that can select Id values (fields called pid) from the people table.

I want records that match ((criterion 1 or criterion 2) and criterion 3).
I want the criteria statements to add columns to the results.
I need a standard way to assemble the queries because I'm making a 'search builder'; so I can't optimise every combination. It can be assumed that each criterion will return zero or one row.

EG1: I first tried

SELECT people.*
  FROM people
       LEFT JOIN (criterion1 SELECT) c1 ON people.id=c1.pid
       LEFT JOIN (criterion2 SELECT) c2 ON people.id=c2.pid,
       (criterion3 SELECT) c3
 WHERE people.id=c3.pid
   AND ( c1.pid IS NOT NULL OR c2.pid IS NOT NULL );

This was far too slow - it never actually returned records in the minute I allowed it!

EG2:Next I tried

SELECT people.*
  FROM people,
       (       (criterion1 SELECT)
         UNION (criterion2 SELECT) ) c12
 WHERE people.id=c12.pid
   AND people.id IN (criterion3 SELECT)

This one returns 2000+ rows in 0.6s, which is acceptable. But you can't add any columns from criterion 3, and can't access columns from c1 and c2 either, really, since one will mask another.

EG3:Out of interest, I moved the c12 into the WHERE clause:

SELECT people.*
  FROM people
 WHERE id IN (criterion3 SELECT)
   AND id IN ( (criterion1 SELECT)
               UNION (criterion2 SELECT) )

but again, this threw it for well over a minute before I killed the query.

EG4: So I returned to the EG2 code, and did this ugly thing:

SELECT *
  FROM ( all the EG2 SQL ) src
       LEFT JOIN (criterion1 SELECT) c1 ON src.id=c1.pid
       LEFT JOIN (criterion2 SELECT) c2 ON src.id=c2.pid

So the c1 and c2 queries are added in twice! This returns the (same number of) results, with the extra fields, in 6s. Too slow, really, but at least it runs. And really ugly!

Can anyone give me any pointers to explain any of this? Why is EG3 massively faster than EG1/EG4 when they're all doing the same thing? Why should running the queries twice (EG4) be faster than running them once (EG1)?!

EDIT: Using MySQL 5.1, InnoDB tables.


texts: Design advice regarding a users/groups/tasks database scheme

texts:
Here are my requirements:

one user can have many tasks
one group can have many task
one group can have many users
only invited user by a user can be added into a group

Does this db design appear to model these requirements?

In group table, group_id should be the FK of which? there are 2 table linked to that table. 1 FK to 2 PK? possible?


texts: Azure Failover Clustering vs On Premise Failover Clustering

texts:
Azure AlwaysOn FCI Failover Clustering IaaS VMs seem to have a different diagram compared to On-Premise Failover Clustering.

Azure has two storage for 2 Vms; Traditional On-Premise contains 1 Shared storage.

(a) Does this mean Azure Failover Clustering not only provides server level protection, but also database level protection, and Availability Groups are no longer required?

(b) Additionally, should Azure Failover Clustering server nodes be in the same zone, or would putting them in different zones, affect performance/server speed?

MSDN Configure SQL Server Failover Cluster Instance on Azure Virtual Machines

https://blogs.technet.microsoft.com/askcore/2015/06/24/windows-server-failover-cluster-on-azure-iaas-vm-part-1-storage/


texts: How can I reset a mysql table auto-increment to 1 in phpMyAdmin?

texts:
I know that in MySQL at the command line I can reset a table's auto-increment field to 1 with this:

ALTER TABLE tablename AUTO_INCREMENT = 1

I am curious if there is a way to do this from within phpMyAdmin.  Something like a check box to reset the auto-increment or something else along those lines?

Not that there is anything wrong with the command line approach.  More one of those curiosity things I keep thinking on...  Thanks in advance!


texts:
I'm trying to track the different wait time classes for current Oracle sessions but I must be misunderstanding something.  When I compare the sum(time_waited)/1000 for a given session in gv$session_wait_class its often greater than the value of seconds_in_wait from gv$session and sometimes even greater than its last_call_et which I thought would be the total time...

What am I missing here?  Why aren't they the same?  I'm comparing numbers with this query:

select last_call_et,wait_time,seconds_in_wait,wait_time_micro/1000000,
(SELECT SUM(time_waited)
  FROM gv$session_wait_class ot WHERE
  ot.sid = ses.sid
  AND ot.serial#                          = ses.serial#
  AND ot.inst_id                          = ses.inst_id
  )/1000 as wait_time_class from gv$session ses where sql_id is not null;


texts: How to combine duplicate parent rows in a relational MySQL table

texts:
I have my DB structured like this with people.person_id as the foreign key for each row in people_meta.

people table

 ╔═════╦════════╦═══════╗
 ║ id  ║ fname  ║ lname ║
 ╠═════╬════════╬═══════╣
 ║  0  ║ John   ║  Doe  ║
 ║  1  ║ John   ║  Doe  ║
 ║  2  ║  Bob   ║ Smith ║
 ║  3  ║ Dave   ║ Jones ║
 ║  4  ║  Bob   ║ Smith ║
 ║  5  ║  Jen   ║ Smith ║
 ╚═════╩════════╩═══════╝

people_meta table

 ╔═════╦════════════╦════════╦═══════╗
 ║ id  ║ person_id  ║  key   ║ value ║
 ╠═════╬════════════╬════════╬═══════╣
 ║  0  ║ 0          ║ state  ║  NY   ║
 ║  1  ║ 0          ║ age    ║  53   ║
 ║  2  ║ 1          ║ party  ║  D    ║
 ║  3  ║ 1          ║  age   ║  53   ║
 ║  4  ║ 2          ║ state  ║  CA   ║
 ║  5  ║ 4          ║ party  ║  R    ║
 ╚═════╩════════════╩════════╩═══════╝

What I want to do is combine the duplicate people rows and have it cascade to the people_meta table somehow.  Please note that some data in people_meta would be duplicate if they were combined.

The first thing I tried was setting ON DELETE NO ACTION and ON UPDATE CASCADE, then in phpMyAdmin I manually attempted to delete the duplicate parent with the lower ID and update the second duplicate's ID to what the first one was, thinking it would then cascade and give me the desired affect.  -FAIL-  Apparently using InnoDB's ON UPDATE / ON DELETE features won't be helpful for such a task, but at least I've learned more about their purpose.

I'm sort of a newbie for SQL so I'd appreciate any recommendations.


texts: Performance implications of MySQL VARCHAR sizes

texts: Is there a performance difference in MySQL between varchar sizes?  For example, varchar(25) and varchar(64000).  If not, is there a reason not to declare all varchars with the max size just to ensure you don't run out of room?


texts:
I have a table with timestamped data. For all the rows, I want to find the row that is the closest to 20 minutes after and between 15 and 25 minutes after.

e.g. if the table is like

CREATE TABLE foo(id,ts)
AS VALUES
  ( 1::int, '2017.10.27T10:12:15'::timestamp with time zone ),
  ( 2,      '2017.10.27T10:24:17' ),
  ( 3,      '2017.10.27T10:30:22' ),
  ( 4,      '2017.10.27T10:40:12' ),
  ( 5,      '2017.10.27T10:52:16' ),
  ( 6,      '2017.10.27T10:53:11' );

I then run a query

 select t1.id as base t2.id as after
 from table t1, table t2 where ??

I would like to get the answer:

 base after
 1    3
 2    4
 3    5

For 4,5 and 6 as base I get no result as there are no rows matching with my criteria

It would be easy enough to get this for a given timestamp:

select id from table where timestamp > $mytimestamp+'00:15:00'
  and  timestamp < $mytimestamp + '00:25:00'
order by
   abs(extract ( epoch from (timestamp -($mytimestamp + '00:20:00')))
limit 1;

But how to do this for all the rows in a table?


texts: Upload multiple files to SQL Server without access to the Server Box

texts:
I am trying to create a process that would bulk upload multiple csv files periodically to individual tables in SQL server. The issue I am having is those files are not located on the same server.

I can use the bulk upload command to upload files from a folder on the SQL server box but not from our shared drive which is located on a different server. There are multiple people who would be running this process and so it might not be feasible because not all of them would get that kind of access to the SQL server box.

Is there any way of accomplishing this with the same level of efficiency and speed?
I have thought of sending each line in the csv files but those files are huge and would take a long time
Just looking for ideas to make this work.
Would SSIS be less or more or equally efficient?


texts: Translating a query from MS Access to SQL Server

texts:
I have a legacy query from years ago that was written for an MS Access backend:

    DELETE category.*
    FROM category
    LEFT JOIN product_category ON category.id = product_category.categoryid
    WHERE product_category.categoryid IS NULL
      AND category.id IN
        (SELECT cat.id
         FROM category AS cat
         LEFT JOIN category AS cat_par ON cat_par.category_parent = cat.id
         WHERE cat_par.category_parent IS NULL
           AND cat.category_parent != 0
           AND cat.link IS NULL);

My system was changed to SQL Server and this query no longer runs.
I have tested the subquery (in the parenthesis) separately and that works as expected. Just no records are getting deleted.

I have tried changing  the first two lines to read DELETE FROM category (the only syntax difference I was able to look up) but still no deletion occurs.


texts: Badly performing query plan after adding ORDER BY to Full Text CONTAINS search

texts:
The following query is proving beyond my abilities to fix - hope someone can help.

SELECT top 100 Id, Name, InsertDate
FROM Foo
WHERE CONTAINS(FooText, '"test*" and "hello*" and "goodbye*"')
ORDER BY InsertDate desc

Without the ORDER BY clause, the query performs fine (< 2 seconds). But when I add the ORDER BY, performance is awful (> 2 minutes).

The problem seems to be an incorrect query plan.

The query plan that performs well is this:

The query plan that performs badly is this:

IX_Foo_InsertDate is defined as: CREATE CLUSTERED INDEX [IX_Foo_InsertDate] ON [Foo] ( [InsertDate] DESC )

What is causing the change in query plans? How can I fix it?


texts:
I am currently in the process of setting up a database in MySQL for an application where I will be running PowerBI to do analyses on the data in the database and in a further stage to design an application to upload/download data from this db.
I tried to install the 8.0.23.0 Community Edition through the MySQL Installer. I installed MySQL Server, Shell, Workbench and the ODBC Connector (I want to use Access as a front end) and the NET Connector (I want to import the data in PowerBI). Furthermore I choose to not have MySQL running as a Windows Service as it would keep running in the background. I am currently using my machine with limited hardware resources both as a host and a client. We don't have dedicated infrastructure yet and I am purely using my machine to test/develop.
What I would like to be able to do is the following: starting the server instance through MySQL Workbench when working on the project, open Microsoft Access and PowerBI to work on both the reports and the database. After finishing, I would stop the server instance again through MySQL Workbench.
My problem is the following: I am not succeeding in stopping/starting the instance from Workbench and I get the following error message:
I succeed in stopping the server instance through CMD but when trying to start again I get the following messages:
Does anyone know wha I am doing wrong? Did I not install enough packages to get it running or did I configure someting wrong?
Thanks in advance!


texts: create table script with constraints in sql server 2008

texts:
I have create table scripts and alter table scripts with some constraints  for that database.

It take time as twice to create the table and alter the table .. Is this possible to combine

create table and alter table constraints with create table script..in sql server 2008


texts:
Fairly simple question that I can't seem to find an answer to - I'm working with unions and differences, and I would like to perform a COUNT on the results. Currently I'm having to pipe out to a file and "wc -l" the file (minus 4 for the Postgres printing). There's got to be a way to include COUNT...

SELECT tbl1.id EXCEPT (SELECT tbl2.id UNION tbl3.id);

I would like to know the number of results after the set difference. Thanks for any input!


texts:
I am using Debian 8, mariadb 10.4, nginx 10.3, php7.3-fpm.
My timezone configuration was like this:

Debian: Europe/Athens ( through tzselect command )
PHP: Europe/Athens , Default timezone UTC
Mariadb: Europe/Athens

The problem:

My records where inserted on DATETIME fields with UTC timezone ( 3 hours earlier).

I guess it the default timezone on php that makes the problem, but I don 't know how to solve it.


texts: Query to filter out the duplicate and pick the latest - Advanced


texts: SSRS: how to handle unavailability of service when datawarehouse is being rebuilt

texts:
We use SSIS to build datawarehouse (full Microsoft environment). This is legacy SSIS package that starts by truncating all tables in datawarehouse, run nightly.

During this time, we restrict access to the application, this way the database is not altered during integration.

1/ can integration be done on Snapshot of the database, so that we can keep the app up and running?

2/ general question: how to handle the period when datawarehouse is rebuilt? Access to report should be forbidden until all tables are refilled with fresh data. Does exist a mecanism of balance between 2 datawarehouses ? It could be done with parameterised datasources in SSRS instead of shared datasource, but it seems to me like gasworks.

Environment: SQL Server 2005 and we plan to migrate to 2012.


texts: SQL Server 2008 simple recovery mode issue

texts:
During my daily maintenance backup task, I am attempting to set specific databases to recovery model simple along with shrinking the database.

ALTER DATABASE db_name SET RECOVERY SIMPLE
GO
DBCC SHRINKDATABASE (db_name)
GO

This happens at 1am along with some other tasks that use the same database. However, I am getting an error where

  Execute T-SQL Statement Task (AERIS\SQL2008) Execute TSQL on Local server connection   Execution time out: 0 Task start:  2011-12-08T01:00:04.
  Task end: 2011-12-08T01:16:45.
  Failed:(-1073548784) Executing the query
  "alter database db_name set recovery simple" failed with the following error: "ALTER DATABASE failed because a lock could not be
  placed on database 'db_name'. Try again later.
  ALTER DATABASE statement failed.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly,
  parameters not set correctly, or connection not established correctly.

Could the issue be that since a user is logged on the the server at the point the recovery model is being changed? I'm puzzled since sometimes the recovery model change works intermittently.


texts: Surprising results from assigning multiple variables in FOR loop

texts:
CREATE OR REPLACE FUNCTION my_function(input jsonb)
  RETURNS jsonb
  LANGUAGE plpgsql AS  -- language declaration required
$func$
DECLARE
   _key   text;
   _value text;
BEGIN
    FOR _key, _value IN
      SELECT jsonb_each_text($1)
    LOOP
        -- do some math operation on its corresponding value
        RAISE NOTICE '%: %', _key, _value;
    END LOOP;

    RETURN input;
END
$func$;

For the function, if I call:

my_function('{"a":1, "b":2}');

It will raise notice that looks like (a, 1), (b, 2) in the message box.

However, if I change the code:

        RAISE NOTICE '%: %', _key, _value;

into:

        RAISE NOTICE '%', _key;

The same results will pop up.

While the code:

        RAISE NOTICE '%', _value;

would pop up nothing. How did that happen?


texts: Computer runs out of space when indexing database on external disk

texts:
I have an SQLite database of about 80 GB on an external harddrive. I want to index some of the columns there, but when I do the operation fails after some time, with this error: sqlite3.OperationalError: database or disk is full

The external harddrive has 386 gb free space, while my local windows disk has 63 gb free space, so I suspect that SQLite use some temp storage on my local disc. Is my only option to reduce space on my local computer? Can I safely set operations on this database to use tmp storage on the external harddrive?

Disclaimer: I know it isn't best practice to use an external harddrive, however this isn't for production data, and I have several copies of this database.


texts: How get process that is locking Database on SQLite?

texts:
I'm developing a iOS app using SQLite. I've got a class to manage SQL's operations, basically read, write and update. In many cases I get a SQLITE_BUSY error, so I want know which process is using the database at the moment that the SQLITE_BUSY error occurs. I believe that I'm trying to INSERT data while another process is reading data.

Actually I'm trying handle it like this: (YES, IT'S WRONG)

    let aux_error_code = 0
    repeat {
        if sqlite3_step(insertStatement) == SQLITE_DONE {
            lastid = Int(sqlite3_last_insert_rowid(db))
            aux_error_code = 0
        } else {
            aux_error_code = sqlite3_errcode(db)
            print("ERRO SQLITE INSERT STEP, ERROR CODE: \(aux_error_code), MSG ERROR: \(sqlite3_errmsg(db)!)\n QUERY: \(insertStatementString)")
        }
    } while(aux_error_code == 5)

This generate a big list of prints.

SQLite has some ways to handle this, but to choose one I want know what process specifically is causing this problem.
I believe that this is a good way to solve my problem: https://www.sqlite.org/c3ref/open.html, but I don't have experience with this. Because that I want know which process causes my problem, if is read or update, or insert by other function too. Anyone can help me to handle this?


texts: Would it be considered as a bad practice to have multiple nullable FKs on a table in SQL Server

texts:
On my database structure in SQL Server, I have 3 types of products which requires different information about the order. So, I created one Customers table and three different orders tables: OrdersForProductAs, OrdersForProductBs, OrdersForProductCs. All orders table has one to many relationship on Customers table.

I also have another table which is Payments and will hold the payment details inside. But I have doubts here on how to structure it.

As I have multiple product types and a customer may have orders for multiple products at the same time, I need to relate those three order tables to Payments table.

The other issue is that a customer may have an order for only one type of product. So, the FK columns on Payments table needs to be nullable.

My question is whether those nullable FK columns would be a headache for me on the long run or not? Generally speaking, would it be considered as a bad practice to have nullable FK columns on a table?


texts:
I am using an SQL Server 2012 on windows datacenter 2012

I have encountered a situation where i think some virus/sql injection had heart my database,

Some data on my table had changed to weird link's and it was 100% not coming from my system,

I wish to know what is the best way to track the origin of this change,
Is there a way to look in the transactional logs and checek which ip/machine had changed a table ? and what were the changes ?

Thanks.

EDIT :

Maybe is there some software like fiddler\wireshark that can trace the sql queries that are being sent to a server ?


texts: Database defragmentation and autogrowth settings

texts:
We do have some maintenance plan for our SQL Server 2008 r2 express.
Every month we do de-fragmentation of the database if any table has page count more than 50 for any table and average fragmentation more than 20.

If the database log size>2 MB, then the recovery mode is made as simple, and it is shrinked, and the recovery mode is set back to FULL.
If the Page_count>50 and avg_fragmentation_in_percent > 30 then the index is REBUILD.

And if Page_count>50 and avg_fragmentation_in_percent > 5 and <30 then the index is REORGANIZE.

This is what we are doing till now.But we found that autogrowth events are resource intensive and it should not happen repeatedly. Now for all database autogrowth is set to MB for mdf file and 10% for ldf file which is default value while creating new database.We are planning to increase the autogrowth values for the database depending on how much database is getting bigger every day.But i want to know how much autogrowth events is ideal for the database.Should i set autogroth so that it happens only once a day,week or month etc.So please help me to set the autogrowth value for my database.
Also there is another problem if I do monthly de-fragmentation of  database then it will be shrinked. So after this for all database for which i did shrink autogrowth occurs once when new data is written to it.So there will be so many autogrowth events. So whether it will be a problem?
Please tell me a solution.


texts:
When it comes to the dbo schema:

Is it a best practice to avoid using the dbo schema when creating database objects?
Why should the dbo schema be avoided or should it?
Which database user should own the dbo schema?


texts: BCP code to extract a large amount of data from MS SQL Server

texts:
I have been using Python to perform a SQL query and retrieve data from the previous date every day since June and load it into a Postgres database which is local. Each date has about 2 million rows and 75 columns or so, so it is fairly large and it takes a while to pull.

Can someone give me an example bcp code which will generate a CSV (comma) file? How much data would I be able to pull at once or can I loop through each calendar date in the SELECT statement? I am hoping this will be a faster way to kind of replicate the tables I am interested in.

My Python code essentially boils down to SELECT * FROM table_name WHERE report_date = {}. The report date is typically yesterday.

Here is my final working result if anyone else every stumbles upon this. The subprocess.run executes the string which contains the BCP query. In the code below, there are a few variables such as the table_name, database name, and query date since I am looping through different tables and databases on different dates.

path = 'C:/raw/'
filename = '{}_{}.csv'.format(database, select_database(database)[table_int])
query = '"SELECT * FROM {}.dbo.{} WHERE row_date = \'{}\'"'.format(database, select_database(database)[table_int], query_date)
command = 'BCP {} queryout \"{}\" -t, -c -S "server_name" -T'.format(query, os.path.join(path, filename))
table_name = 'raw_{}_{}'.format(select_database(database)[table_int], database)
logger.info(command)
subprocess.run(command)


texts: How to benchmark query parsing & planning in Postgres?

texts:
I'm trying to benchmark the query planning and parsing steps in PG:

However, it seems that the query planner is using some sort of cache - only the first execution of EXPLAIN ANALYZE seems to report a high value in the Planning time section. Subsequently, it is less than 1ms. How do I turn this off?
The docs clearly state that the parsing time is not taken into account by EXPLAIN ANALYZE. Is there any other command/tool that will report the time it took to parse a query?

  The Planning time shown by EXPLAIN ANALYZE is the time it took to generate the query plan from the parsed query and optimize it. It does not include parsing or rewriting.


texts: Streaming Replication Failover - how to point second slave at new master?

texts:
I have a Postgres database HA cluster I've set up in a lab. I'm using PgPool-II to manage fail-over.

For the cluster itself, I have three identical Postgres 9.2 servers set up like so:

Server A - Master DB
Server B - Slave DB with synchronous Streaming Replication
Server C - Slave DB with asynchronous Streaming Replication (possible synchronous server)

When Server A fails, server B takes over (no problems here). However, I need to make sure that Server C is 're-pointed' to stream from Server B.

Currently, I'm achieving this by doing a full base backup from B to C - it works, but seems like a sledgehammer approach given that the two databases should be almost identical at the point when server A fails...

Is there a sensible way to get server C to start streaming from server B without doing a full base backup?

I'm not looking for a hack to get this to work - the lab system will eventually move into a production environment, so I need a solid solution...

Any advice or pointers would be most welcome!

Cheers.


texts: Need to convert date to word using trigger

texts:
I have created a datetoword function in Mysql. Checked the function working fine. Now using this function need to convert the date from field1 (date-datetype) to field2 (Varchar-datatype) within the table using trigger on before insert.

Tried with some trigger code

Set New.dobw = CALL date_to_words(New.dob);

Help needed in this regard.

Show function status;  (output is shown in screenshot)

SELECT date_to_words(NOW())


texts: Stored Procedure with Optional Parameters

texts:
I have a View that grabs a lot of data that I want to be able to Search through.  The View is set up with the following columns:

  Part_Number, Description, Information, Supplier, Manufacturer, Category, Subcategory, Currency, Price, Discount.

I am looking to create a Stored Procedure which lets me search through the View for the data I am specifically looking for.  Something like:

SELECT * FROM PartsData
WHERE Part_Number = '0010.05.20.20'

Now my first thought, was to set up the procedure so that the only parameter it was looking for was a VARCHAR which would essentially contain whatever the entire WHERE clause was.  This sounds like it would work.

However, would I be able to instead set up a Stored Procedure with optional parameters?  I am not sure if this is something you can do in SQL though.

If not, is my initial idea for implementation my best bet?  Because it seems like there should be a better way to go about doing this. :\

Sometimes the WHERE clauses can be rather long, such as:

WHERE Description LIKE '%cap'
AND Supplier LIKE 'A2A Systems'
AND Manufacturer LIKE 'Vario'


texts: Large Database backup approx 2 TB and restore with limited disk space

texts:
I am working on DB migration from server A to server B.

Server A:- DB size  - 2 TB - Sql 2008R2 - Multiple Data files stored in diff drives.

Server B:- Disk space:- 999 GB - Sql 2014 Enterprise

Suggest me the best possible way.

I thought of using backup compression but later came to know it can be used only between same Sql server Editions. Is it TRUE ?

Also let me know if I use Sql compression, will there be any change between the original DB and compressed DB restored on the new DB ?


texts: Start Oracle 12c enterprise manager in Windows 7

texts:
I have installed Oracle 12c in Windows 7. The installation was succesful and the RDBMS and the database that I created are working properly.

Then I tried to access to the enterprise manager via web in https://localhost:5500/em as the installer told me to do but it doesn't connect.

If I type netstat I get:

TCP [::1]:5500 My-PC:49439 TIME_WAIT
TCP [::1]:5500 My-PC:49440 TIME_WAIT
TCP [::1]:5500 My-PC:49441 TIME_WAIT
TCP [::1]:49442 My-PC:5500 TIME_WAIT

I am using a proxy but I disabled it for local locations.

I tried this http://docs.oracle.com/cd/E25054_01/doc.1111/e24473/emctl.htm with no success because I couldn't find emctl file or the services it's talking about. I have 5 Oracle services running instead.

Thanks in advance.


texts: Why wouldn't statistics update if all rows in a table were replaced?

texts:
We have several "reporting" tables in a SQL Server 2012 SP1 instance. Every hour, a job runs procedures that delete all the rows from these tables and then insert current data (involving lots of recursion and aggregations) into them. Each contains thousands of rows. Each such table is queried many times per day. The statistics on both the clustered and non-clustered indexes on these tables show outrageous things like 94,000% of rows updated, with the last statistics update being several days ago. Auto Update of statistics is set to true.

I would have thought that the statistics would get updated every time the table was queried for the first time after the contents were last replaced, but that apparently is not happening. Why?


texts: Is it possible to configure PostgreSQL to automatically close idle connections?

texts: Clients connect to our PostgreSQL 8.3 database but leave the connections opened. Is it possible to configure PostgreSQL to close those connections after a certain amount of inactivity?


texts: Troubleshooting Memory-Pressure on SQL Server with 80+Gb RAM

texts:
I have a SQL Server cluster with 80+Gb of RAM and multiple production enterprise databases and applications running. We are encountering memory-pressure errors and are trying to troubleshoot what's happening that leads up to this. Our indications are that some CLR libraries are being unloaded and re-loaded, followed by a system slowdown. I have read through some other items

We are setting up an Extended Event session for rpc_completed, sql_statement_completed and sp_statement_completed, filtering all where duration > 1s.

I have also read through Marked for unload due to memory pressure as well as some other articles and still have some questions:

What other events should we listen for?
Are there any concerns with running this session on a SQL Server with 80+ GB of RAM?
What are some tips on narrowing down major causes to Memory Pressure errors on enterprise servers hosting multiple production databases?

This is our current event session:

CREATE EVENT SESSION [SlowRunningQueries] ON SERVER
ADD EVENT sqlserver.rpc_completed(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.nt_username,sqlserver.sql_text
    ,sqlserver.username
    )
    WHERE ([duration]>=(1000000))
    )
,ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.nt_username,sqlserver.sql_text
    ,sqlserver.username
    )
    WHERE ([duration]>=(1000000))
    )
,ADD EVENT sqlserver.sp_statement_completed(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.nt_username,sqlserver.sql_text
    ,sqlserver.username
    )
    WHERE ([duration]>=(1000000))
    )
ADD TARGET package0.event_file(
    Set filename='C:\Traces\ProductionTrace.xel'
    ,max_file_size=500 --in MBs, default is 1Gb
    ,max_rollover_files=10
)
WITH (
    MAX_MEMORY=4096 KB
    ,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO


texts: How to design a schema to store search keywords

texts:
I am doing a database design that stores search keywords of a search form, used to generate the most popular search keyword reports by date, month and year. I have made a simple schema:

CREATE TABLE SearchTerm (
 ID             INTEGER         PRIMARY KEY AUTOINCREMENT NOT NULL,
 SearchTerm     VARCHAR( 255 )  NOT NULL,
 Date           DATE            NOT NULL
);

In this case, search term will be redundant in the table and make the database grow faster. I want to avoid data redundancy.

Also I am not sure if using the date data type or unix timestamp will make a difference in retrieving the data faster.

So, should I create a new table to store a date that links to master table by id? For example,

CREATE TABLE SearchTerm (
 ID             INTEGER         PRIMARY KEY AUTOINCREMENT NOT NULL,
 SearchTerm     VARCHAR( 255 )  NOT NULL,
 Hit           INTEGER         NOT NULL,
 DateID         INTEGER         NOT NULL
);

CREATE TABLE DateofSearchTerm (
 DateID         INTEGER         PRIMARY KEY AUTOINCREMENT NOT NULL,
 Date           DATE            NOT NULL
);

My concern is performance as the database grows. If the database is not designed properly , it would be leading to performance and bottleneck issues.

One of my friends has done an analytic site, but it is not going well. I figured out that there are 3 million records in the table and his query is taking more than 3 minutes to return results.

And only one table he created, then save “Event Name”, “Start data”,” End date” something like that. When generating monthly report, it could not show the report due to timeout issues (server time out, query time out, jquery response time out).

His SQL query is too expensive. He use group by date in sp to get how many users daily and monthly.  Then passes the result to populate flash based report. I fixed it to see report by increasing command time out. But I don’t think it is a good idea. Data is continuing to grow, it would be 4 mill records in end of this year. At the end of the day , increasing command time out will not work. This issue is a result of weak database design. I don’t want to follow it.

Please advise me and share your experience to avoid these issues. How should I design search term db schema?


texts: Which indexes for joining a table onto itself on two columns

texts:
I am using SQL Server 2016, and trying to join a table onto itself, but the query is very slow, and it seems its because SQL Server is refusing to use the indexes.

If you'll excuse the crudity of this example, I have a table like this...

TABLE [Balances] (
    account_id NCHAR(20),
    balance_date DATE,
    balance INT,
    next_balance_date DATE
)

... and I essentially want to do this query:

SELECT [t1].*, [t2].[balance] [next_balance]
FROM [Balances] [t1]
LEFT JOIN [Balances] [t2]
ON [t1].[next_balance_date] = [t2].[balance_date]
AND [t1].[account_id] = [t2].[account_id]

The problem is that no matter what index I try to put onto this table, SQL Server insists on doing a hash match. Which is slow on this large database.

And I currently have the following indexes:

NONCLUSTERED INDEX [idx_acc_date] ON [Balances] ([account_id] ASC, [balance_date] ASC)
NONCLUSTERED INDEX [idx_acc_nextdate] ON [Balances] ([account_id] ASC, [next_balance_date] ASC)

What indexes should I be creating to speed-up this query?

(As a small note on the data - all of the balance_dates are as at month end, and therefore the same goes for the next_balance_date. Also, each account_id only has one entry at each balance_date.)


texts: How to change schema so that account_id reference is unique among 3 tables

texts:
Original image:

Updated to have more-correct terminology and an 'is_debit' column:

I am designing a (PostgreSQL) schema for a lottery website that uses double-entry accounting.

'jackpots', 'users', and 'house' are each tables which reference an account_id, which in turn is used in the journal to record a credit or debit of money 'amount'.

I want to assure that the account_id is only in one table -- and that it is unique in that table.

What is the best way to refactor the tables so that account_id is guaranteed to appear only 1x in all of them?

What I've considered:

Creating another table, 'account_type' that has different codes for user accounts, jackpot accounts, and the house's account.  Then add two colums to 'accounts', ('account_type' and 'ref_id', the latter of which will reference the id of 'jackpots', 'users', or 'house').  This seems somewhat inelegant since I wouldn't know how to link the table type to the table name using SQL, alone.  I don't mind using triggers, though, if this is the only way for an elegant, foolproof solution.
Looking for some sort of built in constraint that says account_id cannot be used more than once among 3 tables.  Would guess that this doesn't exist, however.

Help is appreciated!


texts: How to migrate MySQL database to SQL Server on a separate machine?

texts: I have a MySQL datadump file from the database to be moved. From the suggestions I've read so far, most migration tools only work with databases that are hosted on the same server. What steps can I take to convert the MySQL datadump file to an SQL Server database on a separate machine?


texts: How to handle the junk characters upon conversion of the data from image datatype to varchar?

texts:
We are trying to convert the column of type image to column of type varchar(max). We tried the below way to do the conversion.

--col1 is of type image
--col2 is of type varchar(max)

set col2 = convert(varchar(max), convert(varbinary(max), col1)) from table

When we do it, we are getting junk characters like ÿÿÿÿÿÿÿÿ. We also tried using nvarchar(max) instead of varchar(max) which again adds more junk data.

The above conversion works for few types of record and doesn't work for few.

col1 starting with "0xD0CF11E0A" is not working
col1 starting with "0x7B5C727" is working fine

Please help me identify the issue here.


texts: MySQL Workbench Automatically Capitalized and Reformatted Section of SQL Script

texts:
I was editing a SQL script that I was using in MySQL Workbench 6.3.  To my surprise, I noticed that one section of the code had transformed into all uppercase.  I normally type in mostly lowercase with my own habits for tabs and capitalization.  Somehow, a section of the code was automatically reformatted.

I am not familiar with what option caused this.  Perhaps I made some sort of error in keyboarding while flipping and scrolling through a lot of code.  Is there an autoformatting feature in MySQL Workbench?

How can this type of formatting be done or undone?

A sample of my code before and after follows:

Before:

    select * from ReportQA_TEMPA;
    select * from ReportQA_TEMPB;
    select * from ReportQA_TEMPC;
    select * from ReportQA_TEMPD;

After:

  SELECT
    *
FROM
    ReportQA_TEMPA;
SELECT
    *
FROM
    ReportQA_TEMPB;
SELECT
    *
FROM
    ReportQA_TEMPC;
SELECT
    *
FROM
    ReportQA_TEMPD;


texts: Creating a global temp table in a SQL Agent job

texts:
I want to create a global temp table in a SQL Server Agent Job.

Here is the code:

CREATE TABLE ##TestEmployee(Id int, EmployeeName nvarchar(50))

When I execute in SSMS, all is well.

I then put the above code on a SQL Agent Job. The job will complete successfully, but when I try doing a select statement in SSMS, I get an Invalid Object error.

SELECT * FROM ##TestEmployee

Are there any gotchas I am missing by creating a global temp table in SQL Server Agent?


texts: mongodb v3.4 - wiredtiger - compact does not work

texts:
I am using mongo v3.4.10 with WiredTiger. I run the following command:

db.runCommand({ compact: "my_collection" });

the response is:

/* 1 */
{
    "ok" : 1.0
}

and the mongo log shows:

2017-10-31T00:12:20.282+0000 I COMMAND  [conn23] compact mydb.my_collection begin, options: paddingMode: NONE validateDocuments: 1
2017-10-31T00:12:21.662+0000 I COMMAND  [conn23] compact mydb.my_collection end
2017-10-31T00:12:21.663+0000 I COMMAND  [conn23] command mydb.my_collection appName: "MongoDB Shell" command: compact { compact: "my_collection" } numYields:0 reslen:22 locks:{ Global: { acquireCount: { r: 1, w: 1 } }, Database: { acquireCount: { W: 1 } } } protocol:op_command 1381ms

In short, everything looks fine, but the storageSize is exactly the same :/

I deleted lots of fields with a value of null, so I would expect a significant drop in disk usage.

I read online that early v3 had issues with this, but I'm on the latest version right now. The database is quite large so if I can avoid repairDatabase that would be good.

Any help is appreciated, thx.

UPDATE: I performed a mongodump / mongorestore, and it resulted in a 9% disk use reduction (admittedly I was expecting more). Nonetheless, it seems like compact should have had an impact.

UPDATE 2: I performed a repairDatabase and it had no effect. This is explicitly stated in mongo docs (https://docs.mongodb.com/manual/reference/command/repairDatabase):

  For WiredTiger, the operation rebuilds the database but does not result in the compaction of the collections in the database

but i thought i'd give it a crack. Still not sure why compact did nothing though.


texts: Whats the difference between Transaction logs and Binary logs?

texts:
Coming from a SQL Server shop, I now work with MySQL and I was curious.

What are the differences between MySQL's binary log and MSSQL's transaction log?

By the outlook thus far, it seems that there is only one binary log per MySQL instance as opposed to a transaction log per database as in MSSQL.


texts: ORA-00933: SQL command not properly ended

texts:
I have created a database link to a remote database, MIGRERDOSSIER.US.ORACLE.COM, then I use it to insert records in a table in a local database. Here is the request I am trying to execute:

insert into
    Dossier(num_dossier,indice,code_service,num_secteur,date_depot,type_dossier,SUP_DECL_ARE,SUP_DECL_CE,SUP_DECL_HE,SUP_DEF_ARE,SUP_DEF_CE,SUP_DEF_HE)
    select s.num_dossier,s.indice,s.code_service,s.num_secteur,s.date_depot,s.cod_sous_typ,s.SUP_DECL_ARE,s.SUP_DECL_CA,s.SUP_DECL_HE,s.SUP_DEF_ARE,s.SUP_DEF_CA,s.SUP_DEF_HA
    from MIGRERDOSSIER.US.ORACLE.COM@Dossier s

but Oracle is displaying the error ORA  00933: SQL command not properly ended.

I can't figure the problem with my request, any help would be greatly appreciated.


texts: Is it possible to have a SQL Server cluster with Hadoop?

texts:
I know how Hadoop works. Is there any possible way to have SQL Server run under Hadoop to build a cluster based on SQL Server and not Drill or Hive.

Just a wild question.

Any good tutorial/documentation?


texts: Using Galera for simple master-master replication

texts:
I was reading the documentation and it specifically mentions that a Galera DB cluster must have at least three nodes to achieve quorum, although our current setup is working with two.

I am tasked with rebuilding the cluster.  My use case for Galera is a simple master-master cluster.  It is in actually a master-slave, with the secondary master sitting there in case the first server fails (then we can point the PHP configs at it).  Galera was chosen due to the fact that it is synchronous.

My questions are:

Is a two node configuration safe/supported?
In a 3 node setup, if the third node dies it now becomes a two node setup.  Is this now unsafe meaning I should really have a 4 node setup?
What exactly is a Galera "arbiter"?


texts:
Trigger reading from two view tables and write on a different table takes more than 12 seconds, what might be the issue here?

In my case, I am using a trigger to read from two view tables (Decisions2 and Con_Decisions_Groupby), each table contains more than 20000 records, and only I want the trigger to work with the updated values and if a new record is added to the view table, and not to go through all the records in order to hide the delay.
This is my trigger: (I am not sure if it is written correctly to cover my case)

USE [W1]
GO
/****** Object:  Trigger [azteca].[trg_WOcommittee_02]    Script Date: 6/5/2018 10:23:41 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [azteca].[trg_WOcommittee_02]
   ON [azteca].[WORKORDER]
   AFTER UPDATE, insert
AS
   UPDATE [azteca].[REQUEST]

SET [azteca].[REQUEST].[TEXT11] = [dbo].[Decisions2].[L1_Desecion],
    [azteca].[REQUEST].[TEXT12] = [dbo].[Decisions2].[L2_Desecion],
    [azteca].[REQUEST].[TEXT13] = [dbo].[Decisions2].[R1_Desecion],
    [azteca].[REQUEST].[TEXT14] = [dbo].[Decisions2].[R2_Desecion],
     [azteca].[REQUEST].[TEXT4] = [dbo].[Decisions2].[HPC1_Desecion],
    [azteca].[REQUEST].[TEXT15] = [dbo].[Decisions2].[SPC2_Desecion],
    [azteca].[REQUEST].[TEXT5] = [dbo].[Decisions2].[Drafting],
    [azteca].[REQUEST].[TEXT2] = [dbo].[Decisions2].[Auditing_Desecion],

        [azteca].[REQUEST].[TEXT20]= [dbo].[Con_Decisions_Groupby].[TEXT20]

FROM [azteca].[REQUEST]  FULL JOIN [dbo].[Decisions2] ON [azteca].[REQUEST].[REQUESTID] = [dbo].[Decisions2].[REQUESTID]
                       Full join [dbo].[Con_Decisions_Groupby] on [azteca].[REQUEST].REQUESTID =  [dbo].[Con_Decisions_Groupby].REQUESTID

Kindly find the attached screen shot which shows all the story:


texts: Impact on SSRS of migration of SQL Server from 2008 to 2012

texts:
As part of my project, we are upgrading SQL Server 2008 R2 to SQL Server 2012. We have a separate SSRS 2008 instance running on a separate machine which interact with these existing 2008 database servers and create various reports.

My architect's recommended that we just create a new VM and get SQL Server 2012 with existing Data. (You install a new environment, copy your metadata and data to the new environment, and configure the new environment to use existing content.)

He suggested that we need not to make any changes to SSRS instance which is running on older version. He suggested just to re-use existing SSRS instance and point to new SQL Server 2012.

My question is about the feasibility of this solution. Will that work? Can you have SSRS instance running on older version but interacting with migrated SQL Db Server 2012 instance. Any challenges/possible issues?


texts: Weird database icon appears on PHPMyAdmin user interface

texts:
On my PHPMyAdmin user interface, I noticed one database which has a strange icon:

What does this mean? What causes it?

P.S. Note that the database in question functions well (I mean I have no issue with it)


texts: There is an equivalent of login.sql on PostgresSQL?

texts:
When you log on Oracle database with SQLplus, it looks for a login.sql file in the current directory. If the file exists, it's executed.

It's usefull to set preferences...

There is an equivalent when I log to a PostgreSQL database with psql ?


texts: Seek and you shall Scan... on partitioned tables

texts:
I've read these articles in PCMag by Itzik Ben-Gan:

Seek and You Shall Scan Part I: When the Optimizer Doesn't Optimize
Seek and You Shall Scan Part II: Ascending Keys

I’m currently having a "Grouped Max" problem with all of our partitioned tables.
We use the trick Itzik Ben-Gan provided for getting a max(ID), but sometimes it just doesn’t run:

DECLARE @MaxIDPartitionTable BIGINT
SELECT  @MaxIDPartitionTable = ISNULL(MAX(IDPartitionedTable), 0)
FROM    ( SELECT    *
          FROM      ( SELECT    partition_number PartitionNumber
                      FROM      sys.partitions
                      WHERE     object_id = OBJECT_ID('fct.MyTable')
                                AND index_id = 1
                    ) T1
                    CROSS APPLY ( SELECT    ISNULL(MAX(UpdatedID), 0) AS IDPartitionedTable
                                  FROM      fct.MyTable s
                                  WHERE     $PARTITION.PF_MyTable(s.PCTimeStamp) = PartitionNumber
                                            AND UpdatedID <= @IDColumnThresholdValue
                                ) AS o
        ) AS T2;
SELECT @MaxIDPartitionTable

I get this plan

But after 45 minutes, look at the reads

reads          writes   physical_reads
12,949,127        2       12,992,610

that I get out of sp_whoisactive.

Normally it runs quite quickly, but not today.

Edit:
table structure with partitions:

CREATE PARTITION FUNCTION [MonthlySmallDateTime](SmallDateTime) AS RANGE RIGHT FOR VALUES (N'2000-01-01T00:00:00.000', N'2000-02-01T00:00:00.000' /* and many more */)
go
CREATE PARTITION SCHEME PS_FctContractualAvailability AS PARTITION [MonthlySmallDateTime] TO ([Standard], [Standard])
GO
CREATE TABLE fct.MyTable(
    MyTableID BIGINT IDENTITY(1,1),
    [DT1TurbineID] INT NOT NULL,
    [PCTimeStamp] SMALLDATETIME NOT NULL,
    Filler CHAR(100) NOT NULL DEFAULT 'N/A',
    UpdatedID BIGINT NULL,
    UpdatedDate DATETIME NULL
CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED
(
    [DT1TurbineID] ASC,
    [PCTimeStamp] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, DATA_COMPRESSION = ROW) ON [PS_FctContractualAvailability]([PCTimeStamp])
) ON [PS_FctContractualAvailability]([PCTimeStamp])

GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_UpdatedID_PCTimeStamp] ON [fct].MyTable
(
    [UpdatedID] ASC,
    [PCTimeStamp] ASC
)
INCLUDE (   [UpdatedDate])
WHERE ([UpdatedID] IS NOT NULL)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, DATA_COMPRESSION = ROW) ON [PS_FctContractualAvailability]([PCTimeStamp])
GO


texts: What could cause waiting for table metadata lock?

texts:
I am using a MySQL 5.6 DB with MyISAM tables under FreeBSD 8.3. The main part of code is in PHP, but the problem arises, when I launch the self-written on C++ chat-server. I use the mysql.h for DB connections and libwebsockets for both HTTP and websockets interaction. The previous version was written in C, no locks happened.
The chat server has a websocket regime and the refresh regime. It also uses the database for user authentication and loading/saving of settings.

Now when I run this server, after a certain period, the database gets flooded with connections with different queries, all with the status "Waiting for table metadata lock". When I kill the chat, the queries start being processed.

I am 100% sure, there are no ALTER TABLE, CREATE TABLE or similar queries being executed at any time, so they are not the reason.

So I don't understand what in this situation may be the reason for database to hang, and how can I trace and solve it. I would appreciate any help or ideas! Thanks ahead!


texts: Transport-level error when I try to launch a select from SQL Server to Oracle via Dblink

texts:
I'm trying to launch a select from Sql Management Studio (SQL Server 2014) on a DBlink that points on an Oracle database that is handled by another company. The results begin to come back to the console and are shown, then, after a while, this error is raised:

  A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - The pipe has been ended.)

Query example

SELECT * from openquery(positions,
 'SELECT * from oracleTableDbLink.POSITION' )

It is strange because if I put this simple query on a procedure it works fine.


texts: IN postgressql "pgstartup.log" occupied 350 GB space in disk

texts:
How  to reduce the pgstartup.log file size.
If i delete pgstartup.log what will happen.
We are using postgresql in Red Hat Enterprise Linux Server.


texts: How to create a "match result" data type?

texts:
I'm building a football match table which contains one column "match result",
which should consist of data like "3-1","0-0"... I'm wondering what kind of data type is best appropriate for it, varchar or else?
I'm thinking of creating a new data type for this column, using CREATE TYPE statement, whose form is like "integer"+"-"+"integer",
is it possible?


texts: Help with Access many-to-many relationship (beginner)

texts:
I'm a beginner with MS Access hoping for some help with a database I am trying to create. This is (will be) a database of land parcels in a given area, and their associated landowners.

Here are my table fields, summarized in Excel (bold are my current PKs):

County Plat contains the parcels; the 'Owner' field is the listed owner for the land and could be an LLC, etc. so could have multiple individuals with varying stakes.

The Owners table associates these 'owners' with their corresponding landholders and their ownership percentage.

Finally, Contact List contains a given landholder's contact details.

So, for example, Parcel 0001 and Parcel 0009 are listed in County Plat as owned by Owner TOFU FAMILY TRUST, and the trust is listed in Owners as owned 50% by Jim Tofu and 50% by Bob Tofu. Their respective contact information is listed in Contact List.

Also, I would prefer not to edit the setup of the County Plat database if possible, as it comes from an outside source.

I would greatly appreciate any advice on how to best set up this database. I am currently getting errors trying to enforce referential integrity, and I'm pretty sure it is due to my lack of understanding of database design. Although I've read articles on it, I'm having difficulty applying those lessons to my own database. It would be really helpful to get some feedback from the pros on how to implement this.

Thank you!


texts: How does AWS DMS on-going replication works internally?

texts: From the documentation it's mentioned that "DMS collects changes to the database logs using the database engine's native API" and replicates to target. But, I didn't see anywhere that, at what rate it replicates from source to target in terms of number of records and is there any way we can control this settings or can I know each time how many rows it replicates?


texts:
I have MariaDB 10.2.9 on Centos7 with ~60 tables. I listed the table's create_options from information_schema.tables:

SELECT TABLE_NAME, TABLE_TYPE, CREATE_OPTIONS
FROM information_schema.tables
WHERE TABLE_SCHEMA like 'dbname';

+------------+--------------------------------------------+
| TABLE_TYPE | CREATE_OPTIONS                             |
+------------+--------------------------------------------+
| BASE TABLE | checksum=1 page_checksum=1 transactional=0 |
| BASE TABLE | checksum=1 page_checksum=1 transactional=0 |
| BASE TABLE | checksum=1 page_checksum=1 transactional=0 |

Can I clear the "create_options" values?   If yes, how?

I want to remove the page_checksum variable from options because I want to go back to using MyISAM, and page_checksum is an Aria-specific variable.


texts:
I've been doing backups of my SQL Server 2014 Express databases for import to other servers and noticed a difference in file size between .bacpac and .bak.

Why is a .bacpac file so small compared to a .bak file of the same database?

Thanks for any insights!


texts: postgres database program to save sql templates and search where used it

texts:
I have a postgres database with tables and functions build using sql templates. And I would like to use a database program to save these templates and search where I used it.

What i want to do is to save those templates and each in tables and functions identified which template i use, for example by comment, and look for them.

I searched for programs that let me save those template and I've found:

navicat; let me save the template queries but will not organize it in folders or tell me where i use it
valentina studio, let me save templates and add comments to the functions and others, but do not let me search for the comments

Do you know any database program that works with postgres, that allows me to do this?


texts: Does the relationship between teams and matches have a many-to-many cardinality ratio?

texts:
Diagram
I created the following diagram:

Considerations
So I will create two tables, one called Match and the other one called Teams. In Match there will be data about two teams, Away and Home.
Let us take into account that each team can have take part in many matches, and a match can have exactly two teams.
Questions

Is this considered a many-to-many (M:N) relationship?
Is there anything I can do to change it?


texts:
How can I select all records with a % in? I think this code doesn't work.

SELECT column1, column2 FROM table WHERE column1 LIKE '%%%'

first and last % for zero, one or more chars
second % for the percent skinn

Can anyone suggest me how it can work? I use MS-SQL server.

Thanks in advance


texts: Difference between table fillfactor and index fillfactor

texts:
In Postgres, one can set fillfactor for an index as well as a table. What is the difference? How to decide values for either. What are the usecases?

I am trying to cluster a spatial relation on a spatial index. It has a couple of million records. The records are updated all the time though very few new ones are created daily.

The use case is spatial range queries.
What will be a good value for the table fillfactor and / or the index fillfactor?


texts: pgAgent : how to configure pgpass.conf for a local connection on Windows Server 2008

texts:
I try to configure pgAgent on Windows server 2008 to make automatic backup on the database.
pgAgent is installed on the server and the service is running normally.
I use PgAdmin3 and PostgreSql 9.3

I have already planned query using the connection string with the password in it, and it works perfectly.

Now, I try to make it by specifying the password in pgpass.conf (as said in the doc) but I always got the "Couldn't get a connection to the database !" error.

My pgpass.conf is something like this :

175.35.105.15:5432:*:admin:***
175.35.105.15:5432:*:postgres:***
localhost:5432:*:postgres:***
localhost:5432:*:admin:***
127.0.0.1:5432:*:admin:***

I know that some line are rhetorical, it's just that I've tried a lot of things.

I usually try to connect on localhost, but even in remote connection mode it doesn't work.

Here some examples of what I've tried with remote connection in step configuration :

user=admin host=175.35.105.15 port=5432 dbname=db
user=postgres host=175.35.105.15 port=5432 dbname=db
user=admin host=127.0.0.1 port=5432 dbname=db
user=postgres host=127.0.0.1 port=5432 dbname=db

EDIT : in Postgres log, pgagent is connected as adminpostgres. This username isn't in the login role...

pgpass.conf is in c:/user/Application Data/postgresql

pgAgent was installed with StackBuilder as Admin

I've tried to fill the "host agent" field with the content of "pga_jobagent.jagstation" but it doesn't work either


texts: c# Backup sql server database, error Operating system error 5(Access is denied.)

texts:
I have been trying to backup an sql server database, and I understand that this error is in regards to drive write protection. I am at the end of my rope with this, as nothing that I have found online has helped with this at all.

I have set up permissions for what should be considered every imaginable user that the server may try to pose as, everyone, administrators, my user, mssqlserver, sqlservermsasuser$xxx$MSSQLSERVER, SQLServer2005SQLBrowserUser, all with permissions set to full control.

I've tried running visual studio as admin, i've tried running the built application as admin, I've changed the "log on as" for the mssqlserver service to "Local System Account", me, and admin.

Yet still am having the same error, Operating System error 5 "Access is denied".
The folder is not read only, and is not in the root of the drive.
Any ideas??

Thanks in advance


texts: MySQL create trigger after insert not inserting all values

texts:
I am new to MySQL triggers. i want to insert the newly inserted booking from booking table to bookings_clone table.
PROBLEM:
All the field in bookings table are filled but in bookings_clone only these fields are getting inserted (id, uniqueid, branch_id, user_id, status, sms_status, email_status, created_at, updated_at) and rest of them are null. Any help in pointing me to right direction is highly appreciated. The table structure of bookings_clone is same as bookings.
CREATE TRIGGER:
CREATE TRIGGER after_booking_insert
AFTER INSERT ON bookings
FOR EACH ROW
INSERT INTO bookings_clone
(id, unique_id, branch_id, pos_user_id, user_id,
 user_address_id, address, postcode, city, town,
 status, order_due_date, order_due_time,
 checkout_mode, sms_status, email_status, notes,
 time_taken, accepted_at, delivery_charges,
 discount_amount, total_charges, sub_total_charges,
 initiator, created_at, updated_at, deleted_at)
 VALUES
 (NEW.id, NEW.unique_id, NEW.branch_id, NEW.pos_user_id, NEW.user_id,
 NEW.user_address_id, NEW.address, NEW.postcode, NEW.city, NEW.town,
 NEW.status, NEW.order_due_date, NEW.order_due_time,
 NEW.checkout_mode, NEW.sms_status, NEW.email_status, NEW.notes,
 NEW.time_taken, NEW.accepted_at, NEW.delivery_charges,
 NEW.discount_amount, NEW.total_charges, NEW.sub_total_charges,
 NEW.initiator, NEW.created_at, NEW.updated_at, NEW.deleted_at);

I've also tried this but the result is same
DELIMITER //
CREATE TRIGGER after_booking_inserted AFTER INSERT ON bookings
FOR EACH ROW BEGIN
    INSERT INTO bookings_clone select * from bookings where id = NEW.id;
END;//
DELIMITER ;

Bookings Table Structure:


texts: SQL Server 2000 dateformat permanent change

texts:
I am using SQL Server 2000 for an old system web site. I've just hit an issue that is going to cause me endless hassles down the road.

When I installed the server on my local system I took all the defaults but now any datetime data type is getting stored in mm/dd/yyyy format.

Now this is not an issue for inserting data but when getting the data out and writing it to a page its in US format.

The site is going to be based in the UK on a UK server so I need my local development system to mirror that, and I also want to have to avoid using CONVERT on every field which is a datetime.

Tried changing the database collation a few times without success.

So my question was this:

Is it possible to change the format of the datetime so that inserting 23/01/1972 is stored as such and not 1/23/1972?

SERVER Properties:

Default language: English (United States)
Collation: SQL_Latin1_General_Cp1_CI_AS

Database Properties

Collation: SQL_Latin1_General_CP1_CI_AS

Specifically as as example

CREATE TABLE [dbo].[lbg_app_section1](
    [id_app_section1] [int] IDENTITY(1,1) NOT NULL,
    [refid_applications] [int] NOT NULL,
    [dateofbirth] [datetime] NULL,
    [gender] [nvarchar](50) NULL,
    [ninumber] [nvarchar](15) NULL,
    [address_hsno] [nvarchar](60) NULL,
    [address_twncty] [nvarchar](60) NULL,
    [address_cnty] [nvarchar](60) NULL,
    [address_postcode] [nvarchar](15) NULL,
    [address_country] [nvarchar](30) NULL,
    [dt_section1_modified] [datetime] NULL
) ON [PRIMARY]


texts: Inserting in a table after crossing the allocated tablespace space

texts:
What will happen when I try to insert so many number of rows beyond the allocated tablespace size.
Say in Oracle DB, the initial tablespace size is set to 500MB and when it reached the limit and we try to create more records into it, should it fail or it will acquire more space from the hard disk?